none
クエリを高速化するアドバイスをお願いします! RRS feed

  • 質問

  • Access2019(フロントエンド)、SQL Server2016(バックエンド)で生産管理のシステムを運用しています。

    パススルークエリにて下記のようなクエリを組んでいるのですが、帳票フォームに500レコードを表示するのに5-7秒ほどかかります。

    表示をより高速化する方法はないでしょうか。

    担当者テーブル

    受注ID       名前

    1               伊藤

    2               後藤

    3               加藤

    工程テーブル

    工程ID      受注ID     工程順序       工程       予定日      完了日

    1             1             1                 焼く        11/2        11/2

    2             1             2                 煮る        11/3

    3             1            3                 蒸す        11/4

    4             2             1                 煮る        11/3 

    5             3             1                 焼く        11/6        11/6

    6             3             2                 蒸す        11/6

    作りたいクエリ

    受注ID   名前     残工程1   残工程1予定日   残工程2   残工程2予定日    残工程3   残工程3予定日

    1          伊藤      煮る        11/3                蒸す        11/4                NULL        NULL

    2          後藤      煮る        11/3                NULL       NULL               NULL        NULL

    3          加藤      蒸す        11/6                NULL       NULL               NULL        NULL

    帳票フォーム上で担当者テーブルと工程テーブルをつなげて表示したいと思っています。

    ただし完了した工程(完了日時がNULLではないレコード)は表示せずに、未完了の残っている工程のみを

    表示したいと思っています。

    そこでサブクエリを組み合わせて下記のようなパススルークエリを組んでいます。

    SELECT TOP(500) TJ.受注ID, TJ.名前, 

          (SELECT TJK.工程 FROM 工程テーブル AS TJK WHERE TJK.受注ID=TJ.受注ID AND

               TJK.工程順序=(SELECT MIN(工程順序) FROM 工程テーブル AS tTJK 

                   WHERE TJK.受注ID=tTJK.受注ID AND tTJK.完了日時 IS NULL))

          AS 残工程1, 

          (SELECT TJK.工程 FROM 工程テーブル AS TJK WHERE TJK.受注ID=TJ.受注ID AND
               TJK.工程順序=(SELECT MIN(工程順序) FROM 工程テーブル AS tTJK

                   WHERE TJK.受注ID=tTJK.受注ID AND tTJK.完了日時 IS NULL)+1)
          AS 残工程2, 
          (SELECT TJK.工程 FROM 工程テーブル AS TJK WHERE TJK.受注ID=TJ.受注ID AND
               TJK.工程順序=(SELECT MIN(工程順序) FROM 工程テーブル AS tTJK

                   WHERE TJK.受注ID=tTJK.受注ID AND tTJK.完了日時 IS NULL)+2)
          AS 残工程3, 

          (SELECT TJK.予定日 FROM 工程テーブル AS TJK WHERE TJK.受注ID=TJ.受注ID AND

               TJK.工程順序=(SELECT MIN(工程順序) FROM 工程テーブル AS tTJK

                   WHERE TJK.受注ID=tTJK.受注ID AND tTJK.完了日時 IS NULL))

          AS 残工程1予定日, 

          (SELECT TJK.予定日 FROM 工程テーブル AS TJK WHERE TJK.受注ID=TJ.受注ID AND

               TJK.工程順序=(SELECT MIN(工程順序) FROM 工程テーブル AS tTJK

                   WHERE TJK.受注ID=tTJK.受注ID AND tTJK.完了日時 IS NULL)+1)

          AS 残工程2予定日

          (SELECT TJK.予定日 FROM 工程テーブル AS TJK WHERE TJK.受注ID=TJ.受注ID AND

               TJK.工程順序=(SELECT MIN(工程順序) FROM 工程テーブル AS tTJK

                   WHERE TJK.受注ID=tTJK.受注ID AND tTJK.完了日時 IS NULL)+2)

          AS 残工程3予定日

    FROM 担当者テーブル AS TJ 

    遅さの原因はおそらくサブクエリだと思います。

    サブクエリを使わずに、PIVOT句を使って似たようなSQLを組んだところ表示に1秒もかかりませんでした。

    ただ、(1)完了日時がNULLでない場合はスキップする、(2)予定日を同時に取得する方法が分からず

    PIVOTでは目的のクエリを組めませんでした。

    クエリ以外にもADOやDAOを使った方が早いなどあればそちらの方法でもプログラムを修正いたします。

    ご教授の程宜しくお願いいたします。











    • 編集済み HIRO_KU 2020年11月9日 12:46
    2020年11月9日 12:36

回答

  • 以前同じような質問してますが、それらは試したのでしょうか

    Select 
    	Top (500)
    	T.受注ID,
    	T.名前,
    	T.工程 ,
    	T.残工程1 ,
    	T.残工程1予定日,
    	T.残工程2 ,
    	T.残工程2予定日,
    	T.残工程3 ,
    	T.残工程3予定日
    from 
    (
    	select 
    		T1.受注ID,
    		T0.名前,
    		T1.工程 ,
    		T1.工程 as 残工程1 ,
    		T1.予定日 as 残工程1予定日,
    		T2.工程 as 残工程2 ,
    		T2.予定日 as 残工程2予定日,
    		T3.工程 as 残工程3 ,
    		T3.予定日 as 残工程3予定日
    		
    		, row_number() over (partition by T0.受注ID order by T1.工程順序) as 残り順
    	from 担当者テーブル as T0
    		inner join 工程テーブル as T1 on T0.受注ID = T1.受注ID
    		left  join 工程テーブル as T2 on T0.受注ID = T2.受注ID and T1.工程順序 = T2.工程順序-1
    		left  join 工程テーブル as T3 on T0.受注ID = T3.受注ID and T1.工程順序 = T3.工程順序-2
    	where  T1.完了日 is null	
    ) as T
    where T.残り順=1
    order by T.受注ID

    個別に明示されていない限りgekkaがフォーラムに投稿したコードにはフォーラム使用条件に基づき「MICROSOFT LIMITED PUBLIC LICENSE」が適用されます。(かなり自由に使ってOK!)


    • 編集済み gekkaMVP 2020年11月10日 5:12 工程順序で並んでなかった
    • 回答としてマーク HIRO_KU 2020年11月10日 10:56
    2020年11月10日 3:34
  • gekkaさんの回答でも問題ないのかもしれませんが…厳密に「完了した工程(完了日時がNULLではないレコード)は表示せず」ではなさそうです。具体的には、工程を飛ばして先に完了してしまったものがあってもスキップされずに残工程として列挙されるように思います。

    ちなみに

    SELECT 受注ID, ROW_NUMBER() OVER(PARTITION BY 受注ID ORDER BY 工程順序) AS 残工程順序, 工程, 予定日
    FROM 工程テーブル
    WHERE 完了日 IS NULL;

    このクエリで未完了のみを抽出し、残工程順序カラムを算出できます。これをベースに組み立てていけば簡単です。

    WITH 残工程テーブル
    AS(
      SELECT 受注ID, ROW_NUMBER() OVER(PARTITION BY 受注ID ORDER BY 工程順序) AS 残工程順序, 工程, 予定日
      FROM 工程テーブル
      WHERE 完了日 IS NULL
    )
    SELECT TJ.受注ID, TJ.名前, TJK1.工程 AS 残工程1, TJK1.予定日 AS 残工程1予定日, TJK2.工程 AS 残工程2, TJK2.予定日 AS 残工程2予定日, TJK3.工程 AS 残工程3, TJK3.予定日 AS 残工程3予定日
    FROM 担当者テーブル AS TJ
    LEFT OUTER JOIN 残工程テーブル AS TJK1 ON TJ.受注ID=TJK1.受注ID AND TJK1.残工程順序=1
    LEFT OUTER JOIN 残工程テーブル AS TJK2 ON TJ.受注ID=TJK2.受注ID AND TJK2.残工程順序=2
    LEFT OUTER JOIN 残工程テーブル AS TJK3 ON TJ.受注ID=TJK3.受注ID AND TJK3.残工程順序=3
    ORDER BY TJ.受注ID;

    NOBTAさんが言及されていますがPIVOTは1回では集計しきれないので、2回に分けることになりますが、とりあえず書いてみました。

    WITH 残工程テーブル
    AS (
      SELECT 受注ID, [1] AS 残工程1, [2] AS 残工程2, [3] AS 残工程3
      FROM (
        SELECT 受注ID, ROW_NUMBER() OVER(PARTITION BY 受注ID ORDER BY 工程順序) AS 残工程順序, 工程
        FROM 工程テーブル
        WHERE 完了日 IS NULL
      ) AS X1 PIVOT (MIN(工程) FOR 残工程順序 IN ([1], [2], [3])) AS X2
    ), 残予定日テーブル
    AS (
      SELECT 受注ID, [1] AS 残工程1予定日, [2] AS 残工程2予定日, [3] AS 残工程3予定日
      FROM (
        SELECT 受注ID, ROW_NUMBER() OVER(PARTITION BY 受注ID ORDER BY 工程順序) AS 残工程順序, 予定日
        FROM 工程テーブル
        WHERE 完了日 IS NULL
      ) AS Y1 PIVOT (MIN(予定日) FOR 残工程順序 IN ([1], [2], [3])) AS Y2
    )
    SELECT 担当者テーブル.受注ID, 名前, 残工程1, 残工程1予定日, 残工程2, 残工程2予定日, 残工程3, 残工程3予定日
    FROM 担当者テーブル
    LEFT OUTER JOIN 残工程テーブル ON 担当者テーブル.受注ID=残工程テーブル.受注ID
    LEFT OUTER JOIN 残予定日テーブル ON 担当者テーブル.受注ID=残予定日テーブル.受注ID
    ORDER BY 担当者テーブル.受注ID;

    # X1, X2, Y1, Y2は使わないのに名前を付けないと構文エラーで怒られるんですよね…理不尽。

    • 編集済み 佐祐理 2020年11月10日 15:01
    • 回答としてマーク HIRO_KU 2020年11月11日 2:05
    2020年11月10日 14:10

すべての返信

  • SQL Server では Pivot クエリで複数行を選択することが出来ないため、一つのステートメントで該当のアウトプットを得るためには、既に検討されているクエリのような実装が必要になるかと思います。

    クエリの高速化という意味では、5-7秒 時間を要しているクエリで何処で時間を要しているか、クエリの実行プランを確認し、インデックスの追加などでパフォーマンスを向上することが可能となるかなどを確認すると良いかと思います。

    また、処理速度が速くなるかは不明ですが、該当のアウトプットを出力可能なストアドプロシージャ (T-SQLによるプログラミング)、ADO.NETのDataTableなどを使用したプログラミングを実装することでパフォーマンスを向上できるかを確認してみるのも良いかと思います。

    もしくは、即時性を求められないクエリなのであれば、該当アウトプットのデータが保持された新規テーブルを作成し、帳票フォームからは新規テーブルを参照し、新規テーブルへのデータを定期的に更新するという仕組みを考えても良いかもしれません。

    2020年11月10日 3:31
  • 以前同じような質問してますが、それらは試したのでしょうか

    Select 
    	Top (500)
    	T.受注ID,
    	T.名前,
    	T.工程 ,
    	T.残工程1 ,
    	T.残工程1予定日,
    	T.残工程2 ,
    	T.残工程2予定日,
    	T.残工程3 ,
    	T.残工程3予定日
    from 
    (
    	select 
    		T1.受注ID,
    		T0.名前,
    		T1.工程 ,
    		T1.工程 as 残工程1 ,
    		T1.予定日 as 残工程1予定日,
    		T2.工程 as 残工程2 ,
    		T2.予定日 as 残工程2予定日,
    		T3.工程 as 残工程3 ,
    		T3.予定日 as 残工程3予定日
    		
    		, row_number() over (partition by T0.受注ID order by T1.工程順序) as 残り順
    	from 担当者テーブル as T0
    		inner join 工程テーブル as T1 on T0.受注ID = T1.受注ID
    		left  join 工程テーブル as T2 on T0.受注ID = T2.受注ID and T1.工程順序 = T2.工程順序-1
    		left  join 工程テーブル as T3 on T0.受注ID = T3.受注ID and T1.工程順序 = T3.工程順序-2
    	where  T1.完了日 is null	
    ) as T
    where T.残り順=1
    order by T.受注ID

    個別に明示されていない限りgekkaがフォーラムに投稿したコードにはフォーラム使用条件に基づき「MICROSOFT LIMITED PUBLIC LICENSE」が適用されます。(かなり自由に使ってOK!)


    • 編集済み gekkaMVP 2020年11月10日 5:12 工程順序で並んでなかった
    • 回答としてマーク HIRO_KU 2020年11月10日 10:56
    2020年11月10日 3:34
  • NOBTA様 ありがとうございます。クエリ実行プランでの確認はできていなかったので、してみたいと思います。また他のアプローチについてもご教授いただきありがとうございます。インデックスの見直しや読み込むテーブルの取り扱いについても検討してみたいと思います。 何か別の方法で思いつくことがありましたら、ご教授の程、宜しくお願いいたします。
    2020年11月10日 6:16
  • gekka様

    クエリ例のご提案をいただきありがとうございます。

    ご指摘の通り前回、登録されている工程を決まった順序で抽出し、一覧表表示するクエリを

    ご教授をいただきました。

    サブクエリやPIVOTを使って無事に運用することができました。

    ただ運用するに従い、利用者からいろいろな要望が出てきました。

    それが今回質問させていただいた、

    ①完了している工程を表示させないでほしい

    ②工程名以外に予定日を表示させてほしい

    という点です。

    決まった順序ではなく、製作の進行具合によって動的に工程順序を抽出する方法をサブクエリで

    やってみましたが、スピードが遅く、なんとか高速化する改善方法がないかと思い質問させていただきました。

    出先のため、戻り次第ご教授いただきましたクエリを試してみたいと思います。

    今後ともよろしくお願いいたします。

    2020年11月10日 6:26
  • gekka様

    早速ご教授いただいたSQLを元に本当に欲しい情報を詰め込んで

    試してみたところ下記のような結果になりました。

    元々のSQLでのクエリ結果 : 13秒

    ご提案いただいたSQLでのクエリ結果 : 1秒未満

    よく教科書などには書いてありますが、実行スピードは サブクエリ<<<テーブルの結合 だと

    改めて感じました。

    ただ、なかなか自分でこれだけの複雑なテーブル結合を使いこなすことはまだまだ難しいです。

    なんとか自分のものにできるように頑張っていきたいと思います。

    ご教授いただき、本当にありがとうございました。

    2020年11月10日 10:55
  • gekkaさんの回答でも問題ないのかもしれませんが…厳密に「完了した工程(完了日時がNULLではないレコード)は表示せず」ではなさそうです。具体的には、工程を飛ばして先に完了してしまったものがあってもスキップされずに残工程として列挙されるように思います。

    ちなみに

    SELECT 受注ID, ROW_NUMBER() OVER(PARTITION BY 受注ID ORDER BY 工程順序) AS 残工程順序, 工程, 予定日
    FROM 工程テーブル
    WHERE 完了日 IS NULL;

    このクエリで未完了のみを抽出し、残工程順序カラムを算出できます。これをベースに組み立てていけば簡単です。

    WITH 残工程テーブル
    AS(
      SELECT 受注ID, ROW_NUMBER() OVER(PARTITION BY 受注ID ORDER BY 工程順序) AS 残工程順序, 工程, 予定日
      FROM 工程テーブル
      WHERE 完了日 IS NULL
    )
    SELECT TJ.受注ID, TJ.名前, TJK1.工程 AS 残工程1, TJK1.予定日 AS 残工程1予定日, TJK2.工程 AS 残工程2, TJK2.予定日 AS 残工程2予定日, TJK3.工程 AS 残工程3, TJK3.予定日 AS 残工程3予定日
    FROM 担当者テーブル AS TJ
    LEFT OUTER JOIN 残工程テーブル AS TJK1 ON TJ.受注ID=TJK1.受注ID AND TJK1.残工程順序=1
    LEFT OUTER JOIN 残工程テーブル AS TJK2 ON TJ.受注ID=TJK2.受注ID AND TJK2.残工程順序=2
    LEFT OUTER JOIN 残工程テーブル AS TJK3 ON TJ.受注ID=TJK3.受注ID AND TJK3.残工程順序=3
    ORDER BY TJ.受注ID;

    NOBTAさんが言及されていますがPIVOTは1回では集計しきれないので、2回に分けることになりますが、とりあえず書いてみました。

    WITH 残工程テーブル
    AS (
      SELECT 受注ID, [1] AS 残工程1, [2] AS 残工程2, [3] AS 残工程3
      FROM (
        SELECT 受注ID, ROW_NUMBER() OVER(PARTITION BY 受注ID ORDER BY 工程順序) AS 残工程順序, 工程
        FROM 工程テーブル
        WHERE 完了日 IS NULL
      ) AS X1 PIVOT (MIN(工程) FOR 残工程順序 IN ([1], [2], [3])) AS X2
    ), 残予定日テーブル
    AS (
      SELECT 受注ID, [1] AS 残工程1予定日, [2] AS 残工程2予定日, [3] AS 残工程3予定日
      FROM (
        SELECT 受注ID, ROW_NUMBER() OVER(PARTITION BY 受注ID ORDER BY 工程順序) AS 残工程順序, 予定日
        FROM 工程テーブル
        WHERE 完了日 IS NULL
      ) AS Y1 PIVOT (MIN(予定日) FOR 残工程順序 IN ([1], [2], [3])) AS Y2
    )
    SELECT 担当者テーブル.受注ID, 名前, 残工程1, 残工程1予定日, 残工程2, 残工程2予定日, 残工程3, 残工程3予定日
    FROM 担当者テーブル
    LEFT OUTER JOIN 残工程テーブル ON 担当者テーブル.受注ID=残工程テーブル.受注ID
    LEFT OUTER JOIN 残予定日テーブル ON 担当者テーブル.受注ID=残予定日テーブル.受注ID
    ORDER BY 担当者テーブル.受注ID;

    # X1, X2, Y1, Y2は使わないのに名前を付けないと構文エラーで怒られるんですよね…理不尽。

    • 編集済み 佐祐理 2020年11月10日 15:01
    • 回答としてマーク HIRO_KU 2020年11月11日 2:05
    2020年11月10日 14:10
  • 佐祐理様

    いつもありがとうございます。

    WITH句を使うとこんなにもシンプルに書けるんですね。

    勉強になります。

    実際に欲しい情報をすべて詰め込んでクエリを実行してみたところ

    クエリ実行時間 1秒

    となりました。

    SQLのメンテナンスのしやすさを考えると、かなりいいと思いますがWITH句を

    使わない場合より気持ち遅くなるんですね。

    ちなみにPIVOTの方も試してみたところクエリ実行時間1秒未満の一番早い結果になりました。

    ただ欲しい情報を増やすためにPIVOT句を追加する必要があることを考えると、、、

    どれを選択するかもう少し悩んでみたいと思います。

    どちらにしても満足できる結果になりそうです。

    本当にありがとうございます。

    2020年11月11日 2:04
  • パフォーマンスではgekkaさんの方式が効率が良いです。私の挙げたクエリは読み易さ・組み立て易さを主眼に置いているので、多少無駄な読み取りが発生します。

    PIVOTを使った方法ですが、工程IDが主キーの場合、1回のPIVOTで振り分けることができ、その結果に対して工程・予定日を後からLEFT OUTER JOINしていく方法もあります。

    WITH 残工程テーブル
    AS (
      SELECT 受注ID, [1] AS 残工程ID1, [2] AS 残工程ID2, [3] AS 残工程ID3
      FROM (
        SELECT 受注ID, ROW_NUMBER() OVER(PARTITION BY 受注ID ORDER BY 工程順序) AS 残工程順序, 工程ID
        FROM 工程テーブル
        WHERE 完了日 IS NULL
      ) AS X1 PIVOT (MIN(工程ID) FOR 残工程順序 IN ([1], [2], [3])) AS X2
    )
    SELECT TJ.受注ID, 名前, TJK1.工程 AS 残工程1, TJK1.予定日 AS 残工程1予定日, TJK2.工程 AS 残工程2, TJK2.予定日 AS 残工程2予定日, TJK3.工程 AS 残工程3, TJK3.予定日 AS 残工程3予定日
    FROM 担当者テーブル AS TJ
    LEFT OUTER JOIN 残工程テーブル AS TJK ON TJ.受注ID=TJK.受注ID
    LEFT OUTER JOIN 工程テーブル AS TJK1 ON TJK.残工程ID1=TJK1.工程ID
    LEFT OUTER JOIN 工程テーブル AS TJK2 ON TJK.残工程ID2=TJK2.工程ID
    LEFT OUTER JOIN 工程テーブル AS TJK3 ON TJK.残工程ID3=TJK3.工程ID
    ORDER BY TJ.受注ID;

    2020年11月11日 14:34
  • 佐祐理様

    ご連絡が遅くなり申し訳ありません。

    本当に色々なやり方で組むことができますね。

    勉強になります。

    残念ながら工程IDは本テーブルでは主キーではないため、今回は使えなさそうですが、

    同じ手法は使えそうです。

    別で応用してみたいと思います。

    今後ともよろしくお願いいたします。

    2020年11月17日 5:48