SQL Server 查询:字面量快，变量慢 [英] SQL Server Query: Fast with Literal but Slow with Variable

问题描述

我有一个使用 CTE 从表中返回 2 个整数的视图.如果我像这样查询视图，它会在不到一秒的时间内运行

I have a view that returns 2 ints from a table using a CTE. If I query the view like this it runs in less than a second

SELECT * FROM view1 WHERE ID = 1

但是，如果我像这样查询视图，则需要 4 秒.

However if I query the view like this it takes 4 seconds.

DECLARE @id INT = 1
SELECT * FROM View1 WHERE ID = @id

我检查了 2 个查询计划，第一个查询正在主表上执行聚集索引查找，返回 1 条记录，然后将视图查询的其余部分应用于该结果集，其中第二个查询正在执行索引scan 返回大约 3000 条记录，而不仅仅是我感兴趣的记录，然后过滤结果集.

I've checked the 2 query plans and the first query is performing a Clustered index seek on the main table returning 1 record then applying the rest of the view query to that result set, where as the second query is performing an index scan which is returning about 3000 records records rather than just the one I'm interested in and then later filtering the result set.

在尝试让第二个查询使用索引查找而不是索引扫描时，我是否遗漏了任何明显的东西.我使用的是 SQL 2008，但我所做的任何事情都需要在 SQL 2005 上运行.起初我认为这是某种参数嗅探问题，但即使我清除了缓存，我也得到了相同的结果.

Is there anything obvious that I'm missing to try to get the second query to use the Index Seek rather than an index scan. I'm using SQL 2008 but anything I do needs to also run on SQL 2005. At first I thought it was some sort of parameter sniffing problem but I get the same results even if I clear the cache.

推荐答案

可能是因为在参数情况下，优化器无法知道该值不为空，因此需要创建一个即使在这是.如果您有 SQL Server 2008 SP1，您可以尝试将 OPTION(RECOMPILE) 添加到查询中.

Probably it is because in the parameter case, the optimizer cannot know that the value is not null, so it needs to create a plan that returns correct results even when it is. If you have SQL Server 2008 SP1 you can try adding OPTION(RECOMPILE) to the query.

这篇关于SQL Server 查询:字面量快，变量慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！