sp_executesql參數很慢

dapper sql-server

我使用dapper-dot-net作為ORM,它產生以下,執行速度慢(1700ms)的SQL代碼。

exec sp_executesql N'SELECT TOP 5 SensorValue FROM "Values" WHERE DeviceId IN (@id1,@id2) AND SensorId = @sensor AND SensorValue != -32768 AND SensorValue != -32767',N'@id1 bigint,@id2 bigint,@sensor int',@id1=139,@id2=726,@sensor=178

當我通過刪除參數來修改此代碼時,查詢執行速度極快(20ms)。缺乏這些參數是否真的會產生這麼大的差異?為什麼?

exec sp_executesql N'SELECT TOP 5 SensorValue FROM "Values" WHERE DeviceId IN (139,726) AND SensorId = 178 AND SensorValue != -32768 AND SensorValue != -32767'

一般承認的答案

添加OPTION(RECOMPILE)到最後

... AND SensorValue != -32767 OPTION (RECOMPILE) 

我懷疑你正在經歷“參數嗅探”

如果是這種情況,我們可以將其留給OPTION或考慮替代方案

更新1

以下文章將向您介紹“參數嗅探” http://pratchev.blogspot.be/2007/08/parameter-sniffing.html

我建議你了解ins和out,因為它會讓你更好地理解sql server內部(可以咬人)。

如果你了解它,你就會知道,如果經常執行的語句重新編譯選項的權衡可能成為一個性能下降。

我個人添加選項重新編譯後,我知道根本原因是參數嗅探並保留它,除非存在性能問題。重寫語句以避免錯誤的參數嗅探會導致意圖丟失,從而降低可維護性。但是有些情況下重寫是合理的(當你這樣做時使用好的評論)。

更新2

關於這個主題的最佳讀物是在第32章中稱為“參數嗅探:你最好的朋友......除非它不是由”GRANT FRITCHEY

這是推薦的。

SQL Server MVP Deep Dives,第2卷


熱門答案

我最近遇到了同樣的問題。我做的第一件事是在where語句中的列上添加NonClustered Covering Index。

這改善了SQL的執行時間,但是當dapper執行查詢時它仍然很慢,實際上它已經超時了。

然後我意識到dapper生成的查詢是作為nvarchar(4000)傳遞的參數, 其中我的db表列是varchar(80),這導致它執行索引掃描而不是搜索 (我建議你閱讀索引,如果這對你沒有意義。)在意識到這一點後,我更新了我的小巧玲瓏,其中的聲明是這樣的:

WHERE Reference = convert(varchar(80),@ Reference)

使用上面的where語句執行會導致索引搜索,並且性能提高100%。

只是添加:選項(重新編譯)對我不起作用。

在所有這些歌曲和舞蹈之後,有一種方法可以告訴dapper默認為你做這件事:

Dapper.SqlMapper.AddTypeMap(typeof(string),System.Data.DbType.AnsiString);

默認情況下,這會將任何字符串參數映射到varchar(4000)而不是nvarchar(4000)。如果確實需要Unicode字符串比較,則可以顯式對參數進行轉換。



許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因