August 31

1 comments

Speeding up SQLRPGLE using indexes instead of logicals

By NickLitten

August 31, 2015

SQLRPGLE, fast, performance, SQL, tuning

SQL Tables with Indexes and Views are faster than DDS Phsyicals and Logicals. #HONEST

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.

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.

Slow webservice example with soapui

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:

Example speeding up sqlrpgle program code

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:

Sqlstmt to create table indexes in as400 iseries and ibm i servers

Run it and it creates the indexes…

Running a sql statement

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….

  • Hi Nick,

    thank you for another helpful article.

    In [Cain/Milligan: IBM DB2 for i. indexing methods and strategies, 2011] I found this statement “With DB2 for i, there is no requirement to periodically reorganize or rebalance the index structures.” From your experience, do you agree? Is there no need to recreate or reorganize an index ever?

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

    >