T-SQL 从视图中选择变量要慢得多

问题描述

在 where 子句中指定值时，我有一个运行速度很快(<1s)的视图:

I have a view that runs fast (< 1s) when specifying a value in the where clause:

SELECT *
FROM vwPayments
WHERE AccountId = 8155

...但是当该值是变量时运行缓慢(~3s):

...but runs slow (~3s) when that value is a variable:

DECLARE @AccountId BIGINT = 8155

SELECT *
FROM vwPayments
WHERE AccountId = @AccountId

为什么第二个查询的执行计划不同?为什么它运行得这么慢?

Why is the execution plan different for the second query? Why is it running so much slower?

推荐答案

简而言之，查询优化器用来选择最佳计划的统计分析会在值为已知值时选择查找，并且它可以利用统计信息和扫描值未知.它在第二个选择中选择扫描，因为在知道 where 子句的值之前编译计划.

In short the statistical analysis the query optimizer uses to pick the best plan picks a seek when the value is a known value and it can leverage statistics and a scan when the value is not known. It picks a scan in the second choice because the plan is compiled before the value of the where clause is known.

虽然我很少建议在这种特定情况下使用查询分析器，但您可以使用 forceseek 提示或其他查询提示来覆盖引擎.但是请注意，在引擎的帮助下找到获得最佳计划的方法是更好的解决方案.

While I rarely recommend bossing the query analyzer around in this specific case you can use a forceseek hint or other query hints to override the engine. Be aware however, that finding a way to get an optimal plan with the engine's help is a MUCH better solution.

我快速搜索了谷歌并找到了一篇体面的文章更深入地介绍了影响查询计划的局部变量的概念.

I did a quick Google and found a decent article that goes into the concept of local variables affecting query plans more deeply.

这篇关于T-SQL 从视图中选择变量要慢得多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！