What is wrong with this SQL 1

[funforus]

Hi All,
I've been trying to write a 'record-matching' statement for our customer database. It is comparing the First Name, Middle Name and Last Name and scoring the potential matches. The first criteria is a match on the first three characters of the customer's last name. The SQL is below.

The problem is that it is timing out for certain customers, and not just those that have more of those initial 3-character matches. Does anyone see any glaring issues with the statement? I got the basic code from another programmer, so I may be overlooking something. Thanks!


SELECT CUSTOMER_ID,
FIRST_NAME as CUST_FIRST_NAME,
SCORE_FNAME = CASE
WHEN [FIRST_NAME] = '' THEN 0
WHEN FIRST_NAME = [FIRST_NAME] THEN 20
WHEN SOUNDEX(REPLACE(FIRST_NAME,' ','')) = SOUNDEX(REPLACE([FIRST_NAME],' ','')) THEN 15
WHEN FIRST_NAME LIKE LEFT([FIRST_NAME],3) + '%' THEN 10
ELSE -10
END,
MIDDLE_NAME AS CUST_MIDDLE_NAME,
SCORE_MNAME = CASE
WHEN [MIDDLE_NAME] = '' THEN 0
WHEN MIDDLE_NAME LIKE LEFT([MIDDLE_NAME],1) + '%' THEN 5
ELSE 0
END,
LAST_NAME AS CUST_LAST_NAME,
SCORE_LNAME = CASE
WHEN [LAST_NAME] = '' THEN 0
WHEN LAST_NAME = [LAST_NAME] THEN 25
WHEN SOUNDEX(REPLACE(LAST_NAME,' ','')) = SOUNDEX(REPLACE([LAST_NAME],' ','')) THEN 15
ELSE 0
END,
CITY AS CUST_CITY
FROM CUST_DATA
WHERE LAST_NAME LIKE LEFT([LAST_NAME],3) + '%'
AND CUSTOMER_ID is not null

 

[gmmastros]

I think your problem is that the WHERE clause is not sargable. (Hint: do a google search on non-sargable.)

What this means is that every record in the database will need to be looked at to determine if the record should be included in the results. This can be bad for performance, especially if there are a large number of records involved.

I suggest...

1. Make sure you have an index on the last name field. IF there isn't an index, then create one.
2. Change your where clause to...

[tt][blue]
Where CUSTOMER_ID Is Not NULL
And [LAST_NAME] like Left(LAST_NAME, 3)
[/blue][/tt]

With the where clause written like this, the query optimizer should be able to use the index.

3. Is Customer_ID the primary key for this table? Can it contain NULLS? If it can't contain nulls, then remove the part AND Customer_ID is not null part of the where clause.

This may not resolve your problem, but hopefully it does. I encourage you to try these suggestions. Then, post pack to let us know how you made out.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[funforus]

Very interesting George. Thanks for the quick reply.

The last_name field is indexed and I did some reading on non-sargable. Is the left(last_name,3) function going to continue to render it non-sargable since it uses a function?

I won't be able to log on to the server until this afternoon to try.

Thanks again!

 

[gmmastros]

That depends on what it represents. Let me explain.

I assumed that [!][[/!]Last_Name[!]][/!] is the column name, and Last_Name (without the square brackets) represents data input from the user. If this is the case, then the expression should be sargable (indexes should get used). If you want to know for sure, then turn on the 'actual execution plan' and see what it tells you.

1. Load your qurey in to Query Analyzer (QA) or SQL Server Management studio(SSMS).
2. If QA, press CTRL-K. For SSMS, Press CTRL-M.
3. Run the query

You will see an execution plan tab. Click on it. If you see a table scan, then you know that the index is NOT being used. However, if you see an index scan or seek, then you know the index is being used.

Good Luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[funforus]

A star to George because I learned so much today. You are great!

It turns out that Middle_Name is not indexed and so is causing a slow Bookmark Lookup on the immense main records table. (It turns out that cust_data is actually a view containing 9 tables)

The only way I could have discovered this was thanks to George and his instructions regarding the 'Actual Execution Plan' which I will be sure to use in the future.

Thank you!