People are always surprised when I tell that their queries will run slower when they use local variables in their queries. Let me explain: If you use a local variable in a query predicate instead of a parameter or literal, the optimizer resorts to a guesstimate for selectivity of the predicate. Use parameters or literals in the query instead of local variables, and the optimizer will do a better selecting a query plan. For example, execute both of these queries. The first one uses a local variable and the second one a literal:
use adventureworks
--first one with a local varaiable
declare @StartOrderDate datetime
set @StartOrderDate = '20040731'
select * from Sales.SalesOrderHeader hdr, Sales.SalesOrderDetail dtl
WHERE hdr.SalesOrderID = dtl.SalesOrderId
AND hdr.OrderDate >= @StartOrderDate
--secondeone with a literal
SELECT * FROM Sales.SalesOrderHeader hdr, Sales.SalesOrderDetail dtl
WHERE hdr.SalesOrderID = dtl.SalesOrderId
AND hdr.OrderDate >= '20040731' The number of rows from Sales.SalesOrderHeader that the optimizer estimates will qualify vs. the condition hdr.OrderDate >= @StartOrderDate is 9439.5, which is exactly 30% of the size of the table. (the observations that follow are based on my SQL Server 2005 version; your results may differ depending on your SQL Server version, available memory, etc.).
The cardinality of the result set for the predicate "hdr.OrderDate >= '20040731'" is estimated as 40 for a selectivity of 0.13%. The plan chosen for this query uses a nested loop join instead of a merge join because of this improved estimate.
To force SQL Server to optimize a query every time it is run, and use the values of local variables to estimate cardinality and cost during optimization of the query, add the RECOMPILE query hint to the query. For example, modify the first of the two preceding example queries shown to:
SELECT * FROM Sales.SalesOrderHeader hdr, Sales.SalesOrderDetail dtl
WHERE hdr.SalesOrderID = dtl.SalesOrderId
AND hdr.OrderDate >= @StartOrderDate
OPTION(RECOMPILE)
This eliminates the problem of bad estimates due to local variables, at the cost of recompiling the query every time you run it. Compilation overhead can be quite high, especially for frequently-run, small queries. To avoid this overhead, further modify your application to use parameterized batches or stored procedures instead of dynamic SQL.
To eliminate the use of local variables, consider :
-
Rewriting the query to use literals directly in queries instead of assigning them to variables and then using the variables in the queries;
-
Using sp_executesql or API server cursors with parameters that replace your use of local variables;
-
Using a stored procedure with parameters that replace your use of local variables.
Hope this helps,
Eric Vaillancourt
Reference : http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx