SQL Server 2000: Why is this query w/ variables so slow vs w/o variables?
- by William DiStefano
I can't figure out why this query would be so slow with variables versus without them. I read some where that I need to enable "Dynamic Parameters" but I cannot find where to do this.
DECLARE
@BeginDate AS DATETIME
,@EndDate AS DATETIME
SELECT
@BeginDate = '2010-05-20'
,@EndDate = '2010-05-25'
-- Fix date range to include time values
SET @BeginDate = CONVERT(VARCHAR(10), ISNULL(@BeginDate, '01/01/1990'), 101) + ' 00:00'
SET @EndDate = CONVERT(VARCHAR(10), ISNULL(@EndDate, '12/31/2099'), 101) + ' 23:59'
SELECT
*
FROM
claim c
WHERE
(c.Received_Date BETWEEN @BeginDate AND @EndDate) --this is much slower
--(c.Received_Date BETWEEN '2010-05-20' AND '2010-05-25') --this is much faster
