SQL Server查询:文本查询快，变量查询慢

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

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

SELECT * FROM view1 WHERE ID = 1

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

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

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

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.

我检查了2查询计划,第一个查询执行集群指数寻求主表1记录然后返回应用其他视图查询的结果集,而第二个查询执行返回大约3000条记录的索引扫描记录而不是一个我感兴趣的,然后过滤结果集。

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 2008，但是我所做的任何事情都需要在SQL 2005上运行。一开始我认为这是某种参数嗅探问题，但即使清除缓存，我也会得到相同的结果。

7 个解决方案

#1

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.

可能是因为在参数的情况下，优化器不知道值不是null，所以它需要创建一个计划，该计划即使返回正确的结果，也要返回正确的结果。如果您有SQL Server 2008 SP1，您可以尝试向查询添加选项(重新编译)。

#2

You could add an OPTIMIZE FOR hint to your query, e.g.

你可以为你的查询添加一个优化提示，例如。

DECLARE @id INT = 1
SELECT * FROM View1 WHERE ID = @id OPTION (OPTIMIZE FOR (@ID = 1))

#3

In my case in DB table column type was defined as VarChar and in parameterized query parameter type was defined as NVarChar, this introduced CONVERT_IMPLICIT in the actual execution plan to match data type before comparing and that was culprit for sow performance, 2 sec vs 11 sec. Just correcting parameter type made parameterized query as fast as non parameterized version.

就我而言在数据库表列类型被定义为VarChar和参数化的查询参数类型被定义为NVarChar,这在实际执行计划引入CONVERT_IMPLICIT匹配数据类型比较这是罪魁祸首播种性能之前,2秒vs 11秒。只是校正参数类型进行参数化的查询和非参数化的版本一样快。

Hope this may help someone with similar issue.

希望这能对有类似问题的人有所帮助。

#4

When SQL starts to optimize the query plan for the query with the variable it will match the available index against the column. In this case there was an index so SQL figured it would just scan the index looking for the value. When SQL made the plan for the query with the column and a literal value it could look at the statistics and the value to decide if it should scan the index or if a seek would be correct.

当SQL开始使用变量优化查询计划时，它将根据列匹配可用索引。在这种情况下，有一个索引，所以SQL认为它只需要扫描索引来查找值。当SQL为包含列和文字值的查询制定计划时，它可以查看统计数据和值，以决定是否应该扫描索引或查找是否正确。

Using the optimize hint and a value tells SQL that “this is the value which will be used most of the time so optimize for this value” and a plan is stored as if this literal value was used. Using the optimize hint and the sub-hint of UNKNOWN tells SQL you do not know what the value will be, so SQL looks at the statistics for the column and decides what, seek or scan, will be best and makes the plan accordingly.

使用优化提示和一个值告诉SQL，“这个值将在大多数情况下使用，所以要对这个值进行优化”，并存储一个计划，就好像使用了这个文字值一样。使用优化提示和未知的子提示告诉SQL您不知道值是什么，因此SQL会查看列的统计信息，并决定查找或扫描什么是最好的，并相应地制定计划。

#5

I ran into this problem myself with a view that ran < 10ms with a direct assignment (WHERE UtilAcctId=12345), but took over 100 times as long with a variable assignment (WHERE UtilAcctId = @UtilAcctId).
The execution-plan for the latter was no different than if I had run the view on the entire table.

我自己遇到了这个问题，我的视图运行了< 10ms的直接赋值(UtilAcctId=12345)，但是在变量赋值(UtilAcctId= @UtilAcctId)的情况下花费了100倍的时间。后者的执行计划与我在整个表上运行视图没有什么不同。

My solution didn't require tons of indexes, optimizer-hints, or a long-statistics-update.

我的解决方案不需要大量的索引、优化-提示或长期统计-更新。

Instead I converted the view into a User-Table-Function where the parameter was the value needed on the WHERE clause. In fact this WHERE clause was nested 3 queries deep and it still worked and it was back to the < 10ms speed.

相反，我将视图转换为用户表函数，其中参数是where子句所需的值。实际上，WHERE子句嵌套了3个查询，它仍然可以工作，并且回到了< 10ms的速度。

Eventually I changed the parameter to be a TYPE that is a table of UtilAcctIds (int). Then I can limit the WHERE clause to a list from the table. WHERE UtilAcctId = [parameter-List].UtilAcctId. This works even better. I think the user-table-functions are pre-compiled.

最后，我将参数更改为UtilAcctIds (int)表的类型。然后我可以将WHERE子句限制为表中的列表。UtilAcctId = .UtilAcctId参数列表。这是更好。我认为用户表函数是预编译的。

#6

Came across this same issue myself and it turned out to be a missing index involving a (left) join on the result of a subquery.

我自己遇到了这个问题，结果发现它是一个丢失的索引，包含子查询结果的(左)连接。

select *
from foo A
left outer join (
  select x, count(*)
  from bar
  group by x
) B on A.x = B.x

Added an index named bar_x for bar.x

为bar.x添加了一个名为bar_x的索引

#7

DECLARE @id INT = 1

声明@id INT = 1

SELECT * FROM View1 WHERE ID = @id

Do this

这样做

DECLARE @sql varchar(max)

声明@sql varchar(max)

SET @sql='SELECT * FROM View1 WHERE ID ='+CAST(@id as varchar)

设置@sql='SELECT * FROM View1，其中ID ='+CAST(@id为varchar)

EXEC (@sql)

EXEC(@sql)

Solves your problem

解决你的问题