2GBの壁
500万件の所蔵レコード(テキストファイル)をMS-Accessに取り込んでみた。
次期システム用のデータコンバートのためである。
サーバのデータベースに格納されている所蔵データをテキストダンプツールで落して、細かい文字列変換やコードマッピングは使い慣れたAccessで処理しようという基本戦略を打ち立てた。
PerlやPHPでもよかったのだが、過去自分で作ったAccessのモジュールやクエリなどの部品をたくさん持っていたので、Accessでの「マッシュアップ」には少し自信があった。
Accessのファイルサイズ制限は2GBであるが、500万件のテキストファイルをインポートすると、約1.7〜1.8GB程度になる。
この状態で、これ以上の処理をこのAccessファイル上で行うのは困難(というか無謀)である。
技術者によっては、「これだけの膨大なデータをAccessで処理すること自体が無謀でしょう」という人も多いだろう。
しかし、私はあえて無謀な賭けに出た。いや、挑戦した。
資産の有効活用といえば聞こえはいいが...
何しろ、Accessの更新処理中に2GBを超えると、
「データベース 'ファイル名' を開くことができません。アプリケーションで認識できないデータベースであるか、またはファイルが破損しています。 」
というメッセージが表示される。
もちろん、最適化を掛ければ、容量は圧縮されるが、修正中で保存していないモジュールやクエリなどの変更点がロールバックされる(変更前の状態に戻される)ため、場合によっては、大きな手戻りが発生するのだ。
だから、この巨大なファイルをいくつかのブロックに分ける必要があった。
今回は、キャンパス毎×図書・雑誌毎に分けることにした。
その結果、1ファイルで100MB〜500MBぐらい(件数は10万件〜100万件ぐらい)のファイルに分割することができた。
さあ、もうこれで2GBの壁を気にせずにバンバン処理ができるぞ!と意気込んだのも束の間、次なる問題にぶつかった。
200MBぐらいのファイルで、VBAを使ってレコード項目の更新(Rs.Update)を行うと、みるみるうちにファイルサイズが膨らんでいって、あっという間に1GBを超えてしまうのだ。
この「Update時のMDBの肥大化」については、他の事例もあることがわかっている。
11万件のデータに対し1件あたり8回のUPDATEを行なったところ
MDBが2Gを超えてしまい、壊れてしまった。ところが同じ処理を2000にエクスポートして行なうと、正常に終了。
試に2000のSR-1では2003と同様の現象が起きた。
MDBの大きさは80M内処理の対象となるテーブルは30M程度の大きさです。疑問点は以下の2つ。
1)UPDATEを行なうたびにMDBは肥大化してゆくのか?
それはレコード単位であるのか?
つまり、1レコードに対し8回UPDATEすると8レコード分のゴミが
MDBに蓄積されるのか?
2)この現象はADOのVerの違いに原因があるのか?(AccessClub「異常にMDBが大きくなってしまう」より引用 )
私の疑問もまさに上記1)であり、レコード単位で更新前ログをMDB内に保管するようなしくみだとすれば、1レコード×更新回数分の容量が必要になってくる。
これでは堪らない。
レコードロックの観点からも以下の指摘があるため、注意を要する。
Access2000 からはレコード単位のロックもサポートしてますので、Access の機能だけでレコードロックを実現するにはこの機能を使うのもテなんですが、どうやらこのレコード単位ロックを選択すると、やけに MDB が肥大化するという噂ですので、あんまりお勧めしません。
(Fly Me To The Access-Heaven「排他制御の罠」より引用)
結局、ファイル分割の他に、1回の更新毎に(手で)最適化を掛けることで対処してしまったが、この最適化もできれば自動で実施したかった。
しかし、自動化も場面によっては難しいようだ。
Access2003までであれば、下記のコードで、現在開いているデータベース自身を最適化できます。
Sub CompactAndRepairDatabase()
Application.CommandBars.FindControl(id:=2071).accDoDefaultAction
End Sub※ Access 2000と2003で動作の確認を行いました。
※ 入力中のデータが失われる場合がありますので、下記コードを実行する際は十分ご注意ください。(Beginer's Memo 初心者備忘録−Office関連「開いているデータベースを最適化する(Access VBA)」より引用)
Access2007になると、相当メンドくさくなるようなので注意!とのこと。
Access2003を追加インストールしてやってみるかなぁ。
とまあ、かなり苦労している訳です。
サンプルデータはこの程度で済んだが、本番移行データは待ったなしなので、今のうちにいろいろと不具合をたたき出しておきたい。
目指せデータ・コンバージョン・マスター(DCM)!?