問題描述
在 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 從視圖中選擇變量要慢得多的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！