SQL is a wonderful beast for quickly getting data from huge tables (aka Files). The trouble with SQL is that it's so very simple to use; programmers get lazy and sometimes forget to do the analysis to make it as simple and #FAST as possible!
It's easy to overlook performance tuning because SQL goes out there and uses any existing access paths for the data it wants, and if it cant find one then it creates a temporary path to get that data. It's like magic. This is great but that split second of temporary path creation can add up when it's done over and over again.
How do we fine tune our SQLRPGLE programs?
We can use Index Advisor in System i Navigator to look for naughty indexes and take it's recommendations for action. But this needs IBM I Access to be installed. So, what if we don't have access to any tools to help us out?
I was at a client site this morning and they had a SQLRPGLE webservice running and complained about the run time. This is a fairly complex SQLRPGLE program that is getting a selection of data from a long list of tables and they were experiencing average response times between 5-9 seconds. But somtimes much much longer... anything over a couple of seconds is way to long in the webservice world.