none
連続したInsertの実行速度が遅い。SQLServer 2008 R2 にてsqlcmdを使用。 RRS feed

  • 質問

  • SQLServer初心者です。よろしくお願いします。

    現在、SQLServerの性能検証中なのですが、
    CPU:Intel Xeon 3.4GHz
    実装メモリ:4.00GB(2.99GB使用可能)
    OS:Windows7 SP1
    HDD:250GB
    の新品PC上にSQLServer 2008 R2をインストールし、

    CREATE TABLE dummy( 
    dummy_id int IDENTITY(1,1) PRIMARY KEY  ,
    x_pos int,
    y_pos int, 
    dummy_text nvarchar(50),
    update_date datetime2,
    update_pc nvarchar(10));

    といった簡単なテーブルを作成し、テキストエディタで30万件のInsert文を作成し、
    sqlcmdで流し込みました。

    結果、「リソース プール 'internal' のシステム メモリが不足しています。」等のメッセージが表示されたため、

    insert文を100件ずつごとに

    begin transaction;
    (insert文 100件)
    commit;
    go

    で区切り、実行しました。
    無事テーブルにはデータを流し込めたのですが、速度がどうしても気になります。

    計測したところ、1分間に3500件程度しか流し込めていないのです。
    タスクマネージャでCPU負荷を調べてみても 1%~3%程度しか使用していません。
    メモリの使用率は最初900MBから徐々に増加していき、最終的には2.5GBあたりで落ち着きます。

     高速化するには何か設定が必要でしょうか?
    ご助言いただければ幸いです。よろしくお願いします。

    2012年1月12日 2:56

回答

  • 元の質問では、

    • INSERT文
    • sqlcmd
    • データベース全体

    いずれの性能検証だったのかわかりませんでしたが、先の返信で手段は問わない=データベース全体の性能検証と理解しました。

    であれば一時テーブルに一旦、全行INSERTしてみてはどうでしょうか? geography型など変換が必要なものに関しては、いったんnvarcharなどを使います。これであればCSVインポートなどもできるでしょう。その上で

    INSERT INTO dummy ( dummy_lonlat )
    SELECT geography::STGeomFromText(dummy_lonlat_text, 4326)
    FROM #temp_table;
    
    のように変換しながら目的のテーブルに格納できるかと。この場合、INSERT1行で完結するのでトランザクションも不要になります。

    • 回答の候補に設定 aviator__ 2012年1月16日 7:32
    • 回答としてマーク 山本春海 2012年1月17日 9:25
    2012年1月12日 23:27
  • 最近別の質問でもありましたが、

    INSERT INTO dummy
    (dummy_lonlat)
    VALUES
     ( geography::STGeomFromText('POINT(134.23 35.67)', 4326) )
    ,( geography::STGeomFromText('POINT(134.23 35.67)', 4326) )
    ,( geography::STGeomFromText('POINT(134.23 35.67)', 4326) )
    ,・・・;
    

    みたいな感じで一度試してみて下さい。

    • 回答としてマーク takanobu 2012年1月16日 5:50
    2012年1月12日 9:30
  • 確かに・・・

    geography 型って時点で BULK INSERT は無しって判断してました。

     

    ①CSVかtab区切りのファイルを用意する。

    ---ここから sqlcmd

    ②一時テーブルを作成する。

    ③BULK INSERT で、一時テーブルにファイルを一括で登録する。 ※この時点ではgeography型ではなくnvarchar型

    ④一時テーブルの内容を INSERT INTO ・・・ SELECT で型変換しつつ登録先テーブルにINSERTする。

    ---ここまで sqlcmd

     

    ちなみに②~④は

    CREATE TABLE #temp_table(dummy_lonlat_text NVARCHAR(4000));
    
    BULK INSERT #temp_table
    FROM 'C:\temp\import.csv'
    WITH ( DATAFILETYPE = 'char'
    	 , FIELDTERMINATOR = ','
    	 , ROWTERMINATOR   = '\n');
    
    INSERT INTO dummy ( dummy_lonlat )
    SELECT geography::STGeomFromText(dummy_lonlat_text, 4326)
    FROM #temp_table;
    
    

    こんな感じですかね。

    • 回答としてマーク takanobu 2012年1月16日 5:50
    2012年1月13日 1:01

すべての返信

  • 「sqlcmd を用いる」というのは前提条件でしょうか?

    それとも方法は何でも良く、大量データのインポートがしたいのでしょうか?

     

    それによって回答は変わると思います。

    2012年1月12日 6:21
  • 返信ありがとうございます。

    大量のデータをインポートできれば問題ありません。ただ、最終的に入れようとしているデータ型が少し特殊で、
    空間データを入れたいと思っています。geography型です。

    INSERT INTO dummy(dummy_lonlat) VALUES ( geography::STGeomFromText('POINT(134.23 35.67)', 4326) );

    こんな形のデータです。C#での開発を予定していて、
    その前調査として手っ取り早くsqlcmdで検証してみましたが、
    sqlcmd特有の問題ということであれば、ちょっと安心できそうです。
    また、サーバ担当者からHDDがRAID1で組んでいるという情報もあり、
    それが原因かなと思ったりしていたところでした。

    こちらでも、他の方法(実際にC#で作ってみる等)でも試してみようと思います。
    引き続き、何か思い当たることがありましたら、ご助言よろしくお願いします。

    2012年1月12日 8:44
  • 最近別の質問でもありましたが、

    INSERT INTO dummy
    (dummy_lonlat)
    VALUES
     ( geography::STGeomFromText('POINT(134.23 35.67)', 4326) )
    ,( geography::STGeomFromText('POINT(134.23 35.67)', 4326) )
    ,( geography::STGeomFromText('POINT(134.23 35.67)', 4326) )
    ,・・・;
    

    みたいな感じで一度試してみて下さい。

    • 回答としてマーク takanobu 2012年1月16日 5:50
    2012年1月12日 9:30
  • 元の質問では、

    • INSERT文
    • sqlcmd
    • データベース全体

    いずれの性能検証だったのかわかりませんでしたが、先の返信で手段は問わない=データベース全体の性能検証と理解しました。

    であれば一時テーブルに一旦、全行INSERTしてみてはどうでしょうか? geography型など変換が必要なものに関しては、いったんnvarcharなどを使います。これであればCSVインポートなどもできるでしょう。その上で

    INSERT INTO dummy ( dummy_lonlat )
    SELECT geography::STGeomFromText(dummy_lonlat_text, 4326)
    FROM #temp_table;
    
    のように変換しながら目的のテーブルに格納できるかと。この場合、INSERT1行で完結するのでトランザクションも不要になります。

    • 回答の候補に設定 aviator__ 2012年1月16日 7:32
    • 回答としてマーク 山本春海 2012年1月17日 9:25
    2012年1月12日 23:27
  • 確かに・・・

    geography 型って時点で BULK INSERT は無しって判断してました。

     

    ①CSVかtab区切りのファイルを用意する。

    ---ここから sqlcmd

    ②一時テーブルを作成する。

    ③BULK INSERT で、一時テーブルにファイルを一括で登録する。 ※この時点ではgeography型ではなくnvarchar型

    ④一時テーブルの内容を INSERT INTO ・・・ SELECT で型変換しつつ登録先テーブルにINSERTする。

    ---ここまで sqlcmd

     

    ちなみに②~④は

    CREATE TABLE #temp_table(dummy_lonlat_text NVARCHAR(4000));
    
    BULK INSERT #temp_table
    FROM 'C:\temp\import.csv'
    WITH ( DATAFILETYPE = 'char'
    	 , FIELDTERMINATOR = ','
    	 , ROWTERMINATOR   = '\n');
    
    INSERT INTO dummy ( dummy_lonlat )
    SELECT geography::STGeomFromText(dummy_lonlat_text, 4326)
    FROM #temp_table;
    
    

    こんな感じですかね。

    • 回答としてマーク takanobu 2012年1月16日 5:50
    2012年1月13日 1:01
  • 最終的に C# で開発するということであるならば、SqlBulkCopy クラスを使って geography 列にも Bulk Insert できます。
    過去に同じことをやったことがありますが、その時は IDataReader をカスタマイズして、geography 列を GetValue するときに SqlGeometry クラスを使いました。

    あと、遅くなる要因として、geography 列にインデックスを付けてはいないでしょうか?
    SqlBulkCopy を使う場合でも、インデックスがあるとないでは、速度がガクッと変わってしまいます。
    インデックスがある場合で、もし可能であれば、Insert をする前に一旦インデックスを消しておいて、Insert が完了してからインデックスを再作成するという手もあります。
    2012年1月13日 2:06
  • ありがとうございます。この方法で問題なくいけました。

    1時間ほどかかっていたSQLが5分程度で済みました。
    途中でcommitする必要もなく、1000件に1回 INSERT文を書き直す修正を行いました。

    やっぱりsqlcmdの癖のようなものでしょうかね。
    こういう書き方をしたことがなくて、目からウロコでした。ありがとうございます。

     

    2012年1月16日 5:37
  • 佐祐理さん、aviator_さん、ありがとうございます。

    ご紹介いただいた方法でも、高速に登録することができました。
    sqlcmdで行うと以下のようなエラーメッセージが表示されましたが、
    SQL Server Management Studioでは問題なく動作し、
    30秒ほどで処理が完了しました。

    ▽エラーメッセージ

    メッセージ 1934、レベル 16、状態 1、・・・
    INSERT が失敗しました。次の SET オプションには不適切な設定 'QUOTED_IDENTIFIER'
    があります。SET オプションが、インデックス付きビュー、計算列のインデックス、フィ
    ルター選択されたインデックス、クエリ通知、XML データ型のメソッド、空間インデック
    スの操作 で使用するのに適切であることを確認してください。

    △ここまで

    たぶん、オプションの設定を変えないとだめなようですね。
    BULK INSERT、高速ですね。使いこなせるようにがんばります。

    2012年1月16日 5:46
  • ありがとうございます。

    C#での開発時に参考にさせていただきます。
    sqlcmdの場合でも、文字列のINSERTよりもgeography列のINSERTのほうが
    はるかに時間がかかったので、インデックスには注意しておかないといけませんね。

     

    2012年1月16日 5:49
  • 余談かつ自分で書いておいてなんですが、個人的には私の書いたサンプルは好きではありません。

    FIELDTERMINATOR を指定している為、もしもカラム数がおかしい時等にそれを拾えません・・・

     

    BULK INSERT をアプリで使う時は、下記の手順を踏むのが良いかと思います。

    ①一時テーブルはVARCHAR(MAX) の1カラムのみのテーブルを用意する。

    ②FIELDTERMINATOR を指定せずに1レコード毎まとめて一時テーブルに BULK INSERT する。

    ③一時テーブルに登録されたデータをループさせて、1行毎にカラム数や内容に問題が無いかエラーチェックする。

    ④エラーが無かった行は登録先にINSERTし、エラーがあった行はログを出力する。

     

    こうする事で、レイアウト異常にも対応するのが良いかと。

    ちなみにカンマ区切りの文字列取得 FUNCTION のサンプルを追記します。

    ※もっといい方法あるかもしれません。

    CREATE FUNCTION GET_CSV_VALUE
    (
    	 @i_value	VARCHAR(MAX)
    	,@i_index	INT
    )
    RETURNS VARCHAR(MAX)
    AS
    BEGIN	
    	IF @i_index = 0
    	BEGIN
    		IF CHARINDEX(',', @i_value) = 0
    		BEGIN
    			RETURN @i_value;
    		END
    		ELSE
    		BEGIN
    			RETURN SUBSTRING(@i_value, 1, CHARINDEX(',', @i_value) - 1);
    		END
    	END
    	DECLARE @c_charCounter INT = 0;
    	DECLARE @c_commaCounter INT = 0;
    	WHILE @c_charCounter < LEN(@i_value)
    	BEGIN
    		SET @c_charCounter = @c_charCounter + 1;
    		DECLARE @c_char VARCHAR(1) = SUBSTRING(@i_value, @c_charCounter, 1);
    		IF @c_char = ','
    		BEGIN
    			SET @c_commaCounter = @c_commaCounter + 1;
    			IF @c_commaCounter = @i_index
    			BEGIN
    				DECLARE @c_value VARCHAR(MAX) = SUBSTRING(@i_value, @c_charCounter + 1, LEN(@i_value));
    				IF CHARINDEX(',', @c_value) = 0
    				BEGIN
    					RETURN @c_value;
    				END
    				ELSE
    				BEGIN
    					RETURN SUBSTRING(@c_value, 1, CHARINDEX(',', @c_value) - 1);
    				END
    			END
    		END
    	END
    	RETURN NULL;
    END
    
    

    こうやって、SELECT dbo.GET_CSV_VALUE('A,BB,CCC,DDDD,EEEEE', 3) とかってすれば色々と出来るかと・・・

     

    2012年1月16日 6:59
  • sqlcmdの癖というわけではなく、SQLの癖というか…。INSERT文を何回もやるより、1度に多くのINSERTをこなした方が効率がいい、というものです。はっきり言ってSQLにも限られない一般論かと。

    #「最近別の質問でもありました」というのはたぶんに私のコメントをさしているかなと。にもかかわらずaviator__さんのコメントばかりに回答としてマークされるのはちょっとしょんぼり。

    2012年1月16日 7:23
  • ・・・何かスイマセン・・・

    ちなみに、さっき書いたGET_CSV_VALUE は ” 囲いを意識してませんのであしからず。

    2012年1月16日 7:31
  • みなさん、ありがとうございます。

    そして佐祐理さん、気分を害してしまってすみません。。。
    「参考になった投稿」としては、すぐさま投票させていただいておりました。

    aviator__さんのサンプルは、今後しばらく家宝にさせていただきます。

    とても活発なフォーラムですね。
    今後も活用して、私もどなたかのお役に立ちたいと思います! 

     

    2012年1月17日 9:27