misc.log

日常茶飯事とお仕事と

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

昨日書いたこれ……

backyard.hatenablog.com

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

SQL Server Tuning Scripts: Performance Optimization Secrets

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

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