大量のテストデータを快適に作る７つのコツ - 負荷テストのためにExcel＆VBAをうまく使う
Webアプリケーションが実運用に耐えうるかどうかテストするためには，
大量のテストデータが必要になる。
規模にもよるが，場合によっては１テーブルあたり，数十万〜数百万レコードを要求されるだろう。
システムの負荷テストを実施する際には，
（１）：
「システムの内部に保有する負荷」つまり「DBの重さ」をまず作り出し，
SQL実行性能を現実の運用状態に合わせてチューニングする。
（２）：
その後で，「システムの外部で発生する負荷」つまり同時アクセスをかける。これがサーバーの重さとなる。
そして各機能ページのパフォーマンスを測定する。
後者は，jmeterなどの便利なツールが存在する。
が，前者の「データ生成」に関しては，方法がそれほど確立していないように思える。
下記は，そういった大量のデータをうまく作るためのメモ。
(１) Excelでデータを作るべし
エクセルのインタフェースは，連続データを「直感的」に生成するのに最適だろう。
いきなりinsert ... と手書きでDML文を作り始める必要はない。まず最初の数件〜数十件のデータだけ，Excelのシート上で作成する事にしよう。
- 列をカラム，1行を1レコードとみなす。
- 最初の行にはテーブル定義をコピペしておく。
- テーブル定義の下に，レコードの内容を書いていく。
- 最初の1件か2件を手入力したら，あとはフィルで連続データを生成する。意図と違うものができたら修正する。
これは，非常に直感的な方法だ。
- Perlなどのスクリプト言語でデータを生成していく方法と異なり，「計算式がどのような値・結果になるか」がその場でわかる。
- 該当する行とカラムを見つけるのは極めて簡単だ。「今，ある１つのカラム内で，連続してどのようなデータが生成されたのか？」という事も一読して把握できる。
- 「テーブル内の全レコードを１カラムだけ修正したい」という場合も，その列だけフィルすれば済む。
- セル内での特殊文字のエスケープも，DML文を手書きで書く場合ほどに気にする必要がない。( 文頭の ' だけは2回重ねて書く必要がある。また，データの種類は「標準」から「文字列」「数値」などに適宜変更しておくとよい)
なおランダムデータの生成ツールというものも多数存在するが，テーブル間に多数のアソシエーションがある場合，対応しきれない。
(２) できれば Excelは 2007 以降を使うべし
97 - 2003までのExcelでは約6万5000件が限界だったが，Office 2007では約104万行までの行数を扱えるようになった。
百万件を超える件数のデータが必要な場合には，後述するマクロでのフィル処理を，複数シートにわたって実施するよう調整すればよい。
またExcel2007にすれば，97 - 2003 までの形式に比べ，ファイルサイズが圧倒的に小さくなる。
手元の例で言うと，旧形式の100メガの.xlsファイルを2007形式で保存し直したところ，2MBの.xlsxファイルになった。
約50分の１である。
Excel2007では2003の旧形式のファイルも開ける（互換モード）。互換モードでは65000行までしか扱えない。
互換モードの状態から2007の新形式に保存し直したときは，いったんファイルを開きなおす事で，104万件のデータが扱えるようになる。
このように，大量のデータを手軽に扱いたい場合，Excel2007の導入が効果的である。
しかしExcel2007が手元にない場合でも，やはり上述したように複数シートに分けてデータをフィルするように調節すればよい。
(３) 作業前に，自動保存をオフにすべし
これは大きなファイルを扱う時の基本になる。余計な処理は発生させないのがよい。
うっかりすると，急に自動保存が始まり，数分間ほどPCが固まるという事態が繰り返される（手元PCのメモリ等性能にもよるが）
Excel2007では，左上のOfficeボタンから，Excelのオプション→保存→このブックの自動保存を行わない にチェックを入れればよい。
(４) １つのエクセルファイルに１つのテーブルだけを入れるべし
データサイズにもよるが，件数が多い場合は１テーブルにつき１つの.xlsxファイルを用意する事が望ましい。
一部のデータの修正が必要になるたびに，重いファイルを開きなおすというのは時間の無駄になる。
(５) データの周期性・階段性を理解すべし
データ入力の際には，下記の関数を何重にも組み合わせることになる。
かりに，A4のセルにレコードのIDが入っているとする。
- カラム内が 1 から n の周期データの場合： mod( A4 - 1, n ) + 1
A列にレコードのID（主キー）が格納されており，そのIDに合わせて 1,2,3, ...., n, 1,2,3, ...., n, というデータを生成したいとする。
単純に mod( A4, n ) としただけでは，n 個目のデータは n ではなく，0 になってしまう。
これを避けるために，先に１引いておき，あとで１足し直すのである。
- 整数値がとびとびに増加する場合： ceiling( A4 / 2, 1 )
この場合，IDが 1,2,3,4,... と増加するのに合わせて， 1,1,2,2,... なる増加列を作ることができる。
これらを組み合わせて，例えば 1,1,2,2,3,3,1,1,2,2,3,3,... という周期的な並びを作りたいなら，関数を組み合わせて
= mod( ceiling( A4 / 2, 1 ) - 1, 3 ) + 1
とする。
(６) マクロでフィルすべし
フィルを行なう方法はいくつかある。
- 選択領域の右下をマウスでドラッグして，領域を広げる方法。
数十万件のフィルを行なうためには，数分〜10分間ずっとマウスを押さえている必要がある。
- キーボードショートカットを利用する方法。
例えば，Ctrl＋下キーでシートの一番下にジャンプする機能を使って，Shift＋Ctrl＋下キーで，列を最終行まで選択する。
その後，Excel2007のリボン上で，ホーム→編集→フィル→連続データの作成，を選ぶ。するとフィルが終わる。
- マクロを利用する方法。
この場合，件数の調整や途中経過出力など，様々なカスタマイズができる。
AutoFillメソッドを利用してフィルを行なってくれるマクロを次記事に掲載した。
Excelシート上で指定領域をオートフィルするVBAマクロ
http://d.hatena.ne.jp/language_and_engineering/20080929/1222698371
この場合，所要時間は
- 35カラムで12万レコードは3秒
- 10カラムで24万レコードは9秒
- 70カラムで50万レコードは68秒
- その次の50万レコードは133秒（大きなID値を扱ったからか）
といったものになった。
Excel2007では，リボン中の開発タブからマクロを選択し，関数名を適当に入力して作成を選択すると，Visual Basicのエディタが現れる。
このエディタ上に関数が書いてあれば，シート上で再度マクロのウィンドウを開く事により，その関数を実行できる。
（関数はVisual Basicエディタ上に書いてあるだけでよく，保存する必要はない。マクロを保存しようとすると，大量のデータと一緒にマクロを保存しようとするものだから，時間がかかってしまう。）
(７) csvにエクスポートすべし
データがそろったら，それをDBにインポートするわけだが，DML文を作るのに苦労する必要はない。
例えばこのようなツールがある。
インサート文を生成するマクロ
http://sugarmemo.blog7.fc2.com/blog-entry-5.html
しかし，いったんSQLに変換したら，そのファイルを処理できるツールは限られたものになる。またファイルサイズもかなり増える。
汎用的に利用できる形式として，ここはデータをCSVに変換するのがよい。
Excelの保存メニューでカンマ区切りのCSV形式を選び，保存後，csvの先頭と末尾に余計な空行が混入していないかどうか，念のため開いて確かめる（エディタで開いて確かめればよい）。
csvにすれば，DB側がテーブル上の型に合わせて適宜insertしてくれるのである。
ここでは例として，PostgreSQLのスキーマ上にCSVからインポートする方法を掲載する。
- コマンドプロンプトからpsqlにログインする
psql -U user_name -d db_name
- \l でデータベース一覧を表示，\q で終了できる。
- csvファイルの文字コードと，テーブル名と，csvの絶対パスを指定し，インポートする。
SET client_encoding TO 'SJIS';
copy tablename from 'D:/temp/a.csv' with csv;
もちろん，このcopyコマンドもテーブルの数だけ打ち込む必要があるわけだが，手書きで書く必要はない。
はじめから，xlsファイルをcsvに書き出す際に「テーブル名.csv」という名前にしておく。
そして，テーブル定義書からテーブル名を縦一列でコピーし，新規エクセルシートに貼り付ける。
あとは，その貼り付けたテーブル名一覧から，csv名を ' &".csv" ' により生成し，copy文も全テーブルの分だけ一括で作るだけだ。
最終的に
delete from TABLENAME; copy TABLENAME from 'D:/temp/TABLENAME.csv' with csv;
という行が全てのテーブルについて並べばよい。これをコマンドプロンプトに貼り付ける事によって，データのインポートが終わる。
なおもしデータ件数が多いために，1テーブルでcsvファイルが2つ以上に分かれてしまう場合は，その事も考慮してcopy文を生成する必要がある。
なお，シート上のデータをODBC経由で，シートから直接DBにインポートするという方法も一応あるが，こちらはえらく時間がかかる。
素早く済ませるためには，このようにDB側がインポート手段として用意してくれている方法を使うのがよいだろう。
まとめ
- Excelでデータを作るべし （直感的な操作性）
- できれば Excelは 2007 を使うべし （100万行）
- 作業前に，自動保存をオフにすべし （余計な処理をカット）
- １つのエクセルファイルに１つのテーブルだけを入れるべし （余計な時間をカット）
- データの周期性・階段性を理解すべし （並びを作る関数）
- マクロでフィルすべし （速さ，カスタマイズ性）
- csvにエクスポートすべし （速さ，汎用性）
下記にテストデータを作るのに役立つ関数などをまとめた関連記事がある。
WebサービスとExcelを併用してCSV形式のテストデータを作る
http://mitc.xrea.jp/diary/072
- 日付を作る場合の date( 2008, 12, 31 )
- 日付形式を加工する場合の text( "2008/12/31", "yyyy-mm-dd" ) & " 00:00:00"
などをよく使う。
関連する記事：
