performance problem with sp_executesql

Question

Post reply

performance problem with sp_executesql

Viewing 7 posts - 1 through 8 (of 8 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!