Unexpected SQL Server 2008 Performance Tip: Avoid local variables in WHERE clause
- by Jim Duffy
Sometimes an application needs to have every last drop of performance it can get, others not so much. We’re in the process of converting some legacy Visual FoxPro data into SQL Server 2008 for an application and ran into a situation that required some performance tweaking. I figured the Making Microsoft SQL Server 2008 Fly session that Yavor Angelov (SQL Server Program Manager – Query Processing) presented at PDC 2009 last November would be a good place to start. I was right. One tip among the list of incredibly useful tips Yavor presented was “local variables are bad news for the Query Optimizer and they cause the Query Optimizer to guess”. What that means is you should be avoiding code like this in your stored procs even though it seems such an intuitively good idea. DECLARE @StartDate datetime
SET @StartDate = '20091125'
SELECT * FROM Orders WHERE OrderDate = @StartDate
Instead you should be referencing the value directly in the WHERE clause so the Query Optimizer can create a better execution plan.
SELECT * FROM Orders WHERE OrderDate = '20091125'
My first thought about this one was we reference variables in the form of passed in parameters in WHERE clauses in many of our stored procs. Not to worry though because parameters ARE available to the Query Optimizer as it compiles the execution plan.
I highly recommend checking out Yavor’s session for additional tips to help you squeeze every last drop of performance out of your queries.
Have a day. :-|
