SQLServerでDataTableの内容を一度のSQLでデータベースに更新する
DataTable の内容をデータベースに登録、または更新したい場合がある。
しかし、DataTable の行をループしながら行毎に INSERT 文や UPDATE 文を発行すると IO 負荷が高くなり、パフォーマンスが悪化する。
そこで、SQL Server のユーザー定義テーブル型を使用することにより一括での更新が可能となる。
前提条件：SQL Server 2008 以降
IO負荷が高い例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | Sub Test(ByVal dt As DataTable) Dim con As New SqlConnection("connection string") Dim cmd As New SqlCommand Dim sql As New StringBuilder con.Open() cmd.Connection = con cmd.Parameters.Clear() 'データテーブルの行分だけループしている悪い例 For i As Integer = 0 To dt.Rows.Count - 1 sql.AppendLine("INSERT INTO dbo.T_TEST ") sql.AppendLine("( ") sql.AppendLine(" CODE ") sql.AppendLine(") VALUES ( ") sql.AppendLine(" @Code ") sql.AppendLine("); ") 'パラメータの作成 Dim param = New SqlParameter("@Code", SqlDbType.Int, ParameterDirection.Input) param.Value = dt.Rows(i).Item(0).ToString() 'パラメータを渡します。 cmd.Parameters.Add(param) 'プロシージャの実行 cmd.CommandType = CommandType.Text cmd.CommandText = sql.ToString() cmd.ExecuteNonQuery Next i End Sub |
まずは、悪例としてデータテーブルのレコードを 1 件ずつ INSERT していく方法。
上記の方法だと件数が少なければ問題が無いように感じるが、１万件を超えてくるとそのパフォーマンスの悪さが目立ってくる。
１万件のデータをループして INSERT すると１万回の IO 処理となり、オーバーヘッドが大きくなる。
しかし、次の方法で INSERT すると１回の IO 処理で済む為、更新するデータ量は変わらないがかなりのパフォーマンス改善が見込まれる。
DataTableをパラメータとして渡す
DataTable をパラメータとして渡す為には、SQL Server 側にユーザー定義テーブル型を作成しておく必要がある。
1 2 3 4 5 | CREATE TYPE TestTableType AS TABLE ( TestCode int NOT NULL ) GO |
テーブルの定義と同様に括弧の中にフィールド定義を記述する。
テーブルで定義できるフィールドはTableTypeでも定義可能。
続いて、プログラム側の処理。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | Sub Test(ByVal dt As DataTable) Dim con As New SqlConnection("connection string") Dim cmd As New SqlCommand Dim sql As New StringBuilder con.Open() cmd.Connection = con cmd.Parameters.Clear() sql.AppendLine("INSERT INTO dbo.T_TEST ") sql.AppendLine(" ( ") sql.AppendLine(" CODE ") sql.AppendLine(" ) ") sql.AppendLine(" SELECT TestCode ") sql.AppendLine(" FROM @TestTable ") sql.AppendLine(" WHERE TestCode = @Code ") sql.AppendLine("); ") 'パラメータの作成 Dim params(1) As SqlParameters params(0) = New SqlParameter("@Code", SqlDbType.Int, ParameterDirection.Input) params(0).Value = 1 params(1) = New SqlParameter("@TestTable", SqlDbType.Structured, ParameterDirection.Input) 'TypeにSqlDbType.Structuredを渡します。 params(1).TypeName = "TestTableType" 'テーブルタイプの名称を渡します。 params(1).Value = dt '編集したデータテーブルを渡します。 'パラメータ配列を渡します。 cmd.Parameters.AddRange(params) 'プロシージャの実行 cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = sql.ToString() cmd.ExecuteNonQuery End Sub |
ユーザー定義テーブル型を使用することで DataTable をテーブルのように渡すことができる為、一括で SELECT して INSERT または UPDATE が可能となる。
<注意事項>
- DataTable のフィールドはユーザー定義テーブル型のフィールドと一致している必要がある。
- ユーザー定義テーブル型を渡す場合は、SqlDbType.Structured で渡す。
ユーザー定義テーブル型を作成しておく必要はあるが、こちらの方法であれば実行速度が圧倒的に速く、パフォーマンス改善が期待できる。
デメリットとしては、ループせずに一括で更新する為、プログレスバーなどで進捗を表示することができない。
