RE: OPNQRYF performance using joins with a large file

[Joe Teff <jteff19@xxxxxxx>]

Bob Larkin wrote:

> The Query Optimizer is will tell you a little about what it is doing, Simply 
>start
> debug, then run the job. When in debug mode, the system will send messages to 
>the job
> log, detailing how it chose the access paths to use, and sometimes suggesting 
>logical
> paths that need to be created.

I looked at the Query Optimizer messages when this feature first came out. It 
was
interesting, but didn't really tell me anything that I didn't already know. 
This was the
first time that I have run complex queries over a large file and I forgot about 
this
feature. Thank you for reminding me.

My query statement was:

OPNQRYF  FILE(SLHSTDTP VELCUST)  FORMAT(SLHSTDTP)  +
                   JFLD((WCUST# CUSTNO))  OPTIMIZE(*FIRSTIO) +
                   OPTALLAP(*YES)  KEYFLD((WITEM)) +
                   QRYSLT('WDATE %RANGE('01/01/98' '08/31/98')')

I have indexes over my 12,000,000+ record history file by both Customer # and 
Item #.
I expected the optimizer to choose the index by Customer # to join with the 
VELCUST
file. This would have eliminated all but about 65,000 records from the history 
file and
building the index by Item # would have been fairly easy.

By ordering the resulting set of records by Item #, I would then optimize my 
RPG. It
would only have to chain to the Item Master once for each item.

The optimizer actually choose the index by Item # because of the key field. It 
also
used the history file as the 1st file and the VELCUST file as the 2nd file. 
This was also
due to the key.

By changing the keyfield to *NONE, the optimizer choose the index by Customer # 
and
used VELCUST as the 1st file (VELCUST will normally contain between 1 and 20
Customer #'s). My job went from running 5+ hours to less than a half hour under 
the
worst scenario.

I haven't had a chance to digest all of the changes in V4R3. I wonder if the 
enhancements
for access path building will handle this scenario better. By this, I mean that 
it would use
both the Customer # index and the Item # index to quickly create the one I 
needed?

Joe Teff

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---