optimization via selection sets/indices

[GCC Consulting]

> -----Original Message-----
> From: filepro-list-bounces at lists.celestial.com 
> [mailto:filepro-list-bounces at lists.celestial.com] On Behalf 
> Of Robert Haussmann
> Sent: Monday, October 11, 2004 12:01 PM
> To: 'filePro List'
> Subject: optimization via selection sets/indices
> 
> We have a need to run real-time queries against a large 
> database (2 million records).  Using rreport and selection 
> processing (-v) takes much too long.  I was hoping to utilize 
> selection sets (-s), created dynamically, to quickly scan 
> through the relevant records.  Creating the .SEL files 
> dynamically was no problem, but we aren't seeing any time 
> savings.  I believe rreport is not taking advantage of the 
> existing automatic indices, but I'm not sure why (fileProODBC 
> 1.0.13 if it matters).
> 
> A sample .SEL file would be:
> 
> a and b and c and d and (e or f or g)
> a:9:eq:000575
> b:2:ge:07/01/2001
> c:2:le:12/31/2004
> d:16:eq:P
> e:3:eq:P
> f:3:eq:D
> g:3:eq:TR
> 
> Automatic indices exist on fields 9, 2, and 16 (in this case, 
> terms a, b, c, and d).  In this particular example, there are 
> about 25 out of the 2M records that fit the first criteria (9 
> eq "000575").  Shouldn't rreport scan these records only?  Is 
> the selection set too complex?
> 
> Any help would be appreciated.
> 
> Bob Haussmann
> Tabor Children's Services, Inc.
> 
Bob,

Create a - lookup in your -v selection process on field 9.

Then apply the balance of you logic to only those records selected.

One of my clients has a file of almost 1 million records and selects records by
customer code for printing edi orders received.  Two variables, customer code
and today's date.  Index is on customer code, selection based on @cd of the
record.  Takes just a few seconds to select the records.

The html help file includes a very good explanation of how to set up a - lookup
if you haven't already used this type of coding.

Richard Kreiss
GCC Consulting