☰

sp_executesql はパラメータを使用すると遅くなります 質問する

tag-icon • tag-icon sql-server dapper
sp_executesql はパラメータを使用すると遅くなります 質問する

私は dapper-dot-net を ORM として使用していますが、次のような実行速度が遅い (1700 ミリ秒) 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

パラメータを削除してこのコードを変更すると、クエリの実行速度が驚くほど速くなります (20 ミリ秒)。これらのパラメータがないと、実際にこれほど大きな違いが生じるのでしょうか。また、その理由は何でしょうか。

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

ベストアンサー1

最後にOPTION (RECOMPILE)を追加します

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

「パラメータスニッフィング」が発生していると思われます

その場合は、オプションのままにするか、代替案を検討することができます。

アップデート1

次の記事では「パラメータスニッフィング」について紹介しますhttp://pratchev.blogspot.be/2007/08/parameter-sniffing.html

SQL Server の内部 (難しい場合があります) をより深く理解するために、詳細を知ることをお勧めします。

これを理解すれば、オプションの再コンパイルとのトレードオフが分かるでしょう。できるステートメントを実行するとパフォーマンスが低下する可能性があるとても頻繁。

私は個人的にオプションの再コンパイルを追加します後根本的な原因はパラメータ スニッフィングであることがわかっているので、パフォーマンス上の問題がない限りはそのままにしておきます。不適切なパラメータ スニッフィングを避けるためにステートメントを書き直すと、意図が失われ、保守性が低下します。ただし、書き直しが正当化される場合もあります (書き直す場合は適切なコメントを使用してください)。

アップデート2

このテーマで私が読んだ中で最も良かったのは、GRANT FRITCHEY 著の「パラメータ スニッフィング: 親友... そうでなければ」という第 32 章でした。

お勧めです。

SQL Server MVP ディープダイブ、第 2 巻

おすすめ記事