JBA/SQL

[Jim Sehi <jsehi@xxxxxxxx>]

We had to write a new customer search facility to replace the two provided
by JBA because it was inadequate.  I attempted to do this using and SQL RPG
program and using this over SLP05/OEP20, the two customer files.

The problem is that our customer address file (SLP05) now contains
approximately 1 million records with multiple companies and the SQL is
dying slowly.  I have built access paths over SLP05 to optimize query
performance so that the program can use key row positioning when possible.  

The problem with this is that one option allows the user to perform a query
where the program attempts to find records where the customer name is LIKE
'%NAME%' (a CONTAINS equivalent).  This causes the program to position the
cursor at the first record for the given company and forces it to look at
each record in that company (approximately 500,000 records).  Needless to
say, the performance is not acceptable.

Has anyone done something similar?  If so, what was your approach?  The
only alternative to this that I can think of is to build a word search
index, parsing out the customer name into individual words and reading
through the file with RPG.
******************************
Jim Sehi
Sr. Programmer/Analyst
Hach Company
5600 Lindbergh Drive
Loveland, Colorado  80538

Email:  jsehi@hach.com
Phone:  970-663-1377  x2538
Fax:    970-669-2932
******************************

+---
| This is the JBA Software Users Mailing List!
| To submit a new message send your mail to JBAUSERS-L@midrange.com.
| To subscribe to this list send email to JBAUSERS-L-SUB@midrange.com.
| To unsubscribe from this list send email to JBAUSERS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: doug333@aol.com.
+---