I have always been partial to SQLRPGLE rather than writing around those chain statements in the RPGLE. More often than not, we are worried about the correctness and not about the efficiency of the program. Even I was not be excused on this accusation, well we can argue how can you increase the performance if we don’t know how it works right?
So, I decided to take a look at how a simple Select statement works inside the SQLRPGLE. SQL checks for any existing access paths for the selection criteria specified inside and if it cannot find one, it creates a temporary path to get that data in the specified order and all. You don’t have to worry about any thing. But over the time, depending upon the size of the file, the time taken to fetch data can add up when done over and over again. So how can we fine tune this? The answer is to use indexes when you know you are going to use the same program over and over again for a long time.
You can run the following statement in the command line to create the desired static indexes.
Verify this method on a large file where the regular query was taking a noticeable time and you can find a considerable performance increase in the program.