RE: accessing the records in a history file

Encoded Vector Indexes are great for enhancing the performance of adhoc queries against large files.  Not sure if this fits your situation exactly, but I would do one over DATE, one over TIME and one over Customer ID.  Obviously, if you always know what criteria and sorting you are going to do, a regular logical would work just as well.  But if you have this large of a file, you need to have some permanent access paths over it that will make accessing the records you want faster.  This will help regardless of whether you use OPNQRYF or SQL.

Dave

-----Original Message-----
From: booth@martinvt.com [mailto:booth@martinvt.com]
Sent: Thursday, December 21, 2000 11:44 AM
To: midrange-l@midrange.com
Subject: accessing the records in a history file

I'd like some insight from folks on this scenario:  There is a history
file of records dating from 1994.  Records are added each day and now
there are 3.6 million records in the file.  The odds of anyone going back
to look up something in 1994 are slim but management won't purge any
records.  Obviously the newest records are at the end of the file.  All of
the logicals have the customer ID number as the first key. One logical has
a key of the customer ID and then date and time.

Each business day there is at least one and sometimes five or more reports
written for all activity during some recent time period (a workshift, a
day, a week, whatever).

This process is becoming a performance pig; additionally there is a need
for more function.   What sorts of suggestions do you people see as
possible enhancements to this?  SQLRPGLE? OPNQRYF? a new Logical on date
and time alone? #GSORT?

Any suggestions are appreciated.  thanks in advance.

_______________________
Booth Martin
Booth@MartinVT.com
http://www.MartinVT.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
+---