Better performance by using a local var vs param in SQL Server?

I’d forgotten about this “issue” until I read on the ASP.NET forum someone else having the same problem. I can’t vouch for what happened to the that user but I can explain what I saw. Basically I had a very simple stored procedure that took an Id value in as a param and then happily used it with a query, something like (not the actual query for customer IP reasons):
Create Proc MyProc(@Id int)
As
Select Col1, Col2 from MyTable Where
Col3=@Id
 
The proc worked fine until the database data was scaled-up. Suddenly it performed very poorly and no amount of statistics updates would fix it. So I went through the usual steps of breaking the inner query out of the procedure and hard-coding the values:
 
Select Col1, Col2 from MyTable Where Col3=3
 
It worked great, so I started to add bits back until it went wrong again:
Declare @MyLocalId int
Set @MyLocalId = 3
Select Col1, Col2 from MyTable Where
Col3=@MyLocalId
 
Worked great…
Create Proc MyProc(@Id int)
As
Declare @MyLocalId int
Set @MyLocalId = 3
Select Col1, Col2 from MyTable Where
Col3=@MyLocalId
 
Worked great…starting to get concerned…
Create Proc MyProc(@Id int)
As
Declare @MyLocalId int
Set @MyLocalId = 3
Select Col1, Col2 from MyTable Where
Col3=@Id
 
Poor again…hmm…
Create Proc MyProc(@Id int)
As
Declare @MyLocalId int
Set @MyLocalId = @Id
Select Col1, Col2 from MyTable Where
Col3=@MyLocalId
 
Worked great!!!
What just happened? All I’ve done is de-reference the param into a local variable and used that. It worked and since I was under a lot of pressure at the time I forgot all about it. However, since reading that at least one other person is seeing the same issue I’ve decided to dig a little deeper. My first clue came from TechNet that stated that by using a local variable the query wouldn’t use statistics, now I’m not sure about that but maybe that’s something do with it. The reason that is plausible is because at low data volumes the procedure worked fine but with different statistics it started to perform badly. Although uncommon it’s not impossible for the Query Optimizer to get confused and produce a poor plan. If you remove its ability to use statistics it falls back to a sort of worst-case algorithm. In this situation it maybe that the default worst-case plan is better that its optimized attempt, in SQL 2005 you could then force the “correct” plan. But it’s still a theory at the moment, but I’ve dug out a Kimberly Tripp webcast on how params work so hopefully that may shed some light on the matter. More posts soon, in the meantime if anyone else has any ideas then please post.
 
Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s