Re: JBA/SQL Customer Search

[Randy_Smith@xxxxxxxxx]

Jim,

What part of the JBA customer search was inadequate?

JBA does what you said you wanted to do, which is parse out the customer
name, although it only takes the first five characters of each "word" and
stuff an index file.  If five characters is not enough, you could mimic the
program with a longer index field, say 8 or 10 characters.  It would be
fairly simple to extract to that file on an occasional basis, and also
change the maintenance programs to also update the new index file.





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.




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