ASP.NET/ADO.NETからの実行だと遅いストアドプロシージャ問題、とりあえず解決にめど

昨日書いたこれ……

www.backyrd.net

SQL Server Management Studioから実行すると1～2秒で結果を返すストアドプロシージャが、なぜかASP.NETのWebアプリから実行すると4分～5分もかかるという問題。とりあえず速度差が無くなったので報告。

対応案

Stackoverflowやらほかのブログなどから、以下の対応が有効そうというところまで昨日調べました。

• ARITHABORTオプションをONにする（日本語名：算術アボート）
• ストアドプロシージャを毎回リコンパイルする設定を行う
• ストアドプロシージャの引数をすべてローカル変数に移して使う

ARITHABORTをONにする方策

残念ながら効果が見られませんでした。目的のストアドプロシージャを実行する前に、「SET ARITHABORT ON」というステートメントをExecuteNonQueryで流したのですが効果なし。

毎回リコンパイルする方策

これが効きました。SQLとして流す方法もあるようなのですが、今使っている環境のソース構成がそういう余分なステートメントを受け付けないつくりだったので、ストアドプロシージャの定義自体に下記のように「WITH RECOMPILE」を追記。

ALTER PROCEDURE [dbo].[TestProcedure] 
(
	@USER_ID NVARCHAR(50)
) WITH RECOMPILE AS
BEGIN

……

これが効果あり。4分かかっていたデータ取得が2秒弱で終わるようになりました。

なぜ？（要勉強）

なぜこれが効いたかについて、正直なところ厳密な説明をするための知識が足りていません。要勉強です。推測ですが…

• Webアプリの画面上での選択によって、同じストアドプロシージャが異なる引数で呼び出されるというつくりだった。
• 実行計画などに引数の組み合わせも含めて格納されているため、ある引数のパターンでは有効な計画が、別の引数の組み合わせでは速度低下につながる、というような事象が起きていた。

なのかな、と。実際、下記のサイトでそういう記述がありました。

https://msdn.microsoft.com/en-us/library/ms190439.aspxmsdn.microsoft.com

「Before You Begin / Recommendations」の3つ目、

If parameter values on the procedure are frequently atypical, 
forcing a recompile of the procedure and a new plan based on different parameter values 
can improve performance.

まさにこれですね。

SQL Server Tuning Scripts: Performance Optimization Secrets

インサイドMS SQL SERVER 2005クエリチューニング&最適化編 (マイクロソフト公式解説書)