業務でSQL Serverへ大量のデータを登録することがあり、
要求される処理速度を満たすやり方はないかと調べていると
バルクインサート（SqlBulkCopy）という機能が使えそうなので
調べてみました。
SqlBulkCopyクラス
MSDNで調べると以下のように説明がありました。
「SQL Server のテーブルに対し、他のソースからのデータを効率よく
一括読み込みできます。」
いつもながらこれだけではよくわかりません。
で、サンプルコードを確認すると、DataTableへいれたデータをクラスへ渡してやれば
それだけでデータが登録できるようです。
これを見る限り簡単にできそうかな・・・ってことで早速作ってみます。
BulkCopy本体
public bool BulkInsertProc(string targetTable, DataTable wkDt)
{
bool ret = true;
using (SqlConnection dbConnection = new SqlConnection())
{
if (openConnection(dbConnection))
{
try
{
using (SqlBulkCopy bc = new SqlBulkCopy(dbConnection))
{
// タイムアウト時間設定
bc.BulkCopyTimeout = 600;
// バルクコピーを行うテーブル名
bc.DestinationTableName = "[" + targetTable + "]";
// バルクコピー実行
bc.WriteToServer(wkDt);
}
}
catch (System.Exception ex)
{
// Error Message Set
m_ErrorMessage = ex.Message;
ret = false;
throw;
}
}
return ret;
}
}
データ登録を行うテーブルを指定しデータテーブルを渡すだけの
シンプルなものです。
"WriteToServer"が実際にデータを登録するメソッドのようです。
・データの作成、BulkCopy呼び出し
private bool registDataBulkCopy()
{
bool regist = true;
string msg = "";
// データテーブル作成クラス
SetDataTable sdt = new SetDataTable();
// DB接続
using (DBAccess db = new DBAccess(mSystemSetting.DbInfo.DbSrc,
mSystemSetting.DbInfo.DbName,
mSystemSetting.DbInfo.DbUser,
mSystemSetting.DbInfo.DbPass))
{
// データがある場合のみ処理を行う
if (mZds.Count > 0)
{
// datatableのベースを作成
using (DataTable dt = mDtZds.Clone())
{
// datatable作成
if (sdt.SetdeliveryStage(mZds, dt))
{
// DB登録実行
if (db.BulkInsertProcine.TABLE_DELIVERY_STAGE, dt))
{
regist = true;
}
else
{
msg = "データ登録失敗 [" + db.ErrorMessage + "]";
writeLog(msg, 1, "registDataBulkCopy");
regist = false;
}
}
else
{
msg = "データ登録失敗 [" + sdt.ErrorMessage + "]";
writeLog(msg, 1, "registDataBulkCopy");
regist = false;
}
}
}
return regist;
}
処理の流れは、DB接続を行いデータテーブルへ登録したいデータを
追加しそれをBulkCopyの関数へ渡します。
（データテーブルへのデータ追加は単純に値の代入だけなので特に
載せていません）
データテーブルは登録するSQL Serverの実テーブルと同じ内容の
ものでないとうまく登録できません。
これを作るのに結構手間がかかったので簡単にできるように
適当なSELECT文を実行し、結果のReaderからテーブルの情報を
取得しデータテーブルを作成しています。
以下のような感じでやってます。
// データテーブル作成
public DataTable GetDataTable(string _tableName)
{
DataTable wkDt = new DataTable();
string msg = "";
try
{
// データテーブル作成のためのSQL
string _sql = "SELECT * FROM ";
_sql += _tableName;
_sql += " WHERE [項目１] = 'ZZZZ'";
// データテーブル作成
wkDt = createDataTable(_sql);
wkDt.TableName = _tableName;
if (wkDt == null)
{
// Error
msg = "GetDataTable エラー:" + ErrMessage + " テーブル名:" + _tableName;
m_ErrMessage = msg;
}
}
catch
{
// Error
msg = "GetDataTable エラー:" + ErrMessage + " テーブル名:" + _tableName;
m_ErrMessage = msg;
}
return wkDt;
}
// SQL文を実行し結果をDataTableで戻す関数
public DataTable createDataTable(string p_query)
{
DataTable wkDt = new DataTable();
try
{
using (SqlConnection dbConnection = new SqlConnection())
{
using (SqlCommand command = new SqlCommand())
{
if (openConnection(dbConnection))
{
//タイムアウトの設定
command.CommandTimeout = 600;
command.CommandText = p_query.ToString();
command.Connection = dbConnection;
using (SqlDataReader sqlDr = command.ExecuteReader())
{
//SQL発行結果の編集
//DataTableの定義をSqlDataReaderから作る
GetSchemaDataTable(sqlDr, wkDt);
}
}
}
}
}
catch (System.Exception ex)
{
// Error Message Set
m_ErrorMessage = ex.Message;
wkDt = null;
}
finally
{ }
return wkDt;
}
//DataTableの定義をSqlDataReaderから作る関数
private void GetSchemaDataTable(SqlDataReader sqlDr, DataTable wkDt)
{
DataTable schemaDt = sqlDr.GetSchemaTable();
foreach (DataRow schemaDr in schemaDt.Rows)
{
// カラム名
string columnName = schemaDr["ColumnName"].ToString().Trim();
// データタイプ
string dataType = schemaDr["DataType"].ToString().Trim();
DataColumn dc = new DataColumn();
dc.ColumnName = columnName;
dc.DataType = System.Type.GetType(dataType);
wkDt.Columns.Add(dc);
}
}
最初に一回だけ作成したら後は使用するときにCloneを作れば
繰り返し利用できて便利です。
実行結果
元々は普通に登録するデータをループで回して1件ずつINSERT文を
実行して登録していました。
このやり方で5000件登録すると大体1分30秒くらいかかりました。
（クライアントもサーバーも仮想環境で確認）
30秒くらいで次の登録すべきデータが出力されると処理が追い付かなく
データがたまる一方でした。
今回調べたSqlBulkCopyを使用すると同じ5000件でも1秒かからない
うちに登録が完了します。
データテーブルを作成する余分な処理が増えますが、それでもかなりの
短縮ができました。
注意点
処理速度もかなり改善されてすばらしい機能だと思いましたが、
やはりいいことばかりではなく注意しないといけない点がありました。
・SQL Serverでしか使用できない
・登録のみに対応
・データテーブルを使用するのでデータ件数が多くなりすぎると
メモリ使用量が半端ないことに
・一括で登録なのでエラーになった場合どのデータが悪いのか
すぐわからない
と、使う場面が限られてしまいますが、使える場面があれば使っていくべき
ものだと思います。
