Re: SQLRPG Program performance -- MIDRANGE-L

Estimated run time is exactly that - "estimated". It is based on some assumptions that may or may not be true. In V5R2 and later, there were some things done to make this estimate better, using statistics.

In order to boost performance you should run the program in debug mode with no breakpoints. This will put optimizer messages into the job log. This access plan says it will do a table scan. This means either that there are no indexes against fields, say, in the WHERE clause or that the optimizer thinks most of the records will be returned anyway. This is probably because of the BETWEEN predicates, which are basically OR predicates and will tell the optimizer that there will likely be a lot of records returned. An AND predicate reduces the result set, an OR expands it.

Another problem is that with host variables it is not possible to use index range testing, because the values in the BETWEEN are not known at compile time.

Check the debug messages - see if it decided not to use an existing index and, if not, whether it recommends building any indexes.

Check the manual at InfoCenter on Query Performance (some such title - they change often ;-) ) for more on how the optimizer works. Everything I've said is in there somewhere, including the use of STRDBG.

HTH
Vern

At 05:12 AM 5/9/2005, you wrote:

I have a SQLRPG program, and it has multiple SQL Statements. When I run
PRTSQLINF for this program, it shows the estimated query run time, and most
of them have 1second estimated run time, but one of them has 8 seconds
estimated run time.

Statement:-

"C+          SELECT DISTINCT PTCNTL

C+          INTO :CNTL FROM EPTMST

C+          WHERE PTCNTL = :SCTL03 AND

C+          PTCMPY BETWEEN :S2LCO AND :S2HCO AND

C+          PTLOC  BETWEEN :S2LLC AND :S2HLC"

SQL4021  Access plan last saved on 09/05/05 at 11:53:23.


SQL4020  Estimated query run time is 8 seconds.


SQL402D  Query attributes overridden from query options file QAQQINI in
library QUSRSYS.

SQL4017  Host variables implemented as reusable ODP.


SQL4010  Table scan access for table 1.


My question is, is this estimated time really denotes the performance of the
program. And if yes, how can I improve the performance of it.

Waiting for reply

AH

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.