SQLServerで大量のテストデータ1億レコードを作った

## はじめに
最近、仕事で1億レコードのテストデータを用意し、性能試験をしました。テストデータを大量作成する記事はいくつかありましたが、スピーディで柔軟性のある下記記事のやり方を採用しました。

- [SQLで大量のテストデータ作成 - Qiita](https://qiita.com/cobot00/items/8d59e0734314a88d74c7)

今回は初めての挑戦だったため、せっかくなので自分用の備忘録も兼ねて記事を書きました。（~~二番煎じ？~~）

## 想定読者
SQLの知識として下記を知ってる前提として執筆しました。

- CROSS JOIN
- 一時テーブル
- INSERT SELECT
- DECLAREとWHILE

## テーブル
テーブル構成は例として下記画像とします。

![ER.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311823/10c4085e-06c9-89f8-a5cc-517a70b4aeb6.png)

設定としては、プレイヤー1万人が毎日1人あたり100体討伐するゲームのDBとして、「プレイヤー」テーブルと討伐記録をする「戦績」テーブルを構築しました。
ただ、100日間で**「戦績」テーブルには1億件のレコードが作成**されてしまいます。このテーブルを使用するクエリの性能測定のために、「プレイヤー」テーブルと「戦績」テーブルのテストデータを作成していきます。

## データ作成までの流れ
データ作成は主に下記の流れで作成していきます。

1. 一時テーブルに`CROSS JOIN`を利用して0～100万のシーケンスを作成
1. シーケンスをもとに1万人のプレイヤーを作成
1. シーケンスをもとに1日分のデータ100万レコードを作成
1. whileループで100日間分コピー

## データ作成
### 1. 100万シーケンス作成
まず、0～999,999のシーケンスを作成します。
下記のDDLで自動インクリメントする一時テーブルを作成します。

```sql:一時テーブル
CREATE TABLE #BASE(
	SEQ INTEGER IDENTITY(0, 1) PRIMARY KEY,
	VAL INTEGER
)
```
ここに、`CROSS JOIN`で指数的にコピーをする素となる10レコードをインサートします。

```sql:10レコード登録
INSERT INTO #BASE(VAL) VALUES
	(0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
```

`CROSS JOIN`を6回行い、10^6（=100万）レコードをインサートします。
（オフセットとして`+9`をかけていますが、深い意味はありません。）

```sql:100万シーケンスレコードインサート
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
;
```

後は必要なデータ数になるように、データを削除します。

```sql:不要レコード削除
DELETE FROM #BASE WHERE SEQ >= 1000000;
```

上記を全て実行すると、一時テーブル`#BASE`は下記のような状態となっています。

|SEQ|VAL|
|-:|-:|
|0|0|
|1|1|
|2|2|
|~中略~|~中略~|
|999998|999998|
|999999|999999|

### 2. 1万人プレイヤーを作成
シーケンスを100万まで作成しました。`WHERE`で絞って、インサートすることで1万レコードの登録を簡単にできます。

```sql:プレイヤー作成
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日分のデータを作成します。

```sql:コピー元の一時テーブル
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
)
```

下記のようにレコードを一時テーブルにインサートします。

```sql:コピー元のレコード
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`関数で進捗度合いを逐次出力しながらループさせます。

```sql:ループ処理
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
```

実行中は下記のようにメッセージとして出力されます。

```:Messages
[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億件のデータがインサートされました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311823/4f1bdee6-42ce-a812-97ab-093b0b74057b.png)

もちろん、中身も想定されたレコードになっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311823/923223a8-e754-9114-1f5f-37ad521ee271.png)


## おわりに
今回の例では「エネミーID」を固定にしていましたが、剰余算と`CASE`句を活用し、シーケンスから計算すると細かく指定することが出来ます。

なお、SQLでテストデータ作成をすると**DBのメモリがガンガン使われます**。ローカル環境で試す場合、デフォルト設定だとメモリ上限設定が無いに等しいため、設定することをお勧めします。

~~ちなみに性能試験の結果は最悪でした。インデックスはちゃんと張ろうね。~~

## 参考
- [SQLで大量のテストデータ作成 - Qiita](https://qiita.com/cobot00/items/8d59e0734314a88d74c7)