[前][次][番号順一覧][スレッド一覧]

mysql:15753

From: ram <ram <ram@xxxxxxxxxx>>
Date: Mon, 23 Apr 2012 15:39:04 +0900
Subject: [mysql 15753] Re: 行列変換を行う処理を高速化するには?

田中様 お返事が遅れまして済みませんでした。
コメント有難うございました。

教えて頂いたSQLですが、
この処理はバッチ処理的なレポートではなく、リアル表示の照会画面で使用する
ものです。
そもそもこれだけの処理時間が掛かることを想定せず、なんとかなるだろう位の
考えで、
DB設計をしていたのが間違いかもしれません。

早速、教えて頂いたSQLをサーバー上で実行してみました。
さすがに、31回のLEFTJOINは非力なサーバーでは厳しかったようです。
10分位、反応がありませんでした。

使用している言語も、COLDFUSIONというアプリケーションサーバーを使っている
のも、
ネックになっているのかもしれません。PHPよりは遅いという情報もあります。

それから、npdt という列は、CHAR(8)で、yyyymmddの形式で入っているため、止
むなく
Mid(CAST(npdt AS CHAR),7,2)で切りだしています。

ご丁寧なコメント、感謝致します。
-----------------------------------------------
初穂太郎


(2012/04/21 2:05), Tsubasa Tanaka wrote:
> 何度も申し訳ありません。
> 間違いに気付きました。
>
>> SELECT nm,h101,h102,...,h131
>> FROM (
>>  SELECT nm,COUNT(nm) AS h101
>>  FROM nippo
>>  WHERE npdt >= #from_ymd# AND npdt < #to_ymd# AND sycd = #sycd# AND
>> DAY(npdt) = 1
>>  GROUP BY nm
>> ) AS t101
>> ...
> SELECT d.nm,h101,h102,...,h131
> FROM (
>  SELECT DISTINCT nm AS nm
>  FROM nippo
>  WHERE npdt >= #from_ymd# AND npdt < #to_ymd# AND sycd = #sycd#
> ) AS d
> LEFT JOIN (
>  SELECT nm,COUNT(nm) AS h101
>  FROM nippo
>  WHERE npdt >= #from_ymd# AND npdt < #to_ymd# AND sycd = #sycd# AND
> DAY(npdt) = 1
>  GROUP BY nm
> ) AS t101 USING(nm)
> LEFT JOIN ...
> でした。
>
> h101の列を作るサブクエリの前に全nmを取ってこないと、DAY(npdt) = 1の時のnm以外が
> JOINしても現れてきませんでした。。
>
> また、Accessのサブクエリは()ではなく[].(ピリオドつき)で囲まなければならなかったりした気がします。
> 確かバージョンによっては()で囲ってもいけた気がしますが、手元にAccessがないのでごめんなさい。
> "access from句 サブクエリ"などで検索していただければ出てくると思います。
>
> また、訪問回数が0の時の値がNULLになるので、
> 0にしたければCOUNT(nm)をIIF(ISNULL(COUNT(nm)),0,COUNT(nm))などに書き換えればいけるかなと思います。
>
> よく考えてから投稿する様に致します。
> お騒がせ致しました。
>
>
> ts. tanaka//
>
> 2012年4月21日1:06 Tsubasa Tanaka <yoku0825@xxxxxxxxxx>:
>> たびたび田中です。
>> こんばんは。
>>
>> 無理矢理INDEXを利かせる様なSQLを考えてみました。
>> INDEX(npdt,sycd,nm)が定義されている前提です。
>>
>> SELECT nm,h101,h102,...,h131
>> FROM (
>>  SELECT nm,COUNT(nm) AS h101
>>  FROM nippo
>>  WHERE npdt >= #from_ymd# AND npdt < #to_ymd# AND sycd = #sycd# AND
>> DAY(npdt) = 1
>>  GROUP BY nm
>> ) AS t101
>> LEFT JOIN (
>>  SELECT nm,COUNT(nm) AS h102
>>  FROM nippo
>>  WHERE npdt >= #from_ymd# AND npdt < #to_ymd# AND sycd = #sycd# AND
>> DAY(npdt) = 2
>>  GROUP BY nm
>> ) AS t102 USING(nm)
>> LEFT JOIN (
>> ...
>> ) AS t131 USING(nm);
>>
>> npdtがDATE型、DATETIME型、TIMESTAMP型のいずれかと仮定して、
>> Mid(CAST(npdt AS CHAR),7,2)='xx'
>> を
>> DAY(npdt) = xx に置き換えています。
>> ご参考までに。。
>>
>>
>>> …が、経験則としては一度MySQLからデータをインポートしてAccess上にテーブルを作ってあげて、
>>> そのテーブルを見に行くクエリにした方がずっと速いと思います。
>> 書きそびれてしまいましたが、インポート後にインデックスを貼るのをお忘れなく。
>>
>> それでは。
>>
>>
>> ts. tanaka//
>>
>> 2012年4月20日22:05 Tsubasa Tanaka <yoku0825@xxxxxxxxxx>:
>>> はじめまして、田中と申します。
>>>
>>>> id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
>>>> 1    SIMPLE    t@nippod    range    idx1    idx1    26    NULL    111    Using where; Using temporary; Using filesort
>>> Using temporaryはテンポラリテーブルを推奨されているのではなく、
>>> クエリ処理中にメモリ内に収まらなかったので、テンポラリテーブルを作りましたよという事後報告です。
>>>
>>> 一番はメモリ内にクエリの結果が全て収まることですが、
>>> テンポラリテーブルはまずはメモリ上に作られますので、この段階であればそこまで遅くなりません。
>>> 最悪のシナリオはテンポラリテーブルもメモリ内に収まらず、
>>> DISK上にテンポラリテーブルが作られてしまうことです。
>>> ただし、メモリ上で済んだのかDISKまで書いてしまったのかはEXPLAINの結果からだけでは判断できません。
>>>
>>> MyISAMテーブルの場合、
>>> read_buffer_size > 結果セットの大きさ の場合にはテンポラリテーブルは使われません。
>>> InnoDBテーブルの場合は明示的なステータスは無かった様な気がしますが、
>>> innodb_buffer_pool_sizeという変数が大きく関連しています。
>>> まずはどちらのテーブルを使っているかで、これらの値を(可能ならば)大きくして下さい。
>>>
>>> 結果セットの大きさを知る方法は無いので、適当に大きくして下さい。
>>> DB専用のサーバであれば、read_buffer_sizeは全メモリの25%〜50%、
>>> innodb_buffer_pool_sizeは全メモリの80%が目安といわれています。
>>>
>>>
>>>
>>> …が、経験則としては一度MySQLからデータをインポートしてAccess上にテーブルを作ってあげて、
>>> そのテーブルを見に行くクエリにした方がずっと速いと思います。
>>> 月に1度の集計であれば、この方法はどうでしょうか?
>>>
>>> may your problem is solved!
>>>
>>>
>>> ts. tanaka//
>>>
>>> 2012年4月20日17:58 ram <ram@xxxxxxxxxx>:
>>>> 中川様
>>>> 重ね重ねコメント有難うございます。
>>>>
>>>>> お使いのSELECT文のEXPLAINの結果は出せますか?
>>>> EXPLAINは使ったことが無いのですが、
>>>> SELECT文の先頭にEXPLAINと付けて、リダイレクトで実行した結果、以下のように出ます。
>>>> t@nippod というのが実テーブル名で、
>>>> idx1というのが今張ってあるインデックスです。
>>>>
>>>> id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
>>>> 1    SIMPLE    t@nippod    range    idx1    idx1    26    NULL    111    Using where; Using temporary; Using filesort
>>>>
>>>> idx1は WHERE句の中に含まれている列とソートの順である sycd, npdt, nm です。
>>>>
>>>> この Using temporary が気になりますが、CREATE TEMPORARY TABLEで、
>>>> テンポラリテーブルを使ったほうが処理が効率的という意味なんですね?
>>>>
>>>>> 後、
>>>>> read_buffer_size
>>>>> read_rnd_buffer_size
>>>>> sort_buffer_size
>>>>> はそれぞれどんな値を設定されていますか?
>>>> read_buffer_size    65536
>>>> read_rnd_buffer_size    262144
>>>> sort_buffer_size    262144
>>>>
>>>> で、デフォルトから変更していません。
>>>>
>>>> ------------------------------------------
>>>> 初穂 太郎
>>>>
>>>>
>>>> (2012/04/20 15:24), 中川 貴 wrote:
>>>>> (2012/04/20 13:58), ram wrote:
>>>>>> 中川様 コメント有難うございました。
>>>>>>
>>>>>> このインデックスは設定済みですが、ほとんど効果がありませんでした。
>>>>>> 情報がお伝え足りなくて申し訳ないです。
>>>>>>
>>>>>> もう一度、外してから、その前後でパフォーマンス比較をしましたが、
>>>>>> 平均応答時間は、ほとんど変わっていませんでした。
>>>>>>
>>>>>> また、先に件数を絞り込んだほうがよいかと思い、サブクエリーで抽出した後に、
>>>>>> GROUP BYを行ってもみましたが、これも、ほとんど変わらず、どちらかというと、
>>>>>> わずかにパフォーマンスが落ちました。
>>>>>>
>>>>>> SELECT x.nm,
>>>>>> sum(case when x.dd = '01' then 1 else 0 end) as 'h101',
>>>>>> sum(case when x.dd = '02' then 1 else 0 end) as 'h102',
>>>>>> (中略)
>>>>>> sum(case when x.dd = '31' then 1 else 0 end) as 'h131'
>>>>>> FROM (SELECT nm,Mid(CAST(npdt AS CHAR),7,2) as dd
>>>>>> FROM nippo
>>>>>> WHERE sycd = #ck_sycd# and npdate>= #from_ymd# and npdate<  #to_ymd# ) as x
>>>>>> GROUP BY x.nm ORDER BY x.nm
>>>>> お使いのSELECT文のEXPLAINの結果は出せますか?
>>>>>
>>>>> 後、
>>>>> read_buffer_size
>>>>> read_rnd_buffer_size
>>>>> sort_buffer_size
>>>>> はそれぞれどんな値を設定されていますか?
>>>>>
>>>>> ****************************************
>>>>> コープ情報システム株式会社
>>>>> 運用事業部
>>>>> コーププラザサービスデスクグループ
>>>>> 中川 貴
>>>>> 175-0094
>>>>> 東京都渋谷区渋谷3-29-8 コーププラザ5F
>>>>> TEL 03-5778-8010
>>>>> E-MAIL takashi.nakagawa@xxxxxxxxxx
>>>>> ****************************************
>>>>>
>>>>>
>>>>>
>>>>
>


[前][次][番号順一覧][スレッド一覧]

     15742 2012-04-20 09:36 [ram <ram@xxxxxxxxxx>] 行列変換を行う処理を高速化するには?    
     15743 2012-04-20 11:12 ┣[中川 貴 <takashi.nak]                                       
     15744 2012-04-20 13:58 ┃┗[ram <ram@xxxxxxxxxx>]                                     
     15745 2012-04-20 15:24 ┃ ┗[中川 貴 <takashi.nak]                                   
     15747 2012-04-20 17:58 ┃  ┗[ram <ram@xxxxxxxxxx>]                                 
     15748 2012-04-20 22:05 ┃   ┣[Tsubasa Tanaka <yoku]                               
     15749 2012-04-21 01:06 ┃   ┃┗[Tsubasa Tanaka <yoku]                             
     15750 2012-04-21 02:05 ┃   ┃ ┗[Tsubasa Tanaka <yoku]                           
->   15753 2012-04-23 15:39 ┃   ┃  ┗[ram <ram@xxxxxxxxxx>]                         
     15756 2012-04-25 14:54 ┃   ┗[中川 貴 <takashi.nak]                               
     15758 2012-04-25 16:34 ┃    ┗[ram <ram@xxxxxxxxxx>]                             
     15759 2012-04-25 17:09 ┃     ┗[中川 貴 <takashi.nak]                           
     15760 2012-04-25 17:18 ┃      ┗[Tomohiro 'Tomo-p' KA]                         
     15761 2012-04-25 18:25 ┃       ┗[中川 貴 <takashi.nak]                       
     15762 2012-04-25 19:05 ┃        ┗[Tomohiro 'Tomo-p' KA]                     
     15752 2012-04-23 14:17 ┗[<gotou1213@xxxxxxxxx]                                       
     15754 2012-04-23 19:53  ┗[ram <ram@xxxxxxxxxx>]                                     
     15755 2012-04-24 13:50   ┗[<gotou1213@xxxxxxxxx]                                   
     15757 2012-04-25 15:39    ┗[ram <ram@xxxxxxxxxx>]                                 
     15765 2012-04-26 14:12     ┗[<gotou1213@xxxxxxxxx]