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.
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.
Here is a SOAP/UI screen shot of a test run of the webservice. The time is shown in the bottom right corner – a whopping 23.956 seconds.
So, I had a quick look at the program code and decided to create SQL indexes of the major files in the program using the SQL selection fields that are used by the program code. This took me about a few minutes to look at the code and create a little SQLSTM *program* to create those indexes ready for testing…
NOTE: Logical Files already existed using the keys. I just did this as an experiment to see if a native SQL INDEX would speed things up…. and YES it did!
Looking at the SQLRPGLE code you might see a SQL statement something like this:
This shows a SQL fetch from file WHP75 ordering by Company (CONO75), Date of Movement (DTMV75), Stockroom (STRC75) and Part Number (PNUM75).
It’s also doing a SQL selection of Company, Event Type, Commit Sequence and Print Status.
I decided to create an index for each condition for the file access to see how it helps the runtime:
Run it and it creates the indexes…
Now lets run the SOAPUI again:
Runtime was 241ms.
Improvement on run from 24 seconds down to just over 2 seconds!!!!
That is a bajillion% performance increase!
Quite #ASTOUNDING for a simple create of an index….
IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.
How to Install IBM Access Client Solutions (ACS)
IBM i Data Obfuscation – Making Data Foggy Murky and Squinty
How to rename Fresche (BCD) Presto Library – XL_PRESTO
What is AS400 modernization?
IBM i ACS 5250 EMULATOR FONT – and other ridiculous mumbo jumbo
IBM i SQL statement to convert or compare hundred year date format
How to compare ‘100 Year Date’ to a Timestamp – aka – Weird AS400 iSeries Date formats
Modernize AS400 iSeries Queries – Convert to IBM i SQL
Use IBM i SQL to validate email addresses in Customer Master File