SQL Serverの負荷テストで大量のデータを作成しないといけなくなり、できるだけ高速で作成する方法を探してみました。
まず、単純にループを使用して１００万件のデータを作成するSQLを作成してみました。
・ループを使用して１００万件のデータを作成するSQL
SET NOCOUNT ON
DECLARE @RowCount INT
SET @RowCount = 0
WHILE @RowCount < 1000000
BEGIN
INSERT INTO T_Test (Data1,
Data2,
Data3,
Data4,
Data5)
VALUES
(@RowCount,
'DATA' + right('0000000000' + convert(varchar, @RowCount), 10),
'0',
'0',
'0')
SET @RowCount = @RowCount + 1
END
上記SQLを実行した結果。
ループ処理で１００万件データを作成した結果、処理時間は３分６秒でした。
もし１０００万件のデータを作成しようとした場合、単純計算で３０分かかります。
テーブル数が多い場合やデータ件数が増えてしまうと、実用的には厳しい感じです。
他にいい方法がないかと調べた結果、再帰クエリを使用した場合、飛躍的に高速でデータが作成できました。
・再帰クエリを使用し、大きなSELECTデータをINSERTするSQL
DECLARE @p_NumberOfRows Bigint
SELECT @p_NumberOfRows=1000000;
WITH Base AS
(
SELECT 1 AS n
UNION ALL
SELECT n+1 FROM Base WHERE n < p_NumberOfRows
),
Nums AS
(
SELECT Row_Number() OVER(ORDER BY n) AS n FROM Base
)
INSERT INTO T_Test
SELECT n,
'DATA' + right('0000000000' + convert(varchar, n), 10),
'0',
'0',
'0'
FROM Nums WHERE n <= @p_NumberOfRows
OPTION (MaxRecursion 0);
上記SQLを実行した結果。
再帰クエリを使用して１００万件のデータを作成した結果、処理時間は9秒でした。
この方法だと、１０００万件のデータでも90秒で作成できます。
１億件でも約15分程度で作成できることになり、実用的なレベルで使用できると思います。
上記SQLでも十分かなと思っていたのですが、もう一工夫することでもっと早く作成できることができました。
・再帰クエリを修正したSQL
DECLARE @p_NumberOfRows Bigint
SELECT @p_NumberOfRows=1000000;
WITH Base AS
(
SELECT 1 AS n
UNION ALL
SELECT n+1 FROM Base WHERE n < CEILING(SQRT(@p_NumberOfRows))
),
Expand AS
(
SELECT 1 AS C FROM Base AS B1, Base AS B2
),
Nums AS
(
SELECT Row_Number() OVER(ORDER BY C) AS n FROM Expand
)
INSERT INTO T_Test
SELECT n,
'DATA' + right('0000000000' + convert(varchar, n), 10),
'0',
'0',
'0'
FROM Nums WHERE n <= @p_NumberOfRows
OPTION (MaxRecursion 0);
上記SQLを実行した結果。
先ほどの再帰クエリを修正して実行したところ、１００万件のデータを作成する処理時間は４秒でした。
１０００万件でも約４０秒で作成できることになり、ループを使用して作成するより圧倒的に高速で
データが作成できるようになりました。
ストアドプロシージャにしてテーブル名や作成する件数を変数化してやればもっと汎用的に
使用できるようにもなり、データ作成も高速に行えると思います。
