Very slow SQL query.

Question

Very slow SQL query.

David Kranes

SSCrazy

Points: 2523
More actions
September 22, 2009 at 8:25 am

#226466

Hello, I have to queries that are the same except for a like vs a substriing in the where clause. The query with the substring runs very fast where the query with the like clause runs painfully slow.
Below are the queries.
SELECT customer.customer_no, customer.customer_id, upper(first_name) 'first_name', upper(last_name) 'last_name',
telephone_no, address_1, post_code, email_address , address_match_key
FROM customer, address
WHERE alpha_key = 'BEDOYA TERE'
and customer.customer_id=address.customer_id
AND customer.active_address_id=address.address_id
and substring(address_match_key, 1, 5) = '07093'
SELECT customer.customer_no, customer.customer_id, upper(first_name) 'first_name', upper(last_name) 'last_name',
telephone_no, address_1, post_code, email_address , address_match_key
FROM customer, address
WHERE alpha_key = 'BEDOYA TERE'
and customer.customer_id=address.customer_id
AND customer.active_address_id=address.address_id
and address_match_key LIKE '07093%'
The address table has a nonclustered index on the address_match_key column. All stats are up to date.
I have attached the query plans.
Best Regards,
~David

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

It looks like your execution plans got hosed when you posted them. They're riddled with garbage from the formatting. Was that the XML plan? You'll need to re-post the execution plans and perhaps surround them with the proper tags. Please use the Preview option to make sure they look correct.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

David Kranes SSCrazy Points: 2523 More actions · Answer 2

What is the best way to present the plans? I copied them from SSMS and put code tags around them.

Best Regards,

~David

David Kranes SSCrazy Points: 2523 More actions · Answer 3

When I include a wildcard on both sides of the like string it uses the better plan and returns immediately.

SELECT customer.customer_no, customer.customer_id, upper(first_name) 'first_name', upper(last_name) 'last_name',

telephone_no, address_1, post_code, email_address , address_match_key

FROM customer, address

WHERE alpha_key = N'BEDOYA TERE'

and customer.customer_id=address.customer_id

AND customer.active_address_id=address.address_id

and address_match_key LIKE N'%07093%'

Best Regards,

~David

John Rowan SSC Guru Points: 56440 More actions · Answer 4

Here's an example of getting the plan into the forum thread.......

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Zipping them almost always works.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

David Kranes SSCrazy Points: 2523 More actions · Answer 6

The zipped query plans are attached. The query with the like in it work well sporatically but the query with the substring works well all the time. Thank you.

Best Regards,

~David

David Kranes SSCrazy Points: 2523 More actions · Answer 7

What would cause the SS optimizer to choose the wrong plan. The same query with index hints runs far better that the path taken by SS. Here are the 2 queries:

Slow:

SELECT customer.customer_no, customer.customer_id, upper(first_name) 'first_name', upper(last_name) 'last_name',

telephone_no, address_1, post_code, email_address , address_match_key

FROM customer, address

WHERE alpha_key = N'BEDOYA TERE'

and customer.customer_id=address.customer_id

AND customer.active_address_id=address.address_id

and address_match_key LIKE N'07093%'

Fast one:

SELECT customer.customer_no, customer.customer_id, upper(first_name) 'first_name', upper(last_name) 'last_name',

telephone_no, address_1, post_code, email_address , address_match_key

FROM customer (index (customer_x2)), address (index (address_x0))

WHERE alpha_key = 'BEDOYA TERE'

and customer.customer_id=address.customer_id

AND customer.active_address_id=address.address_id

and address_match_key LIKE '07093%'

The fast one forces index usage.

Best Regards,

~David

John Rowan SSC Guru Points: 56440 More actions · Answer 8

Can you post your table DDL for customer and address and include the index definitions?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 9

David Kranes (9/22/2009)
What would cause the SS optimizer to choose the wrong plan. The same query with index hints runs far better that the path taken by SS. Here are the 2 queries:
Slow:
SELECT customer.customer_no, customer.customer_id, upper(first_name) 'first_name', upper(last_name) 'last_name',
telephone_no, address_1, post_code, email_address , address_match_key
FROM customer, address
WHERE alpha_key = N'BEDOYA TERE'
and customer.customer_id=address.customer_id
AND customer.active_address_id=address.address_id
and address_match_key LIKE N'07093%'
You're using the "old style" of joins in the WHERE clause. My recommendation would be to try using ANSI joins (like INNER JOIN) and see if the optimizer makes better choices without (ugh!) having to use index hints.
Fast one:
SELECT customer.customer_no, customer.customer_id, upper(first_name) 'first_name', upper(last_name) 'last_name',
telephone_no, address_1, post_code, email_address , address_match_key
FROM customer (index (customer_x2)), address (index (address_x0))
WHERE alpha_key = 'BEDOYA TERE'
and customer.customer_id=address.customer_id
AND customer.active_address_id=address.address_id
and address_match_key LIKE '07093%'
The fast one forces index usage.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Rowan SSC Guru Points: 56440 More actions · Answer 10

That's where I was going to go with this too Jeff, the ANSI JOIN syntax. I wanted to see the table structure though because I noticed that the join is on both Customer_ID and Address_ID which looked funny. This may be a case for normalization as well.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

David Kranes SSCrazy Points: 2523 More actions · Answer 11

Good point! I did re-write the query but it made no difference. Here is the query with the index hints which works well. It still needs the hints. Arg!!!

SELECT

customer.customer_no,

customer.customer_id,

upper(first_name) 'first_name',

upper(last_name) 'last_name',

telephone_no,

address_1,

post_code,

email_address,

address_match_key

FROM

customer (index (customer_x2)) INNER JOIN address (index (address_x0))

ON customer.customer_id=address.customer_id

AND customer.active_address_id=address.address_id

WHERE

alpha_key = 'BEDOYA TERE'

AND address_match_key LIKE N'07093%'

Best Regards,

~David

David Kranes SSCrazy Points: 2523 More actions · Answer 12

Would collation setting have anything to do with this type of performance issues?

Best Regards,

~David

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

As asked before, would you please post the DDL for your table and the indexes?

David Kranes SSCrazy Points: 2523 More actions · Answer 14

Sorry for the delay in providing this. I have attached the DDL.

Best Regards,

~David