0

I've realized lately that queries are much slower when I reference a variable in the filter instead of the actual value in SQL Server 2008. For example:

SELECT table_A.fruit
, SUM(table_B.units)
FROM table_A LEFT JOIN table_B
ON table_A.fruit = table_B.fruit_name
WHERE time_of_sale BETWEEN '2012-11-01' AND '2012-11-25'

is faster than 

DECLARE @date1 DATE = '2012-11-01'
, @date2 DATE = '2012-11-25'

SELECT table_A.fruit
, SUM(table_B.units)
FROM table_A LEFT JOIN table_B
ON table_A.fruit = table_B.fruit_name
WHERE time_of_sale BETWEEN @date1 AND @date2

Would changing my query to this make the query as fast as the first one I posted:

DECLARE @date1 DATE = '2012-11-01'
, @date2 DATE = '2012-11-25'

DECLARE @exec VARCHAR(8000)

SELECT @exec =
'
SELECT table_A.fruit
, SUM(table_B.units)
FROM table_A LEFT JOIN table_B
ON table_A.fruit = table_B.fruit_name
WHERE time_of_sale BETWEEN ''' + @date1 + ''' AND ''' + @date2 + '''
'
EXEC(@exec)
eek
  • 688
  • 2
  • 21
  • 32
  • Depending on the build of SQL Server and settings like parameterization and optimize for ad hoc workloads, the dynamic SQL version could perform worse. Is the query slow now? Are there appropriate indexes in place? In some cases dynamic SQL might offer a chance at a better plan, but it's highly doubtful in this simple scenario. – Aaron Bertrand Nov 27 '12 at 19:18
  • I gotcha. There are indexes in place; it's just that my database is massive. The date range above can easily consist of 30 million rows. – eek Nov 27 '12 at 19:40
  • 1
    Size and complexity are two completely different things in this context. The query plan here should be relatively simple - a range scan (labeled as a seek) if the date range yields a small number of rows, or a table/index scan if it really does yield 30 million rows. Dynamic SQL helps improve scenarios where you have many optional parameters and you want to force different plans depending on which parameters are populated, instead of always getting the plan that was compiled with the first set of parameters that happened to be used (which may not be optimal for other param combinations). – Aaron Bertrand Nov 27 '12 at 19:46

0 Answers0