主页 / dba / 问题 / 221882
Accepted
Badger
Asked: 2018-11-06 23:07:28 +0800 CST

你能强制 SQL Server 在计算列上使用索引来进行交换操作吗？

  • 772

你能强制 SQL Server 在计算列上使用索引来进行交换操作吗？

不幸的是，我们在 SQL Server 中有一个表如下

CREATE TABLE [dbo].[Data](
  [ID] [int] NOT NULL,
  [ValDate] [datetime] NOT NULL,
  [ValHour] [int] NOT NULL,
  [ValMin] [int] NOT NULL,
  [Value] [float] NULL,
  [Flag_ID] [int] NOT NULL,
  CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED 
  ([ID],[ValDate],[ValHour],[ValMin]) ON [PRIMARY]
) ON [PRIMARY]

即使 ValDate 是 DATETIME，它也只存储日期部分。

使用此数据库的一些旧应用程序进行以下查询

SELECT ID, ValDate, ValHour, ValMin, Value
FROM DATA
WHERE ID = @id 
  AND @start < DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate))
  AND DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate)) <= @end

如果有大量数据，这会减慢速度，因为 SQL 无法使用 WHERE 子句中指定的日期来查找，并且必须扫描 ID = @id 的每个表条目以查找所有行。

我们无法更改应用程序，所以我决定添加一个计算列并在其上放置一个 INDEX

ALTER TABLE dbo.Data ADD ComputedDateTime AS
  DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate))
GO
CREATE NONCLUSTERED INDEX [Data_ComputedDateTime_IDX] ON [dbo].[Data]
  ([ComputedDateTime], [ID])
INCLUDE ([ValDate], [ValHour], [ValMin], [Value])

这使得原始查询速度很快，但是如果我改变 DATEADD 的顺序，将分钟添加到日期，然后是不使用索引的小时。

所以我假设这是因为 SQL Server 没有意识到操作是可交换的，即 (date + hour) + minute = (date + minute) + hour

有没有办法在不创建两个计算列和两个索引的情况下加快两个计算顺序？

sql-server

1 个回答

  1. Best Answer

    据我所知，您唯一的选择是创建第二个计算列，联系软件供应商寻求修复，或者向 Microsoft 提交增强请求以获得对您的方案的更好支持。从表面上看，您所要求的功能可以看作是简单的：为什么 SQL Server 不能弄清楚这些表达式对程序员来说显然是等价的？但是，它至少需要满足以下所有条件：

    1. SQL Server 需要知道DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate))等于DATEADD(hour, ValHour, DATEADD(minute, ValMin, ValDate))。有很多 SQL Server 还不知道的日期等效项，这就是其中之一。

    2. 查询优化器需要在计算列匹配期间查找可交换匹配。

    3. 查询优化器需要能够在查询优化过程中找到您的匹配项，该过程旨在非常快速地为您提供“足够好”的计划。

    我很同情你的问题，但我的猜测是你所描述的场景还不够普遍，微软无法在这方面做出改进，尤其是当有一个简单的解决方法可以通过更改代码或通过向表中添加另一个计算列。

    • 4

相关问题