SQL Serverに1億件のテストデータを高速に作成する

HogeHoge

SQL Serverに1億件を超える大量のテストデータを作成する必要がありできるだけ高速な方法を調べてみた。

ストプロを試してみる

まずはストプロを作成してどの程度になる確認してみる。単純にループを回してINSERTを1件づつ行う方法でまずは100万件で試してみる。

ALTER PROCEDURE dbo.CreateData
AS
	SET NOCOUNT ON 
	DECLARE @RowCount INT
	SET @RowCount = 0
	WHILE @RowCount < 1000000
	BEGIN
		INSERT INTO TestTable
		VALUES
			(@RowCount, 'DATA' + right('0000000000' + convert(varchar, @RowCount), 10))
		SET @RowCount = @RowCount + 1
	END
	RETURN

計測すると7分5秒。1億件作るためには700分以上かかってしまう。
今回ノートPCでの計測なのでサーバーだともう少し高速になると思うが、別の方法を考える必要がありそう。

バルクコピーを試してみる

SQL文ではなくSQLServerのバルクコピーを使ってみる。C#からはSqlBulkCopyクラスを使って利用できる。SqlBulkCopyを使って高速に大量のテストデータを作成を参考に実装してみた。

class Program
{
    static void Main(string[] args)
    {
        Stopwatch sw = Stopwatch.StartNew();
        SqlBulkCopy bulk = new SqlBulkCopy(Settings.Default.MillionDataConnectionString);
        bulk.DestinationTableName = "TestTable";
        bulk.WriteToServer(new TestTableDataReader());
        Console.WriteLine(sw.Elapsed);
    }
}

class TestTableDataReader : IDataReader
{
    private int rowCount = 0;
    public int FieldCount { get { return 2; }  }
    public bool Read()
    {
        return this.rowCount++ < 1000000;
    }
    public object GetValue(int i)
    {
        if (i == 0) return this.rowCount;
        return string.Format("DATA{0:0000000000}", rowCount);
    }
    −−−以下略−−−

計測すると約11秒、先ほどの約50分の1。1億件作るためには18分で実用になりそう。

大きなSELECTデータを作ってINSERTする

もっと良い方法はないか探していると、T-SQL – 1 million records in 1 second（1秒で100万レコード）という記事を見つけた。このSQL文を実行すると指定した件数分のレコードが１から順番に作成される。この作成されたレコードを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 < 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 TestTable 
  Select n, 'DATA' + right('0000000000' + convert(varchar, n), 10)
    from Nums  Where n<=@p_NumberOfRows
OPTION (MaxRecursion 0);

計測すると約5秒。1億件作るためには8分ちょっとの予想、これはかなり高速なのかも。
実際に1億件作成してみようと思いますが、ノートPCのDISK容量があまりないので1000万件で試したところほぼ予想通りの57秒だった。