Accessでもっと早く探したい

質問

Accessでもっと早く探す方法はないでしょうか？

郵便局のホームページにある全国の住所のデータ12万件を一つのテーブルに入れました。
http://www.post.japanpost.jp/zipcode/dl/kogaki.html

ためしに、自分の住所を検索してみました。
かなり時間がかかりました。

これをもっと早く検索する方法は無いでしょうか？

たとえば、テーブルの仕組みを変える等のやり方はないでしょうか？
手段は問いません。早ければどんな方法でもかまいません。
ちなみに、インデックスは付けてみました。

回答No.1

PPancho · ベストアンサー

a-z_A-Z　さん
検索は郵便番号→住所　の検索でしょうか？
作成された郵便番号マスター？テーブルの郵便番号の項目に
UNIQE INDEXを付与するのが手です。具体的には

１）ACCESSで該当のテーブルを選択してデザインボタンを押下
２）郵便番号の列を選択
　　画面下の標準タブにある
　　インデックス　「はい（重複なし）」にする
　　空文字列の許可　「いいえ」にする
　　値要求　「はい」

（ACCESS 2000/ACCESS XPのテーブルを前提にしています）

お礼

ありがとうございます。

>インデックス　「はい（重複なし）」にする

元データに重複しているところがあったので、”重複なし”は選べませんでした。だから、”重複あり”にしました。

少し早くなりました。

データが多いので時間がかかるのはしょうがないと思いますが、Accessではこれが限度でしょうか？

補足

>検索は郵便番号→住所　の検索でしょうか？
ハイ、その通りです。

回答No.5

PPancho

a-z_A-Zさん

Oracle8は会社の仕事でしたので。。。
リレーショナルＤＢで、検索先のテーブルが複数ある場合は、
テーブルの結合条件、検索条件であらかじめ作成してあるINDEXを
使用するようにSQL文を書き変えたり、Oracleの場合チューニングの
プログラムを実行することでテーブルの全件検索が回避され、速度が
上がります。

念のためACCESSの「ツール」＞「データベースの解析」＞
「パフォーマンスの最適化」で対象となるテーブル、クエリー
フォームなどにチェックをつけて検索すればパフォーマンス改善の
ヒントが何か得られるかもしれませんが。

ただ、今回のように検索先が１テーブルで、かつ検索条件が１列
（郵便番号）の値のみである場合INDEXを付与できるのは１列だけ
ですし、この場合はどのようなDBMSツールでも限界があります。

一般的に検索条件が２列（項目）以上ある場合は、INDEXを２項目
セットで付与したり、WHERE句の順番を変えることで検索速度が
変わります。

色々試して頑張ってみてください。

お礼

いろいろありがとうございます。

どうも、今回の問題に対しては限界のようですね。
（パソコンのレベルを上げる等をのぞいて）

PPancho様は専門家のようですね。
これからもお世話になると思います。
どうぞこれからもよろしくお願いいたします。

回答No.4

PPancho

a-z_A-Zさん

残念ながら小生はMySQLのことはわかりません。
（その昔　Oracle8ならかじったことはありますが。。。。）
ただ、ACCESSからMysql上のテーブルをリンクできれば、
あとは実態がMysqlのテーブルでもACCESSからはACCESSの
テーブルのように見えますので、クエリー、フォーム、レポート
もできるのではないかと思います。
（ACCESSからODBCを使ってOracle8　DBに接続していました）
もし、MySQLを極められるのなら別の形で質問してみて下さい。
（きっとMySQLの専門家の支援が得られるでしょう）

a-z_A-Zさんの回答から直に自分でテーブルを検索していることが
わかりました。これで遅いとなると解決は非常に難しいと思います。

ただ、そもそも今回の目的は日本全国の郵便・住所を必要とされているのでしょうか？
クエリで特定の都道府県（地方）だけに絞って検索する手もあると思いますが。
また、大口以外の普通の郵便番号は市町村合併を除き、
IMEの漢字変換でもある程度サポートされていますが。。。

　回答になっていなくてゴメンナサイ。

お礼

ありがとうございます。

>（その昔　Oracle8ならかじったことはありますが。。。。）
うらやましい・・・。わたしも、Oracleさわってみたいです。でもあれって値段が高いですよね。趣味では、さわる機会が無いです。

>直に自分でテーブルを検索していることが
実は、その後でクエリをつくり、フォームで検索をすると、結構早く検索されました。理由は不明です。テーブルから検索した方が、早いと考えていたのですが、実際は違いました。

>ただ、そもそも今回の目的は日本全国の郵便・住所を必要とされているのでしょうか？
いえ、そう言うわけではありません。「もっと大量のデータでためしたいな～」「おっ、そういえば郵便局のデータがあったな♪」って感じでためしております。

もし、自分個人のデータならエクセルでも十分すぎます。（広告の裏にメモでじゅうぶんです。）

もうそろそろ、この質問を締め切ろうかと考えています。
PPancho様のおかげで、この質問をした時に求めていたレベルには、ほぼ達しました。
しかし、これが限度なのでしょうか？たとえば、テーブルを分散させる等の方法はないでしょうか？（無いとは思いますが・・・）
以前、PostgreSQLの入ったサーバーを何台かつないで、データを処理する方法を見たので、それに似たことが出来ないかな？と、ふと思いました。
そもそも、Accessで高度なことを求めるのが間違っていますが・・・。

私は、趣味でデータベースをやっているので、このような無駄な事をしております。

以前、同じデータをMｙｓｑｌだけでやりました。チューニングで何とかならないかと思って、いろいろ試しましたが、やればやるほど遅くなりました。結局、1.0秒の壁はどうしても破れませんでした。

回答No.3

PPancho

a-z_A-Zさん

ACCESSで扱うデータはトランザクションレベルで10万件レベルが
運用の限界だという話を聞いたことがあります。それ以上ならば
他のDBMSツールを活用し、ACCESSは画面などフロントエンドの
ところに限定すべきかもしれません。

ところでa-z_A-Zさんがいう検索とは、
どうやってやっていますか？方法により改良が可能
かもしれませんよ。

お礼

ありがとうございます。

>ACCESSで扱うデータはトランザクションレベル
Mysqlにつないで、Accessは画面だけを試したのですが、うまく検索できませんでした。
たぶん、文字コードあたりが原因だと思います。
まったく検索される気配がなく、ずっと待たされる状態がつづきます。
今、調査中なのですが、だんだんめんどくさくなってきて、Accessだけで出来ないものかと考えております。

>ところでa-z_A-Zさんがいう検索とは、
テーブルのフィールドを選択して、双眼鏡のアイコンをクリック（検索）を押して、そこにでてくる分に、郵便番号を入れて検索しています。
手違いで、フィールド名をちゃんと入れたテーブルを、削除してしまい、せっかく作ったフォームが無駄になりました。
又作り直すのが面倒なので、今は、テーブルのみの検索しかしていません。

回答No.2

PPancho

a-z_A-Zさん

あまり期待できませんが、データベースの最適化をすることが
できます。

ACCESSで該当のテーブルを選択して、「ツール」→「データベースユーティリティ」→「最適化／修復」を選択します。

あと郵便番号欄のデータ形式はテキスト型だと思いますが、
フィールドサイズは何桁（何バイト）に設定されていますか？
100-1234で8バイトならしかたありませんが、50バイトなら
8～10バイト程度に縮小したほうが余分なエリアを確保しないので。

ACCESSのバージョンにもよりますが、マスターとは言え10万件以上の
データを扱うのはよほどパソコンのCPUとRAMがハイスペックでないと
難しいかと思います。

お礼

ありがとうございます。
試してみましたが、速度は変わりませんでした。

>マスターとは言え10万件以上の
とても大きな数だと思いますが、ある商品の在庫管理を想定すると、そんなに莫大な数字ではありません。
Accessって案外使い物になりませんね。