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

    質問

  • 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を入れると遅延する理由が分かりません。

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


    2019年5月13日 6:02

回答

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

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

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

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

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

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

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



    • 回答としてマーク chromeMAO 2019年5月24日 2:14
    • 編集済み chromeMAO 2019年5月24日 2:14
    2019年5月24日 2:13

すべての返信

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

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

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


    MSDN/ TechNet Community Support Haruka

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

    2019年5月15日 8:01
    モデレータ
  • フォーラムオペレーター Haruka様

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

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

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

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

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

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



    • 回答としてマーク chromeMAO 2019年5月24日 2:14
    • 編集済み chromeMAO 2019年5月24日 2:14
    2019年5月24日 2:13