Re: OPNQRYF performance using joins with a large file

Maybe there is no need for that Join .....

Have a View over your sales History Detail File by Customer and Invoice Date
(preferably *YYMD format )

WCUST#
WDATE  ..... format *YYMD

(If you do not have this Date format maybe is a good Idea to reconsider 
Recreating the Sales Detail File)

Use a simple program to SETLL by CUSTOMER / INVOICE DATE (From Date) and READE 
by CUSTOMER
until you reach the To Date Value.(If the Date is in ascending Order)  Build a 
workfile with the WITEM Information.

Use the New Work File (Or a New View of that workfile) to generate your Report.

This probably can speed up your processing.

Also Consider some sort of nightly, weekly, monthly, or on demand generation of 
sales summary
files.

Gradually you will be building the foundations of your Data Warehouse.

Your Users will love the fact that Very Quickly that Information Is ALWAYS 
available !!!!!


Regards                                                                         
                                         JORGE




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