Azure SQL データベースでのサブクエリを利用したストアドプロシージャについて

Question

Azure SQL Server12.0.2000.8

Visual Studio Community 2017

オンプレミスで稼働していたSQL ServerをAzure SQL Serverに移行した所、ストアドプロシージャの

実行に非常に時間が掛かるようになってしまいました。

使用しているテーブルは

【tbl_Jisseki】

jisseki_CD,Jisseki_Name,kokyaku_CD

【tbl_kokyaku_Tantou】

kokyaku_CD,Tantou_ID,Section_CD,Primary_Flg,Start_Date,Delete_Flg

【Mastertbl_Syain】※外部テーブル

Tantou_ID,Syain_Name

これらのテーブルを用いて、【tbl_kokyaku_Tantou】のSection_CD毎に検索した値を表示する

ストアドを以下のようにしました。

SELECT  DISTINCT 
TOP (@Kensu)
			tbl_Jisseki.Jisseki_Name,S_Hit.Tantou_ID AS Sales_Syain_ID,  
                        D_Hit.Tantou_ID AS Design_Syain_ID,  
                        A_Hit.Tantou_ID AS Intergration_Syain_ID, 
			S_Name.Syain_Name AS Sales_Syain_Name,　　※0
			--D_Name.Syain_Name AS Design_Syain_Name,　　※1 
                        --A_Name.Syain_Name AS Integration_Syain_Name　※2
FROM              tbl_Jisseki 
		--検索（確定以外も）の名称取得
			LEFT OUTER JOIN
                             (SELECT    ss.kokyaku_CD, ss.Tantou_ID
                                FROM    dbo.tbl_kokyaku_Tantou AS ss
                               WHERE    (ss.Section_No = 1 AND ss.Tantou_ID LIKE N'%' + @Stantou + '%')) AS S_Hit 
			LEFT OUTER JOIN
                              (SELECT   dd.kokyaku_CD, dd.Tantou_ID
                                 FROM   dbo.tbl_kokyaku_Tantou AS dd 
                                WHERE   (dd.Section_No = 2 AND dd.Tantou_ID LIKE N'%' + @Dtantou + '%')) AS D_Hit 
			LEFT OUTER JOIN
                               (SELECT   aa.kokyaku_CD, aa.Tantou_ID
                                  FROM   dbo.tbl_kokyaku_Tantou AS aa
                                 WHERE  (aa.Section_No = 3 AND aa.Tantou_ID LIKE N'%' + @Itantou + '%')) AS A_Hit 
		--表示用の名称取得
			LEFT OUTER JOIN　※3
				(SELECT  ssh.kokyaku_CD, ssh.Tantou_ID
				   FROM  dbo.tbl_kokyaku_Tantou AS ssh 
				  WHERE  (ssh.Section_No = 1) AND (ssh.Primary_Flg = 1) AND (ssh.Delete_Flg = 0)) AS Sales_Syain ON 
					 dbo.tbl_jisseki.kokyaku_CD = Sales_Syain.kokyaku_CD  
			LEFT OUTER JOIN
				(SELECT   ddh.kokyaku_CD, ddh.Tantou_ID
				   FROM   dbo.tbl_kokyaku_Tantou AS ddh 
				  WHERE  (ddh.Section_No = 2) AND (ddh.Primary_Flg = 1) AND (ddh.Delete_Flg = 0)) AS Design_Syain ON 
					 dbo.tbl_jisseki.kokyaku_CD = Design_Syain.kokyaku_CD  
			LEFT OUTER JOIN
				(SELECT  aah.kokyaku_CD, aah.Tantou_ID
				   FROM  dbo.tbl_kokyaku_Tantou AS aah
				  WHERE  (aah.Section_No = 3) AND (aah.Primary_Flg = 1) AND (aah.Delete_Flg = 0)) AS Arc_Syain ON 
					dbo.tbl_jisseki.kokyaku_CD = Arc_Syain.kokyaku_CD  
			LEFT OUTER JOIN　※4
				Mastertbl_Syain AS S_Name ON Sales_Syain.Tantou_ID = S_Name.SYAIN_ID 
			LEFT OUTER JOIN
                                Mastertbl_Syain AS D_Name ON Design_Syain.Tantou_ID = D_Name.SYAIN_ID 
			LEFT OUTER JOIN
                                Mastertbl_Syain AS A_Name ON Arc_Syain.Tantou_ID = A_Name.SYAIN_ID 
WHERE				
			(@Stantou IS NULL OR S_Hit.Tantou_ID LIKE '%' +  @Stantou + '%') AND
			(@Dtantou IS NULL OR D_Hit.Tantou_ID LIKE '%' +  @Dtantou + '%') AND
			(@Itantou IS NULL OR A_Hit.Tantou_ID LIKE '%' +  @Itantou + '%')  AND
ORDER BY tbl_jisseki.kokyaku_CD DESC

この状態で実行すると10000件ほどが2秒程で返ってきますが、※1のコメントを外すと実行結果が返るのに3分以上かかります。

※3と※4を結合して1つの式にしてみたら、※1がコメントアウトしていても非常に遅くなってしまいました。

※0は遅延せず結果が返るのに※1を入れると遅延する理由が分かりません。

原因やもっと良いコードの書き方があればご教授頂きたい次第です。

Answer 1

chromeMAOさん、こんにちは。フォーラムオペレーターのHarukaです。
MSDNフォーラムにご投稿くださいましてありがとうございます。

Azure SQL Databaseでこのクエリを実行している場合は、以下のようにAzureポータルでlong runningクエリのパフォーマンスを確認し、
そのlong runningクエリをクリックして推奨事項を確認できます。

どうぞよろしくお願いします。

---

MSDN/ TechNet Community Support Haruka

～参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、
ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、[e-mail]までお気軽にお問い合わせください。～

Answer 2

フォーラムオペレーター Haruka様

遅くなりましたが、ご回答ありがとうございます。

提示頂いた操作を実行してみたのですが、抜本的解決には至りませんでした。

試行錯誤の結果、外部テーブルを利用したサブクエリに問題があると考え

外部テーブルを一度テーブル変数に落とし込みそのテーブル変数をサブクエリ内で参照させる事によって

速度を上げることが出来ました。

返信が遅くなり申し訳ありません、ありがとうございました。