- PR -

アドレス検索の方法と高速化について

1
投稿者投稿内容
ひろ
ベテラン
会議室デビュー日: 2003/12/26
投稿数: 97
投稿日時: 2006-12-05 15:11
いつもここで勉強させていただいております。
現在以下の問題で悩んでおります。

--------------------------------------------------------------------------------
【住所テーブル】(本番環境のDATA件数：数千万件)
①コード
②場所１（～県）
③場所２（～市）
④場所３（～区）
⑤建物１（丁）
⑥建物２（番）
⑦建物３（号）
⑧Ａサービスステータス
⑨Ｂサービスステータス
⑩Ｃサービスステータス

【条件】
条件１：住所テーブルの①～⑦に対して，全てAND条件で検索し，該当データが存在しない場合条件２へ
条件２：住所テーブルの①～⑥に対して，全てAND条件で検索し，該当データが存在しない場合条件３へ
条件３：住所テーブルの①～⑤に対して，全てAND条件で検索し，該当データが存在しない場合条件４へ
条件４：住所テーブルの①～④に対して，全てAND条件で検索し，該当データが存在しない場合条件５へ
条件５：住所テーブルの①～③に対して，全てAND条件で検索し，該当データが存在しない場合未登録とする

【ステータス】
該当データ１件の場合は，当該データのステータスを適用する。
該当データが複数件の場合，各サービスでステータスが最も良いものを適用する。
--------------------------------------------------------------------------------

条件１～５のＳＱＬを合計５回実行後，取得データの適用ステータスを検出する方法にて
コーディングは可能ですが，本番環境のデータ件数が数千万件あるため，
可能な限りレスポンスを速くしたいと思っております。

ＳＱＬ実行回数を減らすなどして改善は可能でしょうか。
ご教授お願い致します。


Tomcat5.0
j2sdk1.4.2_10

[ メッセージ編集済み 編集者: ひろ 編集日時 2006-12-05 15:16 ]
甕星
ぬし
会議室デビュー日: 2003/03/07
投稿数: 1185
お住まい・勤務地: 湖の見える丘の上
投稿日時: 2006-12-05 16:04
引用:

ひろさんの書き込み (2006-12-05 15:11) より:
ＳＱＬ実行回数を減らすなどして改善は可能でしょうか。
ご教授お願い致します。

可能。
「条件３：住所テーブルの①～⑤に対して，全てAND条件で検索し，該当データが存在しない場合条件４へ。データがある場合条件２へ。以下略」とするだけで、最悪の場合５回行っていたのが、３回になるよね。もちろん最良で２回実行するのだけど・・・。
検索結果をテンポラリテーブルに格納するようにして、「条件５→条件４→条件３・・・」の順番で検索。条件４以降は検索対象のレコード数が減るので、処理も高速になるはず・・・。
どうするのが良いのかは、実際のデータがどうなっているかが重要。住所が広い地域に分散しているのか、あるいは特定地域に集中しているのかでも、最良の方法は違ってくる。
ひろ
ベテラン
会議室デビュー日: 2003/12/26
投稿数: 97
投稿日時: 2006-12-05 16:20
引用:

甕星さんの書き込み (2006-12-05 16:04) より:
可能。
「条件３：住所テーブルの①～⑤に対して，全てAND条件で検索し，該当データが存在しない場合条件４へ。データがある場合条件２へ。以下略」とするだけで、最悪の場合５回行っていたのが、３回になるよね。もちろん最良で２回実行するのだけど・・・。
検索結果をテンポラリテーブルに格納するようにして、「条件５→条件４→条件３・・・」の順番で検索。条件４以降は検索対象のレコード数が減るので、処理も高速になるはず・・・。
どうするのが良いのかは、実際のデータがどうなっているかが重要。住所が広い地域に分散しているのか、あるいは特定地域に集中しているのかでも、最良の方法は違ってくる。


ご返信ありがとうございます。

＞「条件３：住所テーブルの①～⑤に対して，全てAND条件で検索し，
＞該当データが存在しない場合条件４へ。データがある場合条件２へ。以下略」

条件３→４→２の順でＳＱＬを３回実行するということでしょうか。

＞検索結果をテンポラリテーブルに格納するようにして、
＞「条件５→条件４→条件３・・・」の順番で検索。

はい。現在条件５のＳＱＬのみ１回実行して，取得データを検索し，
ＳＱＬを５回実行した場合と同様の結果が得られないか検討・コーディングしております。




shimix
ぬし
会議室デビュー日: 2004/08/05
投稿数: 512
お住まい・勤務地: 大分市
投稿日時: 2006-12-05 16:33
引用:

ひろさんの書き込み (2006-12-05 16:20) より:
条件３→４→２の順でＳＱＬを３回実行するということでしょうか。

条件３→４→２って・・

条件３の結果によって３→２→１もしくは３→４→５の２通りだと読みましたが。

（蛇足）
２分探索を知っていれば応用できていいと思うんですが・・

[ メッセージ編集済み 編集者: shimix 編集日時 2006-12-05 16:35 ]
sawat
大ベテラン
会議室デビュー日: 2006/08/02
投稿数: 112
投稿日時: 2006-12-05 16:34
decode関数を使ってorder byして、１件目にヒットするデータを使うようにすれば、SQL1回でもできると思いますよ。
コード:
select * 
from 住所
where コード = ?
  and 場所1  = ?
  and 場所2  = ?
  and 場所3  = ?
order by decode(建物1, ?, 0, 1),
         decode(建物2, ?, 0, 1),
         decode(建物3, ?, 0, 1),
         Aサービスステータス,
         Bサービスステータス,
         Cサービスステータス
(「?」はプレースホルダーです。)


ただし、これでパフォーマンスが良くなるかは別問題で、どの方法がよいかは実際に測って確かめてみてください。

# decode関数ってOracleだけでしたっけ？代用になるものはあるとおもうけど。
ひろ
ベテラン
会議室デビュー日: 2003/12/26
投稿数: 97
投稿日時: 2006-12-05 17:09
引用:

shimixさんの書き込み (2006-12-05 16:33) より:
条件３→４→２って・・

条件３の結果によって３→２→１もしくは３→４→５の２通りだと読みましたが。

（蛇足）
２分探索を知っていれば応用できていいと思うんですが・・

[ メッセージ編集済み 編集者: shimix 編集日時 2006-12-05 16:35 ]


失礼しました。ご指摘ありがとうございます。
ひろ
ベテラン
会議室デビュー日: 2003/12/26
投稿数: 97
投稿日時: 2006-12-05 17:14
引用:

sawatさんの書き込み (2006-12-05 16:34) より:
decode関数を使ってorder byして、１件目にヒットするデータを使うようにすれば、SQL1回でもできると思いますよ。
コード:
select * 
from 住所
where コード = ?
  and 場所1  = ?
  and 場所2  = ?
  and 場所3  = ?
order by decode(建物1, ?, 0, 1),
         decode(建物2, ?, 0, 1),
         decode(建物3, ?, 0, 1),
         Aサービスステータス,
         Bサービスステータス,
         Cサービスステータス
(「?」はプレースホルダーです。)


ただし、これでパフォーマンスが良くなるかは別問題で、どの方法がよいかは実際に測って確かめてみてください。

# decode関数ってOracleだけでしたっけ？代用になるものはあるとおもうけど。


ご返信ありがとうございます。
HITしたデータ郡の中から各サービスでステータスの最も良いものを抽出しなければなりません。

余談ですが，SELECT区にdecode関数を用いることにより一度の検索で，
取得データを元に，あとはjavaプログラムで処理するという方法が可能かも知れません。
1

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