質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.79%

SQLの結果が返ってくるのに時間がかかりすぎる

受付中

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 1,568
退会済みユーザー

退会済みユーザー

イメージ説明

画像のようなレコードのテーブルがあります。
<organNum>は組織コードとして11,31,51の3パターンがあります。
<dtDateTime>は記録された時間になります。
<timeUnit>は<dtDateTime>をHHで区別した値になります。
<timeGroup>は午前=10, 午後=20くらいのものだと思ってください。(今回の質問には関係ないため)

これに対してのSELECT文が

SELECT
   organNum,
   timeUnit,
   SUM(CASE WHEN DATEPART(MONTH,dtDateTime) = 4 THEN 1 ELSE 0 END) AS '4月',
   SUM(CASE WHEN DATEPART(MONTH,dtDateTime) = 5 THEN 1 ELSE 0 END) AS '5月',
   SUM(CASE WHEN DATEPART(MONTH,dtDateTime) = 6 THEN 1 ELSE 0 END) AS '6月',
   SUM(CASE WHEN DATEPART(MONTH,dtDateTime) = 7 THEN 1 ELSE 0 END) AS '7月',
   SUM(CASE WHEN DATEPART(MONTH,dtDateTime) = 8 THEN 1 ELSE 0 END) AS '8月',
   SUM(CASE WHEN DATEPART(MONTH,dtDateTime) = 9 THEN 1 ELSE 0 END) AS '9月',
   SUM(CASE WHEN DATEPART(MONTH,dtDateTime) = 10 THEN 1 ELSE 0 END) AS '10月',
   SUM(CASE WHEN DATEPART(MONTH,dtDateTime) = 11 THEN 1 ELSE 0 END) AS '11月',
   SUM(CASE WHEN DATEPART(MONTH,dtDateTime) = 12 THEN 1 ELSE 0 END) AS '12月',
   SUM(CASE WHEN DATEPART(MONTH,dtDateTime) = 1 THEN 1 ELSE 0 END) AS '1月',
   SUM(CASE WHEN DATEPART(MONTH,dtDateTime) = 2 THEN 1 ELSE 0 END) AS '2月',
   SUM(CASE WHEN DATEPART(MONTH,dtDateTime) = 3 THEN 1 ELSE 0 END) AS '3月',
FROM
   テーブル
GROUP BY
   organNum, timeUnit


というように月ごとにカウントをとり、organNumとtimeUnitでグループ化をしています。
自分では問題のないSQLだと思うのですが、これの結果が返ってくるのに6分もかかってしまいます。
レコードが多すぎるのではないかと思いましたが、4600件ほどしか入っておらず、問題になっているとは思えません。
また、予定ですが7~8万件のレコードが入る予定です。

何かパフォーマンスを著しく下げる要因があれば教えて頂きたいです。よろしくお願いします。
環境はSQLServer2016です。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • wtokuno

    2019/04/24 13:23

    パフォーマンスの問題を分析したいなら実行計画を取るのです。
    https://qiita.com/kazuho39/items/0d3e617661670311ea05

    キャンセル

  • sazi

    2019/04/24 15:42

    多分インデックスでしょうね。実行計画があれば確認出来そうですが。

    キャンセル

  • sazi

    2019/04/24 16:16

    因みにネットワーク要因ではないですよね?
    集計しない状態でのSELECTは十分早いですか?

    キャンセル

回答 2

+2

dtDateTimeとは別にmonthデータを保持して、そのカラムにインデックスを設定しておけば
無駄な計算が減るので高速化が期待できます。

もしくはDATEPART(MONTH,dtDateTime)でgroup byしてcount(*)したものを
サブクエリにして展開してはどうでしょうか?

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

1つのSELECT句で全ての集計を行っているので
1レコードを出力するために、複数回検索をかけて絞り集計しています。
1レコード出力するのに12回検索して12回集計している感じだと思います。

WITH句を用いて中間テーブルを挟み、集計しやすくデータを加工するといいと思います。

WITH A AS(
--dtDateTime列を集計用に展開
SELECT
   organNum
   ,timeUnit
   ,DATEPART(MONTH,dtDateTime) as count_month_flag
FROM
   テーブル
),B AS(
--月別での集計値
SELECT
    organNum
    ,timeUnit
    ,count_month_flag
    ,count(*) count_month
FROM
    A
GROUP BY
    organNum
    ,timeUnit
    ,count_month_flag
),C AS(
--縦持ちから横持ちへ
SELECT
    organNum
    ,timeUnit
    ,CASE WHEN count_month_flag = 4 THEN count_month ELSE 0 END AS month4
    ,CASE WHEN count_month_flag = 5 THEN count_month ELSE 0 END AS month5
    ,CASE WHEN count_month_flag = 6 THEN count_month ELSE 0 END AS month6
    ,CASE WHEN count_month_flag = 7 THEN count_month ELSE 0 END AS month7
    ,CASE WHEN count_month_flag = 8 THEN count_month ELSE 0 END AS month8
    ,CASE WHEN count_month_flag = 9 THEN count_month ELSE 0 END AS month9
    ,CASE WHEN count_month_flag = 10 THEN count_month ELSE 0 END AS month10
    ,CASE WHEN count_month_flag = 11 THEN count_month ELSE 0 END AS month11
    ,CASE WHEN count_month_flag = 12 THEN count_month ELSE 0 END AS month12
    ,CASE WHEN count_month_flag = 1 THEN count_month ELSE 0 END AS month1
    ,CASE WHEN count_month_flag = 2 THEN count_month ELSE 0 END AS month2
    ,CASE WHEN count_month_flag = 3 THEN count_month ELSE 0 END AS month3
FROM
    B
)
--穴埋め処理
SELECT
    organNum
    ,timeUnit
    ,SUM(month4) as '4月'
    ,SUM(month5) as '5月'
    ,SUM(month6) as '6月'
    ,SUM(month7) as '7月'
    ,SUM(month8) as '8月'
    ,SUM(month9) as '9月'
    ,SUM(month10) as '10月'
    ,SUM(month11) as '11月'
    ,SUM(month12) as '12月'
    ,SUM(month1) as '1月'
    ,SUM(month2) as '2月'
    ,SUM(month3) as '3月'
FROM
    C
GROUP BY
    organNum
    ,timeUnit
;

※実行検証はしていないため悪しからず…

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/04/24 15:40

    >1レコードを出力するために、複数回検索をかけて絞り集計しています。
    そんな動作はしません。
    そのWITH式は単にインラインビューでネストしているのと変わりません。
    自己結合でもないので、WITH式にしてもレスポンスは向上しないと思いますよ。

    キャンセル

  • 2019/04/24 15:49

    ご指摘ありがとうございます。
    大変恐縮ですが、レスポンスを向上させるには、どのような書き方ありますでしょうか?

    キャンセル

  • 2019/04/24 16:05 編集

    質問の記述はクロス集計として一般的だと思います。
    ですので、書き方よりインデックスの設定だと思います。
    質問にはその情報が無いので、回答は出来ませんが。

    キャンセル

  • 2019/04/24 16:10

    ありがとうございます。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.79%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る