optimization via selection sets/indices

[Jeff Harrison]

--- Robert Haussmann <haussma at nextdimension.net>
wrote:

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

Yes, I think your selection set is too complex for
index scan.  Try eliminating the selector sentence,
selection group, and all of your "OR" logic from the
selection set.  Place the "OR" logic in the -v table.

Of course you should also make sure that either
PFIXS=ON or you are using the -j on the command line.

Good Luck.

Jeff Harrison
jeffaharrison at yahoo.com

Author of JHImport and JHEXPORT. The fastest and
easiest way to create code for filePro Imports and
Exports. 

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com