Re: OPNQRYF performance using joins with a large file

The Query Optimizer is will tell you a little about what it is doing, Simoly 
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.
Bob Larkin
blarkin@wt.net

Joe Teff wrote:

> I have a sales history file (SLHSTDTP) that contains about 12,000,000
> records per year. Some of the fields in this file are:
>
>      WITEM        7,0        Item Number
>      WCUST#     5,0        Customer Number
>      WDATE       8L         Invoice Date - format is *MDY
>
> The physical is keyed with WITEM being the 1st key field. There are logicals
> with WCUST# and WDATE as the 1st key as well.
>
> I am creating a generic sales reporting program that allows records to be
> selected several different ways. Customers are grouped together by a Chain
> Code. The user can select one or more Chains; or one or more Customer
> Numbers. I take any Chain Codes and write all of the associated Customers
> to a workfile. If Customer Numbers are entered, I write them to the workfile
> instead. I now have a workfile (VELCUST) with a single field (CUSTNO 5,0)
> that contains all of the Customer Numbers to select for the report.
>
> To speed up my reporting, I will use an OPNQRYF statement to select only
> those history records for the customers I will report on. The statement looks
> like this:
>
>      OPNQRYF  FILE(SLHSTDTP VELCUST)  FORMAT(SLHSTDTP)  +
>                         JFLD((WCUST# CUSTNO))  OPTIMIZE(*FIRSTIO) +
>                         OPTALLAP(*YES)  KEYFLD((WITEM)) +
>                         QRYSLT('WDATE %RANGE('01/01/98' '08/31/98')')
>
> The date range is also a variable, I show it as though it is hard-coded here!
>
> This query is running very long. It also appears to be generating an 
>intermediate
> file. Is this because I'm using the join or is it because of something else?
> Although there are millions of records in the sales detail file, there is a 
>total of
> eight Customer Numbers in VELCUST and there is about 125,000 sales history
> records for these eight customers. I have tried using the logical with 
>Customer
> Number as the key instead of the physical, but it has not helped. I have also
> tried switching VELCUST and SLHSTDTP around on the FILE parameter, but
> that has not helped either. I have also tried it with the OPTIMIZE and 
>OPTALLAP
> parameters and without.
>
> If I go into Query/400, join SLHSTDTP with the Customer Master File and select
> the same Customer Numbers from the Customer Master; the query runs fairly fast
> (matter of a few minutes). My OPNQRYF statement is being cancelled after
> running in excess of a half hour.
>
> Does anyone have any suggestions?
>
> #1 - SQL is not an option, the customer does not have it and is not 
>interested in
> puechasing it.
>
> #2 - This is on V4R2. I'm promoting V4R3, but have not been able to get it 
>sold yet.
>
> Thanks, 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
> +---

begin:          vcard
fn:             Bob Larkin
n:              Larkin;Bob
org:            <A HREF="HTTP://web.wt.net/~blarkin/">Larkin Computer Consulting</A>
adr:            <A HREF="http://web.wt.net/~blarkin/">Bob and Diana's Page</A>;;;Houston;TX;<A HREF="http://web.wt.net/~blarkin/">;United States
email;internet: blarkin@wt.net
title:          Systems Consultant
x-mozilla-cpt:  ;4104
x-mozilla-html: FALSE
version:        2.1
end:            vcard