主页 / dba / 问题 / 34047
Accepted
Mikael Eriksson
Asked: 2013-02-04 23:58:37 +0800 CST

迄今为止的演员阵容是可悲的，但这是个好主意吗？

  • 772

在 SQL Server 2008 中添加了日期数据类型。

将datetime列强制转换date为sargable并且可以使用datetime列上的索引。

select *
from T
where cast(DateTimeCol as date) = '20130101';

您拥有的另一个选择是使用范围。

select *
from T
where DateTimeCol >= '20130101' and
      DateTimeCol < '20130102'

这些查询是否同样好，还是应该优先考虑一个？

sql-server

2 个回答

  1. Best Answer

    迄今为止，铸造 sargability 背后的机制称为动态搜索。

    SQL Server 调用一个内部函数GetRangeThroughConvert来获取范围的开始和结束。

    有点令人惊讶的是，这与您的文字值的范围不同。

    创建一个每页一行，每天 1440 行的表

    CREATE TABLE T
  (
     DateTimeCol DATETIME PRIMARY KEY,
     Filler      CHAR(8000) DEFAULT 'X'
  );

WITH Nums(Num)
     AS (SELECT number
         FROM   spt_values
         WHERE  type = 'P'
                AND number BETWEEN 1 AND 1440),
     Dates(Date)
     AS (SELECT {d '2012-12-30'} UNION ALL
         SELECT {d '2012-12-31'} UNION ALL
         SELECT {d '2013-01-01'} UNION ALL
         SELECT {d '2013-01-02'} UNION ALL
         SELECT {d '2013-01-03'})
INSERT INTO T
            (DateTimeCol)
SELECT DISTINCT DATEADD(MINUTE, Num, Date)
FROM   Nums,
       Dates

    然后运行

    SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT *
FROM   T
WHERE  DateTimeCol >= '20130101'
       AND DateTimeCol < '20130102'

SELECT *
FROM   T
WHERE  CAST(DateTimeCol AS DATE) = '20130101';

    第一个查询已1443读取，第二个查询已读取2883一整天，然后根据剩余谓词将其丢弃。

    该计划显示搜索谓词是

    Seek Keys[1]: Start: DateTimeCol > Scalar Operator([Expr1006]), 
               End: DateTimeCol < Scalar Operator([Expr1007])

    所以代替>= '20130101' ... < '20130102'它读取> '20121231' ... < '20130102'然后丢弃所有2012-12-31行。

    依赖它的另一个缺点是基数估计可能不像传统的范围查询那样准确。这可以在SQL Fiddle的修改版本中看到。

    表中的所有 100 行现在都与谓词匹配（日期时间相隔 1 分钟，都在同一天）。

    第二个（范围）查询正确估计 100 将匹配并使用聚集索引扫描。该CAST( AS DATE)查询错误地估计只有一行将匹配并生成一个带有键查找的计划。

    统计数据并没有被完全忽略。如果表中的所有行都相同datetime并且与谓词匹配（例如20130101 00:00:00or 20130101 01:00:00），则计划显示聚集索引扫描，估计有 31.6228 行。

    100 ^ 0.75 = 31.6228

    所以在那种情况下，估计值似乎是从这个公式得出的：

    下表显示了作为输入表基数 N 的函数的猜测合取数和结果选择性：

    | Conjuncts | Cardinality | Selectivity |
|-----------|-------------|-------------|
| 1         | N^(3/4)     | N^(-1/4)    |
| 2         | N^(11/16)   | N^(-5/16)   |
| 3         | N^(43/64)   | N^(-21/64)  |
| 4         | N^(171/256) | N^(-85/256) |
| 5         | N^(170/256) | N^(-86/256) |
| 6         | N^(169/256) | N^(-87/256) |
| 7         | N^(168/256) | N^(-88/256) |
| ...       |             |             |
| 175       | N^(0/256)   | N^(-1)      |

    如果表中的所有行都相同datetime并且它与谓词不匹配（例如20130102 01:00:00），那么它会回退到估计的行数 1 和带有查找的计划。

    对于表具有多个DISTINCT值的情况，估计的行数似乎与查询正在查找的行数相同20130101 00:00:00。

    如果统计直方图恰好有一个步骤，2013-01-01 00:00:00.000那么估计将基于EQ_ROWS（即不考虑该日期的其他时间）。否则，如果没有步骤，它看起来好像使用AVG_RANGE_ROWS了周围步骤中的 。

    由于datetime在许多系统中具有大约 3 毫秒的精度，因此实际重复值将非常少，这个数字将为 1。

    • 77

  2. 我知道这有来自 Martin 的长期 Great Answer®，但我想在较新版本的 SQL Server 中添加一些对行为的更改。这似乎只在 2008R2 之前进行了测试。

    使用新的USE HINT可以进行一些基数估计时间旅行，我们可以看到事情何时发生变化。

    使用与 SQL Fiddle 相同的设置。

    CREATE TABLE T ( ID INT IDENTITY PRIMARY KEY, DateTimeCol DATETIME, Filler CHAR(8000) NULL );

CREATE INDEX IX_T_DateTimeCol ON T ( DateTimeCol );


WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
     E02(N) AS (SELECT 1 FROM E00 a, E00 b),
     E04(N) AS (SELECT 1 FROM E02 a, E02 b),
     E08(N) AS (SELECT 1 FROM E04 a, E04 b),
     Num(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY E08.N) FROM E08)
INSERT INTO T(DateTimeCol)
SELECT TOP 100 DATEADD(MINUTE, Num.N, '20130101')
FROM Num;

    我们可以像这样测试不同的级别：

    SELECT *
FROM   T
WHERE  CAST(DateTimeCol AS DATE) = '20130101'
OPTION ( USE HINT ( 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_100' ));
GO

SELECT *
FROM   T
WHERE  CAST(DateTimeCol AS DATE) = '20130101'
OPTION ( USE HINT ( 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110' ));
GO 

SELECT *
FROM   T
WHERE  CAST(DateTimeCol AS DATE) = '20130101'
OPTION ( USE HINT ( 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_120' ));
GO 

SELECT *
FROM   T
WHERE  CAST(DateTimeCol AS DATE) = '20130101'
OPTION ( USE HINT ( 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_130' ));
GO 

SELECT *
FROM   T
WHERE  CAST(DateTimeCol AS DATE) = '20130101'
OPTION ( USE HINT ( 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140' ));
GO 

SELECT *
FROM   T
WHERE  CAST(DateTimeCol AS DATE) = '20130101'
OPTION ( USE HINT ( 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150' ));
GO

    所有这些的计划都可以在这里找到。Compat 级别 100 和 110 都给出了键查找计划，但是从 compat 级别 120 开始，我们开始获得具有 100 行估计的相同扫描计划。在兼容级别 150 之前都是如此。

    坚果

    坚果

    坚果

    坚果

    计划的基数估计>= '20130101', < '20130102'保持在 100，这是预期的。

    • 24

相关问题