Index scan question

[GCC Consulting]

> -----Original Message-----
> From: filepro-list-bounces+gccconsulting=comcast.net at lists.celestial.com
>
[mailto:filepro-list-bounces+gccconsulting=comcast.net at lists.celestial.com]
On
> Behalf Of Don Coleman
> Sent: Friday, July 25, 2008 11:27 AM
> To: 'Kenneth Brody'; filepro-list at lists.celestial.com
> Subject: RE: Index scan question
> 
> 
> > -----Original Message-----
> > From: filepro-list-bounces+dcoleman=dgcreact.com at lists.celestial.com
> > [mailto:filepro-list-bounces+dcoleman=dgcreact.com at lists.celestial.com]
On
> > Behalf Of Kenneth Brody
> > Sent: Friday, July 25, 2008 11:06 AM
> > To: filepro-list at lists.celestial.com
> > Subject: Re: Index scan question
> >
> > Quoting Don Coleman (Fri, 25 Jul 2008 10:44:45 -0400):
> >
> > [...]
> > > Built an index on a YESNO field where for a *report export process
where
> > > I only want to select 11 (YESNO) eq "" (blank)
> > >
> > > There are 465,000 records in this file.   This export is to run every
3
> > > minutes so I would expect a maximum of 2000-3000 records (peak
periods)
> > to
> > > be selected for each 3 minute interval scan.  The number exported
could
> > drop
> > > to several hundred records or less during non-peak times.  However
when
> > I
> > > run the *report export process the app. still reads through 25,000-
> > 30,000
> > > records.  While that's not the entire file and some time has been
saved,
> > I
> > > don't understand why it is not only reading the exact records to be
> > > selected.  There is no select process involved and no "ne" statement
in
> > my
> > > selection set.  The selection set is simply:
> > >
> > > Group	Field		Description		REL	Value
> > > 	11		Exported (Y/N)	eq
> > [...]
> >
> > That doesn't make sense.  If index scan is turned off, it would read all
> > 465,000 records in the file.  If index scan is being used, filePro would
> > only read the records where field 11 is blank.
> >
> > Is there any chance that there are really only about 30,000 used records
> > in the file, and the rest are deleted?
> >
> > --
> > Read the truth behind the movie "Expelled" at
> > <http://www.ExpelledExposed.com>
> > --
> > KenBrody at BestWeb dot net        spamtrap:
> <g8ymh8uf001 at sneakemail.com>
> > http://www.hvcomputer.com
> > http://www.fileProPlus.com
> > _______________________________________________
> > Filepro-list mailing list
> > Filepro-list at lists.celestial.com
> > http://mailman.celestial.com/mailman/listinfo/filepro-list
> 
> Ken:
> 
> Yes, this file is archived each night, moving 80-90% of the records
> contained to an archive qualifier.  What you suggest now makes sense.  As
> the day goes on, the number of records being read is increasing, now over
> 40,000.  I tried adding another field to the selection set,
> Field #1(8,*) gt "" (blank) but it still is reading all the records.  I
also
> confirmed PFIXS=ON is in the environment.  What can I add to only read the
> records where field #11 is equal to ""(blank)?  This seems so simple yet
...
> 
> 
> Don Coleman
> Donald G. Coleman, Consultant
> 402 Andrew Circle
> Indiana, PA 15701
> dcoleman at dgcreact.com
> (724) 349-6302

If you do build an index on this field, you could use a lookup - in a -v
select process.  I have used this technique on a file with almost 500,000
records which selects by order date.  In another case, it selects specific
order details for printing.

In both cases the selection process runs very fast.

Nancy Palmquist may have a sample of this programming on her website

 http://www.vss3.com/


Richard Kreiss
GCC Consulting
rkreiss at gccconsulting.net