Re: SQLRPGLE performance problem after update from 6.1 to 7.1

Thanks for your reply.


We've learned a bit since I sent the original inquiry.  The copy of the service program that we were having the problem with had been promoted during the week we were operating from our fail-over box for our annual testing.  We still don't understand exactly why, but when we switched back to our normal production box, the copy that had been created by our mirroring software (Vision) had this problem.  The copy on the fail-over box had worked fine, so maybe it was something about the way that the access plans got built on the production box (which was acting as the fail-over box at the time - confusing, eh?) when Vision restored the program.  In any event, re-promoting the service program through Implementer corrected the problem.

This service program has many static SQL statements in it, including in the procedures we were having the problems with.  The reason is that dynamic SQL failed to meet our performance requirements in our initial testing.  These procedures return blocks of 1 or more records per call, and may be called thousands of times from a single batch report job.  Dynamic SQL appeared to be rethinking the access plan on each procedure call, and the performance was terrible.  Static SQL gave us performance comparable to native I/O.

We don't use ALIAS in our SQL, so that's not an issue for us.

 
Dave Shaw


________________________________
From: "Hiebert, Chris" <chris.hiebert@xxxxxxxxxxxxxx>
To: RPG programming on the IBM i ( AS/400 and i Series) <rpg400-l@xxxxxxxxxxxx>
Sent: Monday, December 16, 2013 5:54 PM
Subject: RE: SQLRPGLE performance problem after update from 6.1 to 7.1


It sounds like you have could have into an issue where the program object has run out of space to store the Access plan.

Are the SQL statements static?

The command PRTSQLINF will allow you to see the SQL information stored in the program object.

If it is an issue of program size, recompiling the program will clear out all the static access plans.
Converting the SQL to dynamic SQL will potentially solve the issue, since the Access plan is not stored with the program object.

It may be better to ask why the program needs to constantly rebuild the access plan, and if a redesign of the SQL is in order.
Using parameterized SQL may increase performance since the system can look up the access plan independently of the values in a where clause.
i.e. (SELECT FIELD LIST FROM FILE WHERE Field1 = ? and Field = ?)


FYI,
I had a SQL-RPGLE program with a static SQL statement that was accessing an alias. The alias was being recreated at least times per program call.
The alias was being recreated in QTEMP to point to a file in different library and each time the statement was executed over the new alias.
Over the course of 12 months the Access Plan for the static SQL exceeded the max for the program object and the SQL statements began failing.
I ended up converting the program to a Dynamic SQL-RPGLE program.