記事一覧

DataTableのデータを一括でデータベースに更新する方法 | VB.Net


今回は DataTable の内容を一括で SQL Server に更新する方法をご紹介いたします。
DataTable のRowをループして毎行に対して INSERT 文や UPDATE 文を発行することはとてもIO負荷が高く、パフォーマンス的にお勧めできません。
そこで SQL Server の TableType を使用することにより一括での更新が可能となります。
TableTypeの作成方法については前回ご紹介しておりますので、そちらをご覧ください。



前提条件
Visual Studio 2005 以降
SQL Server 2005 以降
.Net Framework 2.0 以降



1.IO負荷が高い推奨できない例

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万件を超えてくるとそのパフォーマンスの悪さが目立ってきます。1万件のデータをループしてINSERTすると1万回のIO処理となり、オーバーヘッドが高いです。
それよりも次の方法でINSERTすると1回のIOで済みます。ただ、データの重さは変わりませんが、かなりパフォーマンスが改善されることが見込めます。



2.DataTableを引数に渡す方法(VBのみ)

VB側のみの記述
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 = "dbo.TestTableType"  'テーブルタイプの名称を渡します。
    params(1).Value = dt    '編集したデータテーブルを渡します。
    'パラメータ配列を渡します。
    cmd.Parameters.AddRange(params)
    'プロシージャの実行
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = sql.ToString()
    cmd.ExecuteNonQuery
 
End Sub

TableType を使用することでテーブルみたいにデータを渡すことができるので、一括で SELECT してINSERT または UPDATE が可能です。



3.StoredProcedureの引数にDataTableを渡す方法

VB側の記述
Sub Test(ByVal dt As DataTable)

    Dim con As New SqlConnection("connection string")
    Dim cmd As New SqlCommand
    con.Open()
    cmd.Connection = con
    cmd.Parameters.Clear()
    'パラメータの作成
    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 = "dbo.TestTableType"  'テーブルタイプの名称を渡します。
    params(1).Value = dt    '編集したデータテーブルを渡します。
    'パラメータ配列を渡します。
    cmd.Parameters.AddRange(params)
    'プロシージャの実行
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = "dbo.TestProcedure"
    cmd.ExecuteNonQuery

End Sub

SQLServerのプロシージャの記述
CREATE PROCEDURE dbo.TestProcedure
    @Code INT,
    @TestTable dbo.TestTableType READONLY
AS
BEGIN   
    SET NOCOUNT ON;
    INSERT INTO T_TEST
        (
         CODE
        )
    SELECT  TestCode
      FROM  @TestTable
     WHERE  TestCode = @Code
    ;
END;
GO


注意事項
・DataTable のフィールドは TableType のフィールドと一致している必要があります。
・TableType を渡す場合は、SqlDbType.Structured で渡します。
・Procedure に渡す場合は、READONLY を付けないとエラーとなります。

少し気を付けてコーディングする必要はありますが、慣れてしまえばこちらの方法の方が実行速度が圧倒的に速く、パフォーマンス改善の切り札として活用できます。ループせずに一括で更新することから、プログレスバーなどで進捗をカウントすることができない点だけがデメリットでしょうか。


関連記事

コメント

コメントの投稿

広告

プロフィール

石河 純


著者名 :石河 純
自己紹介:素人上がりのIT技術者。趣味は卓球・車・ボウリング

IT関連の知識はざっくりとこんな感じです。
【OS関連】
WindowsServer: 2012/2008R2/2003/2000/NT4
Windows: 10/8/7/XP/2000/me/NT4/98
Linux: CentOS RedHatLinux9
Mac: macOS Sierra 10.12 / OSX Lion 10.7.5 / OSX Snow Leopard 10.6.8
【言語・データベース】
VB.net ASP.NET C#.net Java VBA
Xamarin.Forms
Oracle10g SQLServer2008R2 SQLAnywhere8/11/16
ActiveReport CrystalReport ReportNet(IBM)
【ネットワーク関連】
CCNP シスコ技術者認定
Cisco Catalyst シリーズ
Yamaha RTXシリーズ
FireWall関連
【WEB関連】
SEO SEM CSS IIS6/7 apache2

休みの日は卓球をやっています。
現在、卓球用品通販ショップは休業中です。