仕事で大量データをMS Accessで処理しないといけなくなったとき、無茶苦茶使うノウハウ

システムの運用保守をしていると、ときに数十万件から数百万件のデータをデータベース上ではなくて手元で処理しなければならないことがある。大概の基幹システムというのはJ-SOXもそうだしそうでなくても、本番環境上で好き勝手なことはなかなかできない。

私の場合はSAPがメインなので、ECC6.0の標準テーブルや関連テーブルを落として結合してみせる…のような局面。それも含めて、心得をメモ。

SAPからの大量データダウンロードはSQVIで、ローカルファイル出力を使う

これが絶対的に最強。タブ区切りで落ちる。単一テーブルはおろか制限はあるもののテーブル結合も可能。項目を絞れば更に早い。ALVリストとかを一旦出力してからダウンロードボタンを押すのはだめ。SE16も遅い。

MS Accessへのインポート時は、型指定を怠らない

テキストファイルをインポートするとき、Accessは中身を見て賢く型を提案してくれるが、基本的に信じない。全カラムの型設定ができるボタンがあるのでそこに入り、全選択してExcelで一挙に調整。またExcelから戻してこれるのでぜひお試しあれ。

基本的に全部テキスト型にしてしまえばいい。乱暴でいいと思う。どうしてもあとの工程で困るものだけは日付と、金額や数量などだけ調整する。数値項目は面倒だから全部、倍精度浮動小数点。これで困らない。

伝票番号などの番号系や、なんとかタイプとかの各種属性は迷わずテキスト型のまま。この基本は後でジョインするときに生きてくる。

属性項目のデコードテーブルを取り込むときには主キーを指定

例えば伝票タイプの項目の横に、伝票タイプテキストを結合したい場合、タイプのコードとテキストだけのテーブルをジョイン先として用意するはず。このときにaccessのテーブル定義上は絶対に主キーとして設定する。こんなのいいでしょ、とズボラをしてはいけない。理由は当然、索引検索の恩恵を受けること。

クエリデザイナでクエリを作るときは命をかけて外部結合にこだわる

あれ〜、Accessでクエリ作るやりかたは知ってるしちゃんとできてるんだけど、なんか遅いなー、とぼーっとしている人はほぼここ。これをやっていない。

断言できるが、90%以上の局面で、選ぶべきは外部結合だ。内部結合が選択されるべき局面なんてまず出会わない。トランを軸にマスタを外部結合する。

理由は2つあって、速度とレコード件数の網羅性と同一性維持。試せばわかる、外部結合ときっちりした索引検索の速度への貢献。同一性というのは無駄なレコード欠落や増幅への対策になるということ。

テーブル同士の結合の線が矢印になっていなければ、教えてあげたいといつも思う。

多段クエリは恐れないが、テーブル作成クエリもボチボチ使う

Accessのすごいところはクエリをペタペタ作れて、するっとそのクエリを元ネタにまたペタペタができること。調子に乗って何重にもやってしまったら危ない？とあまり躊躇することなかれ。パフォーマンス劣化は心配するほどは起きない。多分中のアルゴリズムが賢い。

どうしても何かが辛くなったらテーブル作成クエリで、セーブポイントを作るイメージで考える。

複数のクエリで段階的に加工すると、メンテナビリティが気になるかもしれないけど、名前の付け方のセンス次第なのと、一つのクエリでむちゃくちゃなことしてる方が困るともいえる。

（ほか思いついたら追記予定）