sp_executesql 中查詢的執行時間高

我有一個從 .net 應用程序生成的查詢，參數是 linq 參數。我調整查詢的過程如下。

• 我執行應用程序並使用分析器擷取查詢。
• 查詢的形式為

exec sp_executesql N'SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[GroupId] AS [GroupId], 
[Extent1].[Agency] AS [Agency], 
[Extent1].[Group] AS [Group], 
[Extent1].[Claim] AS [Claim], 
[Extent1].[StartDate] AS [StartDate], 
[Extent1].[ExpireDate] AS [ExpireDate], 
[Extent1].[IsActive] AS [IsActive]
FROM [dbo].[Permissions] AS [Extent1]
WHERE ([Extent1].[GroupId] = @p__linq__0) AND (([Extent1].[ExpireDate] IS NULL)
OR ([Extent1].[ExpireDate] > @p__linq__1)) AND ([Extent1].[IsActive] <> 1)',
N'@p__linq__0 int,@p__linq__1 datetime2(7)',
@p__linq__0=15,@p__linq__1='2017-05-10 00:00:00'

這只是一個範例查詢。實際的查詢要大得多，包括許多涉及多個視圖和表等的連接。

我的問題是：

• 當按原樣執行查詢時，執行時間約為 15 秒
• 當我單獨從查詢中取出查詢sp_executesql並將過濾器值應用於查詢時，它會在不到 1 秒的時間內執行。

我知道，由於這些值被硬編碼到查詢中，它會更快，但為什麼執行時間會有這麼大的差異？兩者的執行計劃也不同：

帶有 sp_executesql 的 SQL 執行計劃

使用硬編碼 SQL 的執行計劃

我是唯一在此伺服器上工作的使用者，我在兩種情況下都執行相同的參數。我已經申請OPTION(RECOMPILE)到最後，sp_executesql它在 1 秒內執行，而 15 秒。但執行計劃仍然與硬編碼計劃不相似。

無論如何OPTION(RECOMPILE)，使查詢在一秒鐘內快速執行，因為它每次都強制查詢編譯並創建新計劃。但我不應該破解程式碼並更改查詢。我會向我的申請團隊建議OPTION(RECOMPILE)作為最後的手段。

查詢優化器能夠對動態查詢使用參數嗅探，如實際計劃中所示：

<ParameterList>
 <ColumnReference Column="@p__linq__5" ParameterDataType="varchar(8000)" ParameterCompiledValue="'MI3300287'" ParameterRuntimeValue="'MI3300287'" />
 <ColumnReference Column="@p__linq__4" ParameterDataType="datetime2(7)" ParameterCompiledValue="'2009-06-11 23:59:00.0000000'" ParameterRuntimeValue="'2009-06-11 23:59:00.0000000'" />
 <ColumnReference Column="@p__linq__3" ParameterDataType="datetime2(7)" ParameterCompiledValue="'2008-06-11 00:00:00.0000000'" ParameterRuntimeValue="'2008-06-11 00:00:00.0000000'" />
 <ColumnReference Column="@p__linq__2" ParameterDataType="varchar(8000)" ParameterCompiledValue="'MI3300287'" ParameterRuntimeValue="'MI3300287'" />
 <ColumnReference Column="@p__linq__1" ParameterDataType="int" ParameterCompiledValue="(90495)" ParameterRuntimeValue="(90495)" />
 <ColumnReference Column="@p__linq__0" ParameterDataType="int" ParameterCompiledValue="(90495)" ParameterRuntimeValue="(90495)" />
</ParameterList>

這些嗅探到的值會影響選擇的查詢計劃。但是，對於輸入變數的所有可能值，查詢計劃仍然需要是安全的。讓我們看一下計劃之間的一個區別，即IncidentDetailsPage_Header桌面上的訪問權限。在較慢的動態計劃中，您將獲得掃描和雜湊匹配，總共需要大約 2.4 秒：

在具有硬編碼值的更快計劃中，您會得到一個索引查找，而不是大約需要 0 毫秒：

下面是對應於該計劃部分的 T-SQL：

AND (([Extent1].[agencyori] = @p__linq__5)
     OR (([Extent1].[agencyori] IS NULL)
         AND (@p__linq__5 IS NULL)))

對這些值進行硬編碼會導致僅針對這些參數值的單次使用查詢計劃。使用您的參數值，該 T-SQL 可以簡化為：

([Extent1].[AgencyOri] = 'MI3300287')

這有資格進行索引搜尋。其他程式碼不是。盡可能避免詢問廚房水槽。

引用自：https://dba.stackexchange.com/questions/209323