Re: OPNQRYF performance using joins with a large file

Joe,

Try changing the File parameter to FILE(VELCUST SLHSTDTP)  and JFLDS 
accordingly. Always
better to have the smaller file as the primary.

HTH

Paul Tuohy

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
> +---




+---
| 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
+---