sp_executesqlのパラメータが遅い

dapper sql-server

質問

私はORMとしてdapper-dot-netを使用しており、以下のスロー実行型(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を紹介します。

SQLサーバーの内部構造を理解するのに非常に役立ちます(これで噛み付く可能性があります)。

あなたはそれを理解していれば、あなたは文が非常に頻繁に実行された場合は、オプション再コンパイルとのトレードオフは、パフォーマンスの低下ことができることを知っているだろう。

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

更新2

私がこの主題について持っていた最も良い読みはGRANT FRITCHEYによる「パラメータスニッフィング:あなたの親友...ではない場合を除いて」と呼ばれる第32章にありました

お勧めです。

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


人気のある回答

私は最近、同じ問題に直面した。私が最初にしたことは、where whereステートメントの列にNonClustered Covering Indexを追加することでした。

これはSQLの実行時間を改善しました。しかし、dapperがクエリを実行していたときにはまだ遅かったですが、実際はタイムアウトでした。

それから、dapperによって生成されたクエリが、dbテーブルのカラムがvarchar(80)のnvarchar(4000)としてパラメータに渡されていることに気付きました。 これはシークの代わりにインデックススキャンを実行しました。それがあなたに合っていない場合は索引を付けます)。これを実現すると、次のようなステートメントがどこに更新されました:

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

上記のwhereステートメントを実行すると、インデックスシークと100%のパフォーマンス向上が実現しました。

ちょうど追加する:オプション(再コンパイル)が私のために働かなかった。

そしてこのすべての歌とダンスの後に、デフォルトであなたのためにこれを行うようにdapperに指示する方法があります:

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

これは、デフォルトでは、nvarchar(4000)ではなく、任意の文字列パラメータをvarchar(4000)にマッピングします。 Unicode文字列の比較が必要な場合は、明示的にパラメータの変換を行うことができます。



ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow