- PR -

同じテーブルを複数回結合の書き方と性能

1|2|3 次のページへ»
投稿者投稿内容
TWZZ
会議室デビュー日: 2008/10/24
投稿数: 7
投稿日時: 2008-10-24 14:55
はじめまして。
SQLServer2005で大量データ抽出SQLのチューニングをやっています。

販売履歴テーブルから複数日の販売数を抽出し、同じ行に並べる業務です：

商品コード 前日販売数 前々日販売数 ・・・
--------------------------------------
001 50 40 ・・・
002 15 16 ・・・
・・・

こんな感じで。

テーブルの定義は以下のようです：
商品テーブル
商品コード(PK)

販売履歴テーブル
商品コード(PK)
日付(PK)
販売数

デフォルトのままで、主キーに対してクラスタ化のインデックスが張られてます。
それ以外のインデックスがありません。

で、今までまの書き方は：
--書き方①-----------------------------------------------------------------------
SELECT
T1.商品コード,
T2.販売数 AS 前日販売数,
T3.販売数 AS 前々日販売数
FROM 商品 T1

LEFT JOIN 販売履歴 T2
ON T1.商品コード = T2.商品コード
AND T2.日付 = @前日

LEFT JOIN 販売履歴 T3
ON T1.商品コード = T3.商品コード
AND T3.日付 = @前々日
-------------------------------------------------------------------------

チューニングの為、書き方を以下のように変えて見たんです：

--書き方②-----------------------------------------------------------------------
SELECT
T1.商品コード,
T2.販売数 AS 前日販売数,
T3.販売数 AS 前々日販売数
FROM 商品 T1

LEFT JOIN 販売履歴 T2
ON T1.商品コード = T2.商品コード
AND T2.日付 = @前日

LEFT JOIN(
SELECT
商品コード,
販売数
FROM 販売履歴
WHERE 日付 = @前々日
)T3
ON T1.商品コード = T3.商品コード
-------------------------------------------------------------------------

--書き方③-----------------------------------------------------------------------
SELECT
T1.商品コード,
T2.前日販売数,
T2.前々日販売数
FROM 商品 T1

LEFT JOIN(
SELECT
商品コード,
SUM(CASE WHEN 日付 = @前日 THEN 販売数 ELSE NULL END) AS 前日販売数,
SUM(CASE WHEN 日付 = @前々日 THEN 販売数 ELSE NULL END) AS 前々日販売数
FROM 販売履歴
WHERE 日付 = @前日
OR 日付 = @前々日
GROUP BY 商品コード
)T2
ON T1.商品コード = T2.商品コード
-------------------------------------------------------------------------

それぞれの処理時間は以下です
(どっちも数回実行した平均値です)
書き方①：6.34秒
書き方②：6.18秒
書き方③：5.61秒

で、一番変則と思わしき書き方③が一番いい性能をだしてしまったのです。

自分としてはあんまりこんな書き方にしたくないんですが・・・
あるいは他にもっといい書き方がないでしょうか？
もし経験者がいれば是非教授してお願いします。



追記：大量データDBで実行した結果、書き方①が
20分30秒だったところが書き方③の14分になりました。
期待以上の改善です。
書き方③にしようと思います。

[ メッセージ編集済み 編集者: TWZZ 編集日時 2008-10-24 22:02 ]
こあら
大ベテラン
会議室デビュー日: 2007/06/26
投稿数: 157
投稿日時: 2008-10-24 16:39
引用:

TWZZさんの書き込み (2008-10-24 14:55) より:
で、一番変則と思わしき書き方③が一番いい性能をだしてしまったのです。


クロス集計するときの一番一般的な書き方じゃないですか？
また、SQLServer2005だとPIVOTも使えるみたいです。
TWZZ
会議室デビュー日: 2008/10/24
投稿数: 7
投稿日時: 2008-10-24 17:56
こあらさんありがとうございました。

なるほど、普通な書き方なんですね。
お恥ずかしいです。

PIVOTは初めて知りましたね。
早速調べてみますが、
SQL Server 2005 Books Onlineに以下の説明がちょっと気になります：
引用:

PIVOT 関係演算子を使用すると、SELECT...CASE ステートメントを複雑に組み合わせて同じ操作を指定する場合に比べ、構文が単純で読みやすくなります。

これって、構文が単純になるだけで、実際にやってることは同じなのですかね？
platini
大ベテラン
会議室デビュー日: 2002/12/03
投稿数: 193
投稿日時: 2008-10-24 20:27
引用:

これって、構文が単純になるだけで、実際にやってることは同じなのですかね？

内部実行内容は全く同一だと仮定しても、
実行時間は
●与えられたSQL文の解釈パース時間
●どの索引を使用するかプランニング時間
●実際のデータベースの検索実行所要時間
の和だと思うので、より単純なSQL文となることで
与えられたSQL文の解釈パース時間は減ると思います。

但し、より文章が単純になれば、処理実行時間が短縮されるかは
別物ですが。解釈パース所要時間は確実に短縮化されるでしょう。
TWZZ
会議室デビュー日: 2008/10/24
投稿数: 7
投稿日時: 2008-10-24 22:16
PIVOTの説明資料を見てきました。
確かに構文が単純になるだけでも嬉しいことですよね。
ただし、条件は固定値しか設定できないという、
使い勝手がちょっとあれかなぁと。
こあら
大ベテラン
会議室デビュー日: 2007/06/26
投稿数: 157
投稿日時: 2008-10-25 10:10
引用:

TWZZさんの書き込み (2008-10-24 22:16) より:
PIVOTの説明資料を見てきました。
確かに構文が単純になるだけでも嬉しいことですよね。
ただし、条件は固定値しか設定できないという、
使い勝手がちょっとあれかなぁと。


ではOracleのLag関数の模倣は？
http://oraclesqlpuzzle.hp.infoseek.co.jp/db2/db2-8-1.html



[追記]
ひとつ前のレコードが前日とは限らないので、Lag関数では不可能でしたね。すみません。

自己結合もテーブル数（日付列の数）が増えるとパフォーマンス悪そうですし、
やはり普通にクロス集計が良いですかね。

クエリの性能ではなく、レスポンスを改善するにはインデックス付きビューが使えます。
[/追記]


[ メッセージ編集済み 編集者: こあら 編集日時 2008-10-25 15:30 ]
無名tiger
常連さん
会議室デビュー日: 2008/04/18
投稿数: 36
投稿日時: 2008-10-25 22:57
引用:

TWZZさんの書き込み (2008-10-24 14:55) より:

追記：大量データDBで実行した結果、書き方①が
20分30秒だったところが書き方③の14分になりました。
期待以上の改善です。
書き方③にしようと思います。

[ メッセージ編集済み 編集者: TWZZ 編集日時 2008-10-24 22:02 ]


個人の見解：
１）OR は IN で置き換えましょう。INなら索引が使用できます。
WHERE 日付 = @前日 OR 日付 = @前々日
↓
WHERE 日付 IN ( @前日, @前々日)

２）複合インデックスの場合　設計と書き方によって実はインデックスが使用されていない可能性があります。

ほかの処理に影響がなければ、

販売履歴テーブルの設計
ＰＫ：商品コード、日付
↓
ＰＫ：日付、商品コード

1)、2)の参照資料：
http://www.geocities.jp/mickindex/database/db_optimize.html#LocalLink9

３）SQLの実行時間何分単位になると、やはりSQL文だけの問題ではなく、データベースのチューニングも必要かもしれません。

以上あくまでも個人の見解、検証しませんでした。
_________________
カスタマイズ自由自在のスタートページ。
TWZZ
会議室デビュー日: 2008/10/24
投稿数: 7
投稿日時: 2008-10-27 11:20
こあらさん、無名tigerさん　ありがとうございました。

ORとINの指摘ありがとうございました。
複合インデックスの順番はこれから検証してみます。

データベースのチューニングに関しては、
自分はまだ経験値が足りないと思いますが、
時間の余裕があればやってみます。
1|2|3 次のページへ»

スキルアップ／キャリアアップ（JOB@IT）