はじめに
最近、仕事で1億レコードのテストデータを用意し、性能試験をしました。テストデータを大量作成する記事はいくつかありましたが、スピーディで柔軟性のある下記記事のやり方を採用しました。
今回は初めての挑戦だったため、せっかくなので自分用の備忘録も兼ねて記事を書きました。（二番煎じ？）
想定読者
SQLの知識として下記を知ってる前提として執筆しました。
- CROSS JOIN
- 一時テーブル
- INSERT SELECT
- DECLAREとWHILE
テーブル
テーブル構成は例として下記画像とします。
設定としては、プレイヤー1万人が毎日1人あたり100体討伐するゲームのDBとして、「プレイヤー」テーブルと討伐記録をする「戦績」テーブルを構築しました。
ただ、100日間で**「戦績」テーブルには1億件のレコードが作成**されてしまいます。このテーブルを使用するクエリの性能測定のために、「プレイヤー」テーブルと「戦績」テーブルのテストデータを作成していきます。
データ作成までの流れ
データ作成は主に下記の流れで作成していきます。
- 一時テーブルに
CROSS JOINを利用して0～100万のシーケンスを作成 - シーケンスをもとに1万人のプレイヤーを作成
- シーケンスをもとに1日分のデータ100万レコードを作成
- whileループで100日間分コピー
データ作成
1. 100万シーケンス作成
まず、0～999,999のシーケンスを作成します。
下記のDDLで自動インクリメントする一時テーブルを作成します。
CREATE TABLE #BASE(
SEQ INTEGER IDENTITY(0, 1) PRIMARY KEY,
VAL INTEGER
)
ここに、CROSS JOINで指数的にコピーをする素となる10レコードをインサートします。
INSERT INTO #BASE(VAL) VALUES
(0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
CROSS JOINを6回行い、10^6（=100万）レコードをインサートします。
（オフセットとして+9をかけていますが、深い意味はありません。）
INSERT INTO #BASE(VAL)
SELECT
(ROW_NUMBER() OVER (ORDER BY B1.SEQ)) + 9 AS VAL
FROM
#BASE AS B1,
#BASE AS B2,
#BASE AS B3,
#BASE AS B4,
#BASE AS B5,
#BASE AS B6
;
後は必要なデータ数になるように、データを削除します。
DELETE FROM #BASE WHERE SEQ >= 1000000;
上記を全て実行すると、一時テーブル#BASEは下記のような状態となっています。
| SEQ | VAL |
|---|---|
| 0 | 0 |
| 1 | 1 |
| 2 | 2 |
| ~中略~ | ~中略~ |
| 999998 | 999998 |
| 999999 | 999999 |
2. 1万人プレイヤーを作成
シーケンスを100万まで作成しました。WHEREで絞って、インサートすることで1万レコードの登録を簡単にできます。
INSERT INTO PLAYER(ID, NAME)
SELECT
CONCAT('test_id_', FORMAT(SEQ, '00000')) AS ID,
CONCAT('player_', FORMAT(SEQ, '00000')) AS NAME
FROM
#BASE
WHERE
SEQ < 10000
;
上記を実行すると「プレイヤー（PLAYER）」テーブルは下記のような状態になります。
| ID | NAME |
|---|---|
| test_id_00000 | player_00000 |
| test_id_00001 | player_00001 |
| test_id_00002 | player_00002 |
| ~中略~ | ~中略~ |
| test_id_09998 | player_09998 |
| test_id_09999 | player_09999 |
3. 1日分の戦績データ100万レコード作成
一時テーブルを作って、1日分のデータを作成します。
CREATE TABLE #BATTLE_BASE (
SEQ BIGINT IDENTITY(0, 1) PRIMARY KEY,
PLAYER_ID VARCHAR(20) not null,
ENEMY_ID VARCHAR(20) not null,
DT DATETIME not null
)
下記のようにレコードを一時テーブルにインサートします。
INSERT INTO #BATTLE_BASE(PLAYER_ID, ENEMY_ID, DT)
SELECT
P.ID AS PLAYER_ID,
'enemy_0000' AS ENEMY_ID,
CONVERT(DATETIME, '2021/01/01 12:00:00') AS DT -- 仮決めで1月1日
FROM
#BASE AS B
INNER JOIN
PLAYER AS P
ON
P.ID = CONCAT('test_id_', FORMAT(B.SEQ/100, '00000')) -- 100で除算して切捨てでプレイヤーIDを特定
;
今回の例では『結合しなくてもプレイヤーID分かるじゃん』と思ってしまいますが、実際のDBにはもっと複雑にカラムやリレーションがあり、結合して取得した方が、いろいろ都合が良くなります。
4. Whileループでデータをコピー
一時テーブルに作成した100万件のレコードを対象のテーブルにループ文でコピーしていきます。
ただし、「討伐時刻」の箇所だけはループのたびにずらしていく必要があるため、DATEADD関数で日数を加算しています。
なお、単純にインサートのみだと寂しいので、RAISERROR関数で進捗度合いを逐次出力しながらループさせます。
DECLARE @INDEX int = 0;
DECLARE @LENGTH int = 100;
WHILE @INDEX < @LENGTH
BEGIN
/* 進捗出力 */
DECLARE @MSG VARCHAR(200) = CONCAT(FORMAT(GETDATE(), '[yyyy/MM/dd HH:mm:ss]'), @INDEX + 1, '/', @LENGTH);
RAISERROR(@MSG, 0, 0) WITH NOWAIT;
/* インサート */
INSERT INTO BATTLE_RECORD(PLAYER_ID, ENEMY_ID, DT)
SELECT
PLAYER_ID,
ENEMY_ID,
DATEADD(DAY, @INDEX, DT) AS DT -- カウンタ変数で日数を加算
FROM
#BATTLE_BASE
;
SET @INDEX = @INDEX + 1;
END
実行中は下記のようにメッセージとして出力されます。
[2021/11/24 17:18:16]1/100
(1000000 rows affected)
[2021/11/24 17:18:17]2/100
(1000000 rows affected)
～中略～
[2021/11/24 17:20:31]99/100
(1000000 rows affected)
[2021/11/24 17:20:33]100/100
(1000000 rows affected)
Completion time: 2021-11-24T17:20:34.4511239+09:00
結果
上記手順を踏むことで、無事に1億件のデータがインサートされました。
もちろん、中身も想定されたレコードになっています。
おわりに
今回の例では「エネミーID」を固定にしていましたが、剰余算とCASE句を活用し、シーケンスから計算すると細かく指定することが出来ます。
なお、SQLでテストデータ作成をするとDBのメモリがガンガン使われます。ローカル環境で試す場合、デフォルト設定だとメモリ上限設定が無いに等しいため、設定することをお勧めします。
ちなみに性能試験の結果は最悪でした。インデックスはちゃんと張ろうね。