For ～ Nextの回避策について

Question

For ～ Next の回避策について、なにか良い方法があれば教えてください。

データベースにテーブル（T_WK_StockTaking_Log）があります。
データ数は、約800万件。

ここから、ID=114のデータ（約12万件）をまず削除します。
別のテーブルからデータを集計・計算・その他の処理をして、datatable（dt）を作成（ID=114）します。
---このテーブル（dt）の構成は、T_WK_StockTaking_Logと同じです。

削除無しでの更新はできません---削除前と削除後のデータ数が同じとは限りませんから（データ数が増えることもあります）。
なので、一旦削除してから、追加（Insert）をします。

        'T_WK_StockTaking_Log データ挿入　
        For i = 0 To dt.Rows.Count - 1
            Dim TWSTLG_Inst As String
            TWSTLG_Inst = "INSERT INTO T_WK_StockTaking_Log"
            TWSTLG_Inst &= " (ItemNo,CloseDate,CloseControl_ID,---)"
            TWSTLG_Inst &= " Values(@ItemNo,@CloseDate,@CloseControl_ID, -----)"
            Dim sqlInst As New System.Data.SqlClient.SqlCommand(TWSTLG_Inst, Con)
            sqlInst.Parameters.AddWithValue("@ItemNo", Cint(dt.Rows(i).Item("ItemNo")))
            sqlInst.Parameters.AddWithValue("@CloseDate", CDate(dt.Rows(i).Item("Closedate")))
            sqlInst.Parameters.AddWithValue("@CloseControl_ID", Cint(dt.Rows(i).Item("CloseControl_ID")))
            ---------------------
            ---------------------
            sqlInst.ExecuteNonQuery()

            If i = dt.Rows.Count - 1 Then
                sqlInst.Dispose()
            End If
        Next

この方法で成功はしていますが、処理時間が長いのです。
ループ処理で時間を取っているのだと思います。

For ～ Nextを使用しないで、このdatatable(dt)をデータベースのテーブルに追加（Insert）する方法は、ありますか？
目的は、処理時間の短縮です。

YKsaila

Answer 1

一般的にはBULK INSERTを使うとか。

---

甕星

Answer 2

クライアント側で集計とかさせずに、SQL サーバー内で必要な計算を完結させるのがベストですね。
クライアントに取り込む必要があるなら、SqlBulkCopyつかうと速いです

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        'テスト用のデータ
        Dim dt As New DataTable
        dt.Columns.Add("ID", GetType(Integer))
        dt.Columns.Add("ItemNO", GetType(Integer))
        dt.Columns.Add("CloseDate", GetType(DateTime))
        dt.Columns.Add("CloseControl_ID", GetType(Integer))
        For i = 0 To 120000 - 1
            Dim row = dt.NewRow()
            row.Item("ID") = 114
            row.Item("ItemNo") = i
            row.Item("CloseDate") = DateTime.Now
            row.Item("CloseControl_ID") = i
            dt.Rows.Add(row)
        Next

        Using con As New System.Data.SqlClient.SqlConnection("Data Source=***;Initial Catalog=TEST1;Integrated Security=True")
            con.Open()
            '失敗したときにロールバックするためのトランザクションを開始
            Dim tran = con.BeginTransaction()
            Try
                '一時テーブルをSQLServerに作成(コネクションを閉じれば消えます)
                '一時テーブル経由しなくても直接挿入先に挿入でもいいです
                Dim SQL_TempTabel = "CREATE TABLE [dbo].[#Temp1] ([ID] [int] NOT NULL, [ItemNO] [int] NOT NULL , [CloseDate] [datetime] NOT NULL , [CloseControl_ID] [int] NOT NULL)"
                Dim cmdTemp = New SqlClient.SqlCommand(SQL_TempTabel, con, tran)
                cmdTemp.ExecuteNonQuery()

                '一時テーブルに一気にデータを挿入
                Dim bulk As New System.Data.SqlClient.SqlBulkCopy(con, SqlClient.SqlBulkCopyOptions.Default, tran)
                bulk.BulkCopyTimeout = 1 * 60 '1分
                bulk.DestinationTableName = "[#Temp1]"
                bulk.WriteToServer(dt)'挿入

                '挿入先の不要な行を削除
                Dim SQL_Delete = "DELETE FROM T_WK_StockTaking_Log where ID=@ID"
                Dim cmdDelete As New System.Data.SqlClient.SqlCommand(SQL_Delete, con, tran)
                cmdDelete.Parameters.AddWithValue("@ID", 114)
                cmdDelete.ExecuteNonQuery()

                '一時テーブルから挿入先に挿入
                Dim SQL_insert = "INSERT INTO dbo.T_WK_StockTaking_Log([ID],[ItemNO],[CloseDate],[CloseControl_ID]) select [ID],[ItemNO],[CloseDate],[CloseControl_ID] from dbo.[#Temp1]"
                Dim cmdInsert As New System.Data.SqlClient.SqlCommand(SQL_insert, con, tran)
                cmdInsert.ExecuteNonQuery()

                tran.Commit()
            Catch ex As Exception
                tran.Rollback()'失敗したらロールバック
                MessageBox.Show(ex.Message)
            End Try
        End Using
    End Sub
End Class

#ループ内でコマンドを毎回作り直していますがループの外でコマンドを作って、パラメーターをループ内で設定した方がいいです。

---

個別に明示されていない限りgekkaがフォーラムに投稿したコードにはフォーラム使用条件に基づき「MICROSOFT LIMITED PUBLIC LICENSE」が適用されます。(かなり自由に使ってOK!)

Answer 3

gekka様へ

ありがとうございます。　しかし、まだ、うまくいきません。

事前に、サーバー上のテーブル(T_WK_StockTaking_Log）は削除（ID=114のデータのみ）してあります。
接続も開いています（con.Open）
datatableは、事前作成済（dt）。

御教示の例に倣ってやってみたのですが、下記のエラーコメントが出ました。
”指定されたColumnMappingは、ソースのいずれとも一致しません”
私がコードを書き間違えたか、何かが不足していたのでしょうか？

それで、下記のコードでやってみました。
このコードで成功すればありがたいのですが、ダメでした。
Using bulkCopy As New SqlClient.SqlBulkCopy(Con)
     bulkCopy.DestinationTableName = "T_WK_StockTaking_Log"　　　 'DataBase内のテーブル Insert先
     bulkCopy.WriteToServer(dt)　　　 'dt: DataTable　Insert元
End Using

これでも、下記のエラーコメントが出ました。
”データソースから与えられたInt32型の値を指定された列の型(datetime)に変換不能-----”

datatableに型指定がなされていないから（？）と考え、事前に型指定をしてからdatatableを作成してみました。
しかし、下記のコードでも、同じエラーでした。

下記Select文でdatatableを作成していますが、そこで型指定が出来ていないからなのでしょうか。

 Dim ds As New DataSet
 Dim dt As New DataTable
  ds.Tables.Add(dt)

'データ型事前指定---追加コード（*)まで

  dt.Columns.Add("ItemNo", GetType(Integer))
  dt.Columns.Add("CloseDate", GetType(Date))
  dt.Columns.Add("CloseControl_ID", GetType(Integer))
  dt.Columns.Add("Client_ID", GetType(Integer))
  ----------------------
  ----------------------

’（*）

  'datatable作成--　下記の日付、114は実例
        Dim SQL_Str As String
        SQL_Str = "SELECT T_ItemStock.ItemNo,　'2014/8/8' as CloseDate,114 as CloseControl_ID, 　T_ItemStock.Client_ID, "
        SQL_Str &= "-------- From <テーブル名>"
　　　　-------------------------------
        adapter.SelectCommand = New SqlClient.SqlCommand(SQL_Str, Con)
        adapter.SelectCommand.CommandType = CommandType.Text
        adapter.Fill(dt)

 Using bulkCopy As New SqlClient.SqlBulkCopy(Con)
     bulkCopy.DestinationTableName = "T_WK_StockTaking_Log"  　　'DataBase内のテーブル Insert先
     bulkCopy.WriteToServer(dt) 　　'dt: DataTable　　Insert元
 End Using

通常datatableからデータベースにInsertするときは、型指定をしますが、ここでの指定法が間違ているのでしょうか？

YKsaila

Answer 4

DBテーブルの構造が提示されていないのではっきりしませんが、DataTableと列が一致していないのでこのエラーになっているのでしょう。

説明が足らなかったですが、SqlBulkCopy.ColumnMappings プロパティにの解説を読んでください。
DataTableの列の並びとDBのテーブルの並び順が一致していないなら対応する列を登録する必要があります
DB側の列が後ろに多い場合はたぶんセーフ。前や途中に余計な列がある場合はアウト。
#根本的に対応する列の型が違うときもエラーになりますが
先の例ではDataTableの列の並びと一時テーブルの列の並びが一致しているので正常にバルクコピーが成功します。

並びが違うだけで名前が同じなら以下のようにできます

Dim bulk As New System.Data.SqlClient.SqlBulkCopy(con, SqlClient.SqlBulkCopyOptions.Default, tran)
bulk.DestinationTableName = "[T_WK_StockTaking_Log]"
For Each column As DataColumn In dt.Columns
　　　　 'bulk.ColumnMappings.Add("DataTableの列名","データベースの列名")
　　　　 bulk.ColumnMappings.Add(column.ColumnName, column.ColumnName)
Next
bulk.WriteToServer(dt) '挿入

---

個別に明示されていない限りgekkaがフォーラムに投稿したコードにはフォーラム使用条件に基づき「MICROSOFT LIMITED PUBLIC LICENSE」が適用されます。(かなり自由に使ってOK!)

Answer 5

gekka様へ

できました！

datatableとDataBase内のテーブル（T_WK_StockTaking_Log）の列順が違っていました。
datatableの列順をサーバー・テーブルの列順に合わせましたら、できました。

datatableの再度の型指定も不要でした。

所要時間も劇的に短縮しました。
以前は10分近くかかっていました（データ数、約12万件）が、約40～50秒で終了します。
このコードの部分だけでしたら、おそらく 20秒ぐらいだとおもいます。

ありがとうございました。本当に、助かりました。

YKsaila

以下は、成功したコード例：

 '接続は、 すでに開いているので省略
 '（A)--datatableとDataBase内のテーブルの列順一致しているとき
  Using bulkCopy As New SqlClient.SqlBulkCopy(Con)
     bulkCopy.DestinationTableName = "T_WK_StockTaking_Log"  'DataBase内のテーブル（Insert先）
     bulkCopy.WriteToServer(dt) 　　'dt: DataTable（Insert元）
  End Using

 ’ColumnMappings使用例---列順が異なるときも、以下で成功
 '（B)--datatableとDataBase内のテーブルの列順が異なるとき---Sql ColumnMappingsを使用
  Using Cn As New System.Data.SqlClient.SqlConnection(St)　　　'st: ConnectionString
  Cn.Open()
    Dim tran = Cn.BeginTransaction()
    Dim bulk As New System.Data.SqlClient.SqlBulkCopy(Cn, SqlClient.SqlBulkCopyOptions.Default, tran)
    bulk.DestinationTableName = "T_WK_StockTaking_Log"   'DataBase内のテーブル(Insert先)
  　'列順の調整
　 For Each column As DataColumn In dt.Columns 

     bulk.ColumnMappings.Add(column.ColumnName, column.ColumnName)   
   Next
   bulk.WriteToServer(dt)   'dt: DataTable(Insert元)
   tran.Commit()
  End Using