[comu-ml 0103] Re: 規格数が多い場合に「規格」編集画面に移動で

[Kentaro Ohkouchi (Loop AZ) <ohkouchi@loop-az.jp>]

大河内です.

ちょっと反応が遅れてしまいました...

From: 神谷 浩之 <kamiya@s-cubism.jp>
Subject: [comu-ml 0102] 規格数が多い場合に「規格」編集画面に移動できない
Date: Wed, 27 Jun 2007 12:46:03 +0900

> 
> 開発に関わるモノの性（さが）なのかもしれませんが、恒常的に忙しく
> ちょっとでも空いた隙を見計らって投稿しております ^-^;

お疲れ様です.
僕の場合は, 超多忙な時期は現実逃避でオープンソースに携わっていたりしま
した.
最近, やっと鎮火してきましたが...

以下, 長い引用になってしまいますがご了承下さい.

> 
> ■規格数が多い場合に「規格」編集画面に移動できない

snip...

> select文 に含まれる vw_cross_products_class といったサブクエリが
> 展開されて（規格数が多い場合）膨大なテーブルが作成されているようです。
> 
> 早い段階で product_id で絞込みができるので、vw_cross_products_class
> を使わずに済む SQL に変えたところ、耐えうる処理時間内に返ってくるように
> なりました。
> 
> ＃　変更前 18分（通常はタイムアウト）　⇒　変更後 1秒以内
> 
> 
> DB構造を正確に把握できている方（ロックオン様？）に見て頂きたいのですが
> お願いできますでしょうか？
> メール末尾に参考SQLを記載します。　（MySQL 4.1）
> 
> 同じく、「規格」を押した次の画面の「削除」を押した際の処理も
> 非常に時間がかかっております。
> （こちらは原因を追跡しきれておりません）
> 

snip...

> 【参考】
> 
> ■オリジナル
> 
> $objQuery = new SC_Query();
> 
> $col = "class_id1, class_id2, name1, name2, rank1, rank2, ";
> $col.= "product_class_id, product_id, T1_classcategory_id AS classcategory_id1, T2_classcategory_id AS classcategory_id2, ";
> $col.= "product_code, stock, stock_unlimited, sale_limit, price01, price02, status";
> 
> $sql = "SELECT $col FROM ";
> $sql.= "( ";
> $sql.= "SELECT T1.class_id AS class_id1, T2.class_id AS class_id2, T1.classcategory_id AS T1_classcategory_id, T2.classcategory_id AS T2_classcategory_id, T1.name AS name1, T2.name AS name2, T1.rank AS rank1, T2.rank AS rank2 ";
> $sql.= "FROM dtb_classcategory AS T1, dtb_classcategory AS T2 ";
> $sql.= "WHERE T1.class_id IN (SELECT class_id1 FROM vw_cross_products_class AS crs_prd WHERE product_id = ? GROUP BY class_id1, class_id2) AND T2.class_id IN (SELECT class_id2 FROM vw_cross_products_class AS crs_prd WHERE product_id = ? GROUP BY class_id1, class_id2)";
> $sql.= ") AS T1 ";
> 		
> $sql.= "LEFT JOIN (SELECT * FROM dtb_products_class WHERE product_id = ?) AS T3 ";
> $sql.= "ON T1_classcategory_id = T3.classcategory_id1 AND T2_classcategory_id = T3.classcategory_id2 ";
> $sql.= "ORDER BY rank1 DESC, rank2 DESC";
> 
> 
> 
> ■修正
> （vw_cross_products_classを事前展開し、必要なカラムのみにして、
> product_idで先に絞り込むようにしたもの）
> 
> $col = "class_id1, class_id2, name1, name2, rank1, rank2, ";
> $col.= "product_class_id, product_id, T1_classcategory_id AS classcategory_id1, T2_classcategory_id AS classcategory_id2, ";
> $col.= "product_code, stock, stock_unlimited, sale_limit, price01, price02, status";
> 
> $sql = "SELECT $col FROM ";
> $sql.= "( ";
> $sql.= "SELECT T1.class_id AS class_id1, T2.class_id AS class_id2, T1.classcategory_id AS T1_classcategory_id, T2.classcategory_id AS T2_classcategory_id, T1.name AS name1, T2.name AS name2, T1.rank AS rank1, T2.rank AS rank2 ";
> $sql.= "FROM dtb_classcategory AS T1, dtb_classcategory AS T2 ";
> 
> $sql.= "WHERE T1.class_id IN (SELECT TTT1.class_id FROM dtb_classcategory AS TTT1, dtb_classcategory AS TTT2 LEFT JOIN dtb_products_class AS TT2 ON TTT1.classcategory_id = TT2.classcategory_id1 AND TTT2.classcategory_id = TT2.classcategory_id2 WHERE TT2.product_id = ?)";
> 
> $sql.= " AND  T2.class_id IN (SELECT TSS2.class_id FROM dtb_classcategory AS TSS1, dtb_classcategory AS TSS2 LEFT JOIN dtb_products_class AS TS2 ON TSS1.classcategory_id = TS2.classcategory_id1 AND TSS2.classcategory_id = TS2.classcategory_id2 WHERE TS2.product_id = ?)";
> 
> $sql.= ") AS T1 ";
> 		
> $sql.= "LEFT JOIN (SELECT * FROM dtb_products_class WHERE product_id = ?) AS T3 ";
> $sql.= "ON T1_classcategory_id = T3.classcategory_id1 AND T2_classcategory_id = T3.classcategory_id2 ";
> $sql.= "ORDER BY rank1 DESC, rank2 DESC";
> 
> 

この箇所は, 当方でも問題視しておりましたが,
下記環境で規格数を300件程度まで試した結果, 50〜70ms 程度で返ってきてい
ましたので, ミッションクリティカルな案件でなければ大丈夫であろうと考え
ていました.

テスト環境
PowerMac G5 Dual 2.3GHz Mac OS X 10.4.7
PostgreSQL 8.1.4

ちなみに, vw_cross_products_class を使用しないようチューニングして頂い
たクエリを試してみたところ, PostgreSQL では下記のエラーとなってしまい
ました.

ERROR:  invalid reference to FROM-clause entry for table "ttt1"
HINT:  There is an entry for table "ttt1", but it cannot be referenced from this part of the query.

原因は, vw_cross_products_class の代りに使用しているサブクエリで, LEFT
JOIN しないテーブルのカラムを LEFT JOIN の結合条件にしているためだと思
われます.

  (SELECT 
          TTT1.class_id
     FROM dtb_classcategory AS TTT1,  <- これを
          dtb_classcategory AS TTT2 
LEFT JOIN dtb_products_class AS TT2
       ON TTT1.classcategory_id = TT2.classcategory_id1 <- ここで結合
      AND TTT2.classcategory_id = TT2.classcategory_id2
    WHERE TT2.product_id = ?)

同様にその次にある T2.class_id の IN 句のサブクエリも同様のエラーとな
ります.

MySQL で上手くいっているのでしたら, DB によって処理を分けるのも手だと
思いますが, 規格数が膨大にならなければ, さほど問題にならないと思います
ので, 規格数が多い場合のチューニング例としておく方が良いかもしれません
ね.

しかし, PostgreSQL の場合, 7.x 系と, 8.x 系ではかなり性能差が出るので,
7.x 系を使用している場合は心配です.

また時間を見つけてテストしてみたいと思います.

----------------------------------------------
大河内健太郎(Kentaro Ohkouchi)
E-mail: ohkouchi@loop-az.jp
有限会社Loop AZ
http://www.loop-az.co.jp/
----------------------------------------------