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

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

新規登録して質問してみよう
ただいま回答率
85.50%
SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

Q&A

解決済

3回答

6141閲覧

SQLの集計(SUM)時のレスポンス向上について

ktamon

総合スコア35

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

0グッド

0クリップ

投稿2018/10/25 07:25

SQL Serverを使用してデータベースの集計用のSQLを作成しております。

以下のテーブル TB_成績表 があったと仮定させていただきます。
|クラス|班|性別|学生コード|国語|算数|理科|社会|英語|
|:--:|:--:|--:|
|1組|A|男|00001|10|10|20|20|30|
|1組|A|女|00002|20|50|30|10|40|
|2組|B|男|00003|30|40|40|50|50|
|1組|A|男|00004|10|10|20|20|30|
|2組|A|女|00005|20|50|30|10|40|
|1組|B|男|00006|30|40|40|50|50|
|2組|A|男|00007|10|10|20|20|30|
|2組|A|女|00008|20|50|30|10|40|
|1組|B|男|00009|30|40|40|50|50|

上記のテーブルに対して集計を行うために、
同一組、同一班で各科目の点数の合計を出そうとした場合に、

SQL

1SELECT 2 クラス, 3, 4 SUM(国語) AS 国語総計, 5 SUM(算数) AS 算数総計, 6 SUM(理科) AS 理科総計, 7 SUM(社会) AS 社会総計, 8 SUM(英語) AS 英語総計 9FROM 10 TB_成績表 11GROUP BY 12 クラス, 13

という構文で行っております。
※Indexはクラスと班の複合インデックスを作成しています。

上記の場合に件数が少ない場合は問題ないのですが
数十万件単位での処理となった場合に、集計関数のレスポンスが急激に悪くなっております。
いろいろと検索サイト等でも調べているのですが皆さまのお知恵を拝借して他にチューニング等が
可能な個所がないか意見を頂ければと思い投稿させていただきました。

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

SQLよりも最適化を確認した方が良いと思われます。
インデックスの再構成と再構築

状況によって、
・ALTER INDEX REORGANIZE
・ALTER INDEX REBUILD
を使い分けとあります。
また、

断片化のレベルが非常に低い場合 (5% 未満) は、通常、これらのコマンドのいずれも使用しないでください。インデックスの再構成や再構築には、ほとんどの場合、そのようなわずかな断片化を解消するには見合わないコストがかかります。

というのも。
何れにしてもコストに見合う効果の話なので、改善しなければ実行してみた方が良いかと思います。
確実なのはREBUILDの方なので、REORGANIZEで改善しない場合は、REBUILDを。

後は、統計情報の更新ですね。
どうする? SQL Server のクエリ パフォーマンスが低下した!

そもそも、インデックスが使われているかも確認して下さい。
実行計画の表示方法

投稿2018/10/25 07:37

編集2018/10/25 08:47
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ktamon

2018/10/25 07:42

回答ありがとうございます。 REORGANIZE及びREBUILDを試してみます。
ktamon

2018/10/25 08:31

REBUILDをした結果、数秒早くなりましたが劇的には改善されませんでした。 統計情報の更新について夜間に流して明日以降試してみるようにします。
sazi

2018/10/25 08:48

そのテーブルには他にもインデックスがあったりしませんか? 追記しましたので、実行計画を確認して下さい。
ktamon

2018/10/25 08:54

ご親切にありがとうございます。 一応実行計画を確認しておりますが、IndexScanにはなっておりました。 ※Index Scan(NoClusterd)となっておりました。
sazi

2018/10/25 09:10 編集

数十万件ということですけど、1回のテストでそこまでの学生数にはならないと思いますので、その他に年度とかの項目があったりしませんか?
sazi

2018/10/25 09:12

よく見ると質問は仮定ですね。参照しているのがビューだったりしませんか? チューニングのような繊細なものは、情報が不足していると、適切なアドバイスにはなりませんので。
ktamon

2018/10/25 09:21

すいません、業務にかかわるところだったので仮定とさせていただきました。。。 参照先についてはビューではありません。 ※むしろビューを作成しようとしている状況です。。。 ただ、数十万件と記載しましたが、実際は800万件ほどのレコード数になります。 ※実際には現在、30秒ほどかかる形です。 なので、このままチューニングを続けるか、定期的に実体化(テーブル化)してそこを参照する形にするかを悩んでおります。。。
sazi

2018/10/25 09:54

800万件全体の集計ですか? 集計結果としては何件位なのでしょう? 集計結果の件数が大きいようだとカーディナリティが低いということになりますから、 改善はあまり望めないと思います。
ktamon

2018/10/25 09:58

ありがとうございます、 集計結果は70万件超になりました。 改善見込めなさそうであればやはり定期的な実テーブルへの吐き出しを検討していこうと思います。
sazi

2018/10/25 10:05

全件(800万件)に対しての集計なら、別テーブルの方が良いでしょうね。 全件じゃないなら、パーティションという方法も考えられますが。
ktamon

2018/11/01 01:05

すいません。。。 SQLSERVERでもパーティションの概念あるの失念してました。。。 定期的にテーブルに情報掃き出しのためにテーブル定義見直しとなりましたので参考にさせていただきます。ありがとうございます!
guest

0

sqlを 以下の方法で集計してみてはどうでしょうか?
select sum(tb_kokugo) as tensu from
(select tb_class,tb_han,tb_kokugo from scm1.tb_seisekitbl
where tb_class ='1' and tb_han='A') as ViewA;
上記記述は、postgresで記述できるdmlなのですが、おそらくsqlserverでも、使えるかと思いますが
上記方法だと、必要なレコードを抽出してから集計するのでいくらか高速に動くかと思います。

投稿2018/10/25 10:57

akirafudo6

総合スコア341

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ktamon

2018/10/25 12:08

ありがとうございます。 内容理解して試させていただきます。
ktamon

2018/10/30 04:04

遅くなりました。 試してみました、確かに早く5秒ほど上がりましたが申し訳ありません 別テーブルに定期的にデータを吐き出す形にしようと思います、ありがとうございます。
akirafudo6

2018/10/30 05:04

であれば、テーブル設計からの見直しですね。頑張ってください!
ktamon

2018/11/01 01:04

ありがとうございます! また質問させていただくかもですがよろしくおねがいします!
guest

0

ベストアンサー

※Indexはクラスと班の複合インデックスを作成しています。

クラス、班、各教科を横断したインデックスがいるのでは?

投稿2018/10/25 07:32

yambejp

総合スコア114572

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ktamon

2018/10/25 07:42 編集

回答ありがとうございます。 すいません、 Index_01 クラス, 班,国語 Index_02  クラス, 班,英語 という形で作るべきという 意味でよろしいでしょうか?
yambejp

2018/10/25 07:42

index(クラス,班,国語), index(クラス,班,算数), index(クラス,班,理科), index(クラス,班,社会), index(クラス,班,英語) すると明示的ですが index(クラス,班,国語,算数,理科,社会,英語) でもそれなりの効果は得られると思います
ktamon

2018/10/25 07:43

ありがとうございます。 作成して試してみるようにします。
sazi

2018/10/25 07:48

SELECTに対して使用されるINDEXは一つですので、科目ごとのインデックスで最高の効果を生むには、unionクエリーである必要があります。 全科目並べると、インデックスのみで解決するので、効果が無いわけではありませんが。
ktamon

2018/10/25 08:30

補足等含め回答ありがとうございました。 試してみましたが効果が得られませんでした。 もう少し見直してみるようにします。 ※UNIONもやってみましたが若干遅くなっていました。
yambejp

2018/10/25 09:32 編集

データに日付など埋め込んで、サマリーテーブルを作っておいて定期的に流し込むのが妥当でしょうね 近々のデータだけリアルに拾ってサマリーの数値を足せば800万件くらいならなんとかなるかも あとはSQL Serverのオプティマイザ次第かなと。
ktamon

2018/10/25 09:44

やはり。。。 了解です、その線でも当たってみるようにします。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問