Very slow SQL query.

Question

Post reply

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

David Kranes

SSCrazy

Points: 2523

September 22, 2009 at 8:51 am

#1056847

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://qa.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

RBarryYoung

SSC Guru

Points: 143329

September 22, 2009 at 10:36 am

#1056950

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

David Kranes

SSCrazy

Points: 2523

September 22, 2009 at 11:25 am

#1056991

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

John Rowan

SSC Guru

Points: 56440

September 22, 2009 at 12:14 pm

#1057039

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: 1000539 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)
Intro to Tally Tables and Functions

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%'