× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  • Subject: Re: OPNQRYF performance using joins with a large file
  • From: Bob Larkin <blarkin@xxxxxx>
  • Date: Wed, 16 Sep 1998 01:19:19 -0700

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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.