開発思考実験日記

2011-10-15 (^_^)

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

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文を実行すると指定した件数分のレコードが1から順番に作成される。この作成されたレコードを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秒だった。

wedoitwedoit 2013/12/05 03:27 システムを開発する過程で時間のかかる工程として単体・結合テスト作業があります。その中でテストデータを作成するにはそれ相応の時間が掛かってしまいます。すべて「ほげほげ」のようなデータを作るなら、ゴミデータも多いし、ミス発生しやすいでしょう。
綺麗な擬似テストデータ作成できるExcelDBTool/ExcelDevToolをお勧めします。これを使うと、Excelシートに速やかに60種類以上の擬似テストデータが作成できます。
http://www.superdbtool.com
http://www.vector.co.jp/soft/winnt/business/se475869.html
http://www.vector.co.jp/soft/winnt/business/se475115.html

ダミーデータ種類:
郵便番号, 都道府県, 企業名, 市区,町村,最寄駅,最寄駅ふりがな,路線,銀行コード, 銀行名, 銀行カタカナ, 支店コード, 支店名, 支店カタカナ,E-Mail, URL, 携帯, 苗字のみ, 氏名, 住所, 住所ふりがな, 性別, 年齢, 血液型, 固定電話, 婚姻, ふりがな, 大学名,身長,体重固定値,国籍,国籍英語,国籍英語略,クレジットカード会社,クレジットカード番号,有効期限,IPアドレス,業種大分類,業種大分類名称,業種中分類,業種中分類名称,業種小分類,業種小分類名称,職種大分類,職種大分類名称,職種中分類,職種中分類名称,職種小分類,職種小分類名称,数値連番,整数・小数点数,英数混在,文字列+連番,文字列+全角連番全角漢字,ひらがな,全角カタカナ,半角カタカナ,全角英数,全角数値,全角英字,全半混在日付,時間,タイムスタンプ,特殊文字

はてなユーザーのみコメントできます。はてなへログインもしくは新規登録をおこなってください。