Re: SQLRPG Program performance

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

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.