2010年12月7日火曜日

Access mdbでのSQLチューニング

こんばんは。(略)部隊長です。

早師走ですね。当部隊もご多分に漏れず、年末進行中です。
クリスマス以降は仕事しません!



今回はまたWindowsAzureから遠ざかり、
AccessMdbをバックエンドに使ったシステムでパフォーマンス悪いぞ!
というご指摘がありまして、
それの対応で へー と思った事があったので書いておきます。


今更mdb?、とかいわない。実際多いでしょ。


【今回の要件】
1.メインテーブル(物件)に、サブテーブル(価格)がぶら下がっている
2.価格は逐次更新され、更新日とともに追加登録される。
3.一覧画面では、最新の価格が取りたい
4.価格テーブルには、価格の他、価格登録時のメモなんかもあり、一緒に取りたい

【改修前のサブクエリ】

SELECT S.物件ID, S.価格ID, S.価格, S.価格設定日, S.お値段メモ
FROM お値段TBL S
WHERE EXISTS (
 SELECT *
 FROM (
  SELECT 物件ID, MAX(価格設定日) as Dt
  FROM お値段TBL
  Group by 物件ID
 ) q
WHERE S.物件ID = q.物件ID
AND S.価格設定日 = q.Dt

サブクエリ内でWhere Exists し、
その中で物件IDでグループした最新価格日を取得しています。
Indexは物件IDだけで、価格設定日には張っていませんでしたが、
これが超遅い。3秒位かかる。

本体はこういうLEFT JOINのサブクエリが4つほどあり、
全部合わせると10秒超える勢い。
全く以て実用に耐え得る性能じゃない。


で、色々試行錯誤した結果、一瞬で結果が返るのがこれ(found by シャチョー)


【改修後のサブクエリ】

SELECT S.物件ID, S.価格ID, S.価格, S.価格設定日, S.お値段メモ 
FROM (
お値段TBL S
INNER JOIN (
 SELECT 物件ID, MAX(価格設定日) as Dt 
 FROM お値段TBL 
 Group By 物件ID
) as sp
ON S.物件ID = sp.物件ID AND S.価格設定日 = sp.Dt)
) 

これで一瞬です。本体のWHERE EXISTSを全てINNER JOINに変更したら、
本体自体一瞬で結果が返るようになりました。


私今まで内部結合で絞り込みをするのはダメかと思っておりましたが・・・
大体なぜこれで速いのか、改善前はなぜ遅いのか、追及してまいりたいと思います。

0 件のコメント:

コメントを投稿

とある規格化されたコード

世の中こんなもんまで規格化されていますよ、というお話 https://ja.wikipedia.org/wiki/ISO_5218 この辺が大変良くできた、ためになる(?)解説記事です。(長い https://qiita.com/aoshirobo/items/32deb...