performance problem with sp_executesql

Question

Post reply

performance problem with sp_executesql

the_fenix

Right there with Babe

Points: 789
More actions
October 15, 2009 at 9:55 am

#136465

hi guys!
This is driving me nuts!!!!
Does anyone know why this query
exec sp_executesql N'Select X
From Addresses
Where Address like ''%'' + @Address + ''%'' and
ZipCode like ''%'' + @ZipCode',N'@Address nvarchar(23),@ZipCode nvarchar(5)',@Address=N'Address1',@ZipCode=N'ZipCode1'
may take FOREVER (2 minutes or more, I didn't wait to the end)
while the same query within a stored procedure
ALTER PROCEDURE spS_GetX
@Address varchar(200),
@ZipCode varchar(10)
AS
BEGIN
SELECT X FROM dbo.Addresses
WHERE Address like '%' + @Address and ZipCode like '%' + @ZipCode
END
takes only 6 seconds?
There is an index over the column ZipCode.
Why such a BIG difference??
Thanks in advance!

Viewing 8 posts - 1 through 7 (of 7 total)

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

the_fenix Right there with Babe Points: 789 More actions · Answer 1

No idea? It must be something related to sp_executesql. Thanks!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Sounds like parameter sniffing. Have you tried using EXEC(@string) instead/

[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]

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

crfenix (10/15/2009)
There is an index over the column ZipCode.

Which will never the actively used by this query as this

ZipCode like '%' + @ZipCode

Gets all the zip codes ending in @ZipCode.

Try getting me all the names from a phone directory ending in 'ith' , same rules apply here.

It may be used to scan through as its 'thinner' but not as a seek.

There is a subtle difference in your queries , in one you append a trailing '%' to the address in the othere you dont. Could be enough to cause a difference.

For futher help though, please see the link below on how to post performance issues.

Clear Sky SQL
My Blog[/url]

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 4

There are also other differences, which may affect the plan:-

@Address is nvarchar(23) in one, but varchar(200) in the other

@ZipCode is nvarchar(5) in one, but varchar(10) in the other

one query refers to dbo.Addresses, the other refers to Addresses... do these actually map to the same table.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

one more difference

in your dynamic query its Where Address like ''%'' + @Address + ''%'' where as in your procedure it is

WHERE Address like '%' + @Address

"Keep Trying"

the_fenix Right there with Babe Points: 789 More actions · Answer 6

Thanks for all your replies guys! You're simply GREAT

I'll analyze everything you said. I did other simper queries before this I pasted and I also had big differences of performance between using sp_executesql and not using it. Always on the same table and related to zipcode (column with index)

the_fenix Right there with Babe Points: 789 More actions · Answer 7

Dave Ballantyne (10/16/2009)
crfenix (10/15/2009)
There is an index over the column ZipCode.
Which will never the actively used by this query as this
ZipCode like '%' + @ZipCode
Gets all the zip codes ending in @ZipCode.
Try getting me all the names from a phone directory ending in 'ith' , same rules apply here.
It may be used to scan through as its 'thinner' but not as a seek.
There is a subtle difference in your queries , in one you append a trailing '%' to the address in the othere you dont. Could be enough to cause a difference.
For futher help though, please see the link below on how to post performance issues.

Dave Ballantyne. you're the man. Can't say that this explain the performance difference, but it's a bug on the query 🙂 the '%' must be at the end, not at the beggining. THANKS!