Re: JBA/SQL Customer Search

We ended up adding some additional criteria to the list of things that
uniquely identify customers... ie. split them into domestic/international,
etc.  It's a really long story.

I have decided to do something similar to what JBA is doing, build a scan
index and use that for searches.

-----Original Message-----
From: Randy_Smith@omron.com <Randy_Smith@omron.com>
To: JBAUSERS-L@midrange.com <JBAUSERS-L@midrange.com>
Date: Wednesday, August 12, 1998 4:04 PM
Subject: Re: JBA/SQL Customer Search


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

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