RE: Fastest string scan

["Kahn, David" <KAHN@xxxxxxxxxxxxxxxxxx>]

Denis Robitaille (drobitaille@Cascades.com) wrote:

>Currently, i use the SCAN opcode to do the
>scanning. It works great in the test environnement but in production
>(with a lot more products), it is way to slow. I do load the subfile a
>page at a time. I think the delay is due to the SCAN opcode and i am
>thinking of replacing it with the QCLSCAN API. But before going any
>further, i tought i'd ask around.

I think you'll have more luck with the SQL or OPNQRYF options that other
people have already suggested. The problem is not the efficiency of the
scan but the number of records you're reading to fill the first subfile
page. If there are no hits you will have to read the entire product file
for one interactive response - a definite no-no. In my experience the
SCAN opcode is PDQ. If you replace it with QCLSCAN you'll simply be
exchanging a very efficient embedded routine with an external call to
another (or possibly the same?) very efficient embedded routine for
every record read.

A possible alternative would be to build a separate index file in an
overnight batch job. There would be an entry for each occurrence of each
word in the description field of your product file. You might want to
have a list of words not to include such as "and" and "the". The record
would contain the master file key. Now your enquiry does not need to
scan the descriptions; you simply use the search word to CHAIN (or SETLL
if you prefer <G>) to the index file and loop on READE. This approach
has some drawbacks, however. 1) The index file could get very large. 2)
The program to rebuild it will probably run for a long time. 3) The
search won't turn up records newly added or changed since the last
rebuild unless you add a routine to the product file update to maintain
the index. If you do amend the product file update it's probably a good
idea to put the changes into a data queue and let a background job
maintain the index.

Dave Kahn - TCO, Tengiz, Kazakstan
=========

e-mail:  kahn@tengizchevroil.com    (until September 30th)
         dkahn@cix.compulink.co.uk  (from  October 1st)


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to "MIDRANGE-L@midrange.com".
| To unsubscribe from this list send email to MAJORDOMO@midrange.com
|    and specify 'unsubscribe MIDRANGE-L' in the body of your message.
| Questions should be directed to the list owner/operator: david@midrange.com
+---