SQL Server 2000: why is this w / variable query so slow as without variable?

Question

I cannot understand why this request will be so slow with and without variables. I read some where I need to enable "Dynamic Settings", but I can not find where to do it.

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 data type is c.Received_Date?

If it is not datetime, then the column will be converted to datetime, because @ BeginDate / @ EndDate is the date and time. This is called data type priority . This includes if the column is smalldatetime (by reference), since datetime has almost the highest priority.

With constants, the optimizer will use the column data type

Conversion means that the pointer that is the cause cannot be used in the plan.

Edit after viewing query plans

For literals, SQL Server decided that a search followed by a bookmark search is best because the values ​​are literals.

As a rule, search by road bookmarks (and by the way, one of the reasons why we use coverage indexes) for more than a few lines.

For a query using variables, he adopted the general case, because if the values ​​change, he can reuse the plan. In general, avoid bookmark searches, in which case you scan PK (clustered index)

Learn more about why bookmark searches usually don't work well on Simple-talk.

In this case, you can try the index pointer to force it, but if the range is too wide, it will be very slow. or you can remove SELECT * (in any case, bad practice) and replace with SELECT col1, col2 etc and use the coverage index

Answer 2

 SET STATISTICS IO ON SET STATISTICS TIME ON 

the number of scans and logical readings?

Answer 3

You mentioned that the same request for the same data on another server is fast.

Is the hardware identical or at least similar?

• processors - the same number?
• Is any processor hyperthreading?
• Is the physical location of the drives the same (disk speed, separate spindles for data, log, tempdb?)

This behavior can often be seen from outdated statistics.

 use dbfoo; go exec sp_updatestats go 

Finally, compare the SQL settings in each window:

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