SQL Server 2000:为什么这个带变量的查询与不带变量的查询相比这么慢? [英] SQL Server 2000: Why is this query w/ variables so slow vs w/o variables?

问题描述

我不明白为什么这个查询在有变量和没有变量的情况下会如此缓慢.我阅读了一些需要启用动态参数"的地方，但找不到在哪里执行此操作.

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

推荐答案

c.Received_Date"是什么数据类型?

What datatype is "c.Received_Date"?

如果不是日期时间，则该列将转换为日期时间，因为@BeginDate/@EndDate 是日期时间.这称为数据类型优先级.这包括列是否为 smalldatetime(根据链接)，因为 datetime 几乎具有最高优先级

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

对于文字，SQL Server 计算出最好先查找后跟书签查找，因为这些值是文字.

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.

对于使用变量的查询，它采用一般情况，因为如果值发生变化，它可以重用计划.一般情况是避免书签查找，在这种情况下，您有一个 PK(聚集索引)扫描

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

在这种情况下，您可以尝试使用索引提示来强制它，但如果范围太宽，它会很慢.或者您可以删除 SELECT * (无论如何都是不好的做法)并替换为 SELECT col1, col2 etc 并使用 覆盖索引

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

这篇关于SQL Server 2000:为什么这个带变量的查询与不带变量的查询相比这么慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！