SQL Server 2000: Why is this query w/ variables so slow vs w/o variables?

Question

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

Answer 1

What datatype is "c.Received_Date"?

If it isn't datetime then the column will be converted to datetime because @BeginDate/@EndDate are datetime. This is known as data type precedence. This includes if the column is smalldatetime (as per the link) because datetime has almost the highest precedence

With constants, the optimiser will use the column datatype

The conversion means no index seeks can be used in the plan, which is the cause.

Edit, after seeing query plans

For the literals, SQL Server worked out that the a seek followed by bookmark lookup is best because the values are literals.

Generally, bookmark lookups are expensive (and incidentally one reason why we use covering indexes) for more than a handful of rows.

For the query using variables, it took the general case because if the values change it can reuse the plan. The general case is avoid the bookmark lookups and in this case you have a PK (clustered index) scan

Read more about why bookmark lookups are usually a bad thing on Simple-talk

In this case, you could try an index hint to force it but if the range it too wide it will be really slow. or you could remove SELECT * (bad practice anyway) and replace by SELECT col1, col2 etc and use a covering index

Answer 2

SET STATISTICS IO ON
SET STATISTICS TIME ON

number of scans and logical reads?

Answer 3

You've mentioned that the same query on the same data on a different server runs fast.

Is the hardware identical, or at least reasonably similar?

• processors - same number?
• Is any processor hyperthreaded?
• Is the physical layout of the disks the same (disk speed, separate spindles for data, log, tempdb?)

This behavior can often be seen by out of date statistics.

use dbfoo;
go
exec sp_updatestats
go

Lastly, compare SQL settings on each box:

exec sp_configure 'show advanced options', '1'
go
RECONFIGURE
exec sp_configure;
go