当我将值设置为变量时，为什么我的 SQL 语句的运行时间会延长 N 倍？

Question

我想说的第一件事是，这并不是我想要实现的目标。我已经简化了这个查询很多，以便更清楚地表达我的问题。

我在表 (CallDetail) 上有两个值的非聚集索引：TermDate (int) 和 SourceSystemID (int)。为了完整起见，我将在此处包含索引的精确定义：

CREATE NONCLUSTERED INDEX [CallDetail_TermDateSourceSystemID] ON [dbo].[CallDetail] 
(
    [TermDate] ASC,
    [SourceSystemID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

我遇到的问题是，当我对该表运行两个几乎相同的查询时，我没有得到相同的结果（不要与结果集混淆） ）。第一个查询运行时间不到一秒，并返回大约 10,000 行。第二个查询在执行时会继续运行，直到我在大约 30 分钟后取消它。

查询 1（约 1 秒）：

SELECT
    *
FROM
    CallDetail
WHERE
    CallDetail.TermDate >= 1101221 AND
    SourceSystemID = 1

查询 2（>30 分钟）：

DECLARE @TermDate AS INT
SET @TermDate = 1101221

SELECT
    *
FROM
    CallDetail
WHERE
    CallDetail.TermDate >= @TermDate AND
    SourceSystemID = 1

我想注意的是，查询执行计划告诉我将该表的所有列“包含”在索引中。我认为这是完全错误的。我还想指出，如果我只选择 TermDate 和 SourceSystemID 而不是 *，我会在大约 1 秒内得到结果。

当使用变量而不是将值硬编码到需要更长时间的地方时，是否有原因？我对此完全感到困惑，非常感谢任何帮助。

谢谢！

克里斯托弗·霍斯

Answer 1

好的，我用我的查询重现了这种情况：

declare @a as int
set @a = 12972100
select * from MyTable where (MyColumn > @a)

在阅读了 marc_s 的帖子后，我这样做了：

declare @a as int
set @a = 12972100
select * from MyTable where (MyColumn > @a) option (recompile)

一切又变得很快了！

Answer 2

您很可能正遭受参数嗅探和此处 。

您可以比较两个查询的查询计划吗？固定值的还是带参数的？他们给你的实际执行计划是一样的吗？

只需记住在运行之间进行适当的“清理”并发出DBCC FREEPROCCACHE，以便两者不会相互影响......

Answer 3

更新

鉴于这些评论，我被难住了。你说你简化了细节来解释问题，可能是其中一些细节导致了问题。您是否在上面显示的确切示例中看到了相同的问题？

我见过包含强制转换或转换的更复杂的查询也有类似的问题——这是你简化的事情之一吗？

或者也许是 Marc 建议的参数嗅探。

---

原始答案

这是因为它必须将列表中的每个值转换为 int 类型（TermDate 列必须是其他列。）

在第一个查询中，优化器很聪明，知道它可以转换常量到列的类型并执行此操作。

此外，当它必须为每一行进行转换时，它不能使用索引。

将此行更改

 DECLARE @TermDate AS INT

为与列相同的类型，您会很高兴。