RE: Customer Lookup Redux!

Doug,

Your lucky to have such a well defined requirement. Build a new column
composed of the letters you know you will have to search on:
substr(firstname,1,1) + substr(lastname,1,3). Index it.

12 million rows isn't enough to get excited about in terms of fancy index
strategies.

In the CRM world, this is a typical problem - you need to see if you've got
the customer in your table before deciding to do an insert or an update.
The name in your database has been standardized, you standardize the name
coming in, then you query against the db with whatever static information
you have to go on. Name, DOB, SSN (if you can get it) and whatnot - address
is a poor choice since 11% of the people move evey year. You use basically
the same technique, build an index on the some meaningful portion of the
columns which will return at least the customer in question and some
(tunable) set of the customers around it. Those are the records you want to
check.

Your mileage will vary according to your needs and the data you have to work
with.

Regards,
Jack Parker

-----Original Message-----
From: Doug Kostelnik
[mailto:db2udbdba-ezmlmshield-x59792502.[Email address protected]
Sent: Sunday, October 29, 2006 1:19 AM
To: LazyDBA Discussion
Subject: Customer Lookup Redux!

Greetings Again,

I have been playing with the soundex() function in an effort to perform
faster customer lookups on my transactional database. I ended up with
some interesting results, but nothing that I think will help me resolve
my issue.

Let me as the question in a slightly different way:

For those of you who have large customer databases (mine is 12+Million),
what solution(s) have you employed to get a fast response on a partial
name lookup? Example: Rather than having an end user type in Mary Smith
to pull up a customer records, the user enters 'M' for the first name
and 'SMI' for the last name. Up until this point the requirement was
first letter of the first name and the entire last name - spelled
correctly.

Realms of solutions could include such things as:

* Database setup (i.e. partitioning or other database level
attributes)

* Table design (i.e. triggers or generated columns)

* Use of functions in table design, queries, or stored
procedures

* Stored Procedures

* Slick queries.

My customer table has separate columns for first and last name. The
current proposed minimum standard is that an end user has to enter the
first letter of the customer's first name and the first three letters of
the customer's last name. The list of customers must always be returned
sorted in the correct order. This means:

1) A clustered index with frequent reorgs will not be an adequate
substitute for an order by clause.

2) No other requirements on the name search can be defined (i.e.
Make the end user enter a zip code or state)

3) The solution has to work for both low and high cardinality
searches.

4) Rationalizations on the silliness of the minimum requirements I
have to work with will not be useful unless backed with a strong
argument.

What I am looking for is a "big picture" solution to the issue. The VP,
President, and CEO of the company want our internal applications to have
the same lookup functionality of our "canned" applications.
Unfortunately our canned applications have small subsets of the entire
consolidated customer database.

Any input would be appreciated. Apologies for the length of this plea,
but I want to be concise in expressing the task that management has
placed on my group.

Douglas Kostelnik

Senior Database Administrator/Architect

<html>
 <align=justify> 
_____________________________________________________________ 
The information transmitted is intended only for the person(s) or entity to
which it is addressed and may contain confidential and or privileged
material and should be treated as a confidential AAA Auto Club South
communication. If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited. If you are not the intended recipient,
please contact the sender and delete the material from any
computer.
</html>

---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

DB2 & UDB email list listserv db2-l LazyDBA home page