sql-server - SQL数据库表BULK更新的陷阱，在vb.net中使用staging table和bulkcopy

Question

我一直在寻求提高我的 vb.net 程序中表更新的速度。
场景是我有一个包含 10,000 行和 160 列的表。
此表已更新非常频繁，每行更改 1 到 100 多个列。
使用 DataAdapter 更新非常缓慢且不合适，并且已经过调查但没有改进结果。

下一个选项很可能是使用 bulkcopy 将数据转储到暂存表的过程，从原始数据库表中删除所有行，然后发出复制命令以将数据从暂存表返回到我希望更新的原始表。

考虑到更新过程在数据运行的程序执行期间发生 30-50 次，速度差异是惊人的

数据适配器更新 = 10-15 秒乘以 50 = 12.5 分钟

描述的批量更新 = 1.2-1.4s 乘以 50 = 1.16 分钟

非凡的不同!所以我真的很想选择批量更新选项。
我知道这不是常规的，但是这个表将保持在 10,000 行，并且该程序是单线程单用户本地数据库，消除了很多潜在的问题。

我主要关心的是数据安全。
我已经将代码包含在 try-catch 中，但也许有一种方法可以确保所有涉及 SQL 事务的进程都正确运行，就像它们在银行中所做的那样，所以如果出现问题，一切都会被逆转。
考虑到我的技能水平没有超过创建下面的代码。
该程序的最佳实现是什么？

 Try

            ESTP = "Start Bulk DBselection Update"

            Dim oMainQueryT = "Truncate Table DBSelectionsSTAGE"
            Using con As New SqlClient.SqlConnection(RacingConStr)
                Using cmd As New SqlClient.SqlCommand(oMainQueryT, con)
                    con.Open()
                    cmd.ExecuteNonQuery()
                    con.Close()
                End Using
            End Using

            ESTP = "Step 1 Bulk DBselection Update"

            Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(RacingConStr)
                bulkCopy.DestinationTableName = "DBSelectionsSTAGE"
                bulkCopy.WriteToServer(DBSelectionsDS.Tables("DBSelectionsDetails"))
                bulkCopy.Close()
            End Using

            ESTP = "Step 2 Bulk DBselection Update"

            oMainQueryT = "Truncate Table DBSelections"
            Using con As New SqlClient.SqlConnection(RacingConStr)
                Using cmd As New SqlClient.SqlCommand(oMainQueryT, con)
                    con.Open()
                    cmd.ExecuteNonQuery()
                    con.Close()
                End Using
            End Using

            ESTP = "Step 3 Bulk DBselection Update"

            oMainQueryT = "Insert INTO DBSelections Select * FROM DBSelectionsSTAGE"
            Using con As New SqlClient.SqlConnection(RacingConStr)
                Using cmd As New SqlClient.SqlCommand(oMainQueryT, con)
                    con.Open()
                    cmd.ExecuteNonQuery()
                    con.Close()
                End Using
            End Using

            Data_Base.TextBox25.Text = "Deleting data - DONE "
            Data_Base.TextBox25.Refresh()

DBSelectionsDS.Tables("DBSelectionsDetails").AcceptChanges()

        Catch ex As Exception

            ErrMess = "ERROR - occured at " & ESTP & " " & ex.ToString
            Call WriteError()
            Call ViewError()

        End Try

编辑:我将代码合并到我的项目中并采纳了 Dan 建议的更改。
“更新”现在只需不到一秒钟。这是对以前代码的巨大改进。我不确定这种扩展的效果如何，但在我的情况下，表格的大小(行数)大致相同，我对结果非常满意。

完成后不要忘记接受对数据表的更改DBSelectionsDS.Tables("DBSelectionsDetails").AcceptChanges()

Answer 1

考虑用事务性 TRUNCATE 和 SWITCH 替换步骤 2 和 3，以用暂存数据替换 DBSelection 表的内容。 SWITCH 比复制单个行更有效，因为它是一种仅元数据操作。

SWITCH 在 SQL Server Enterprise Edition 中通常用于将数据移动(而不是复制)分区表，但它也可以用于非分区表和较小的版本。 SWITCH 的基本要求是源表和目标表具有相同的架构(包括索引)，驻留在相同的文件组中，并且目标表为空。操作后源暂存表将为空。参见 https://technet.microsoft.com/en-us/library/ms191160.aspx有关 SWITCH 的更多详细信息。

下面是如何实现此技术的示例。

Try

    ESTP = "Start Bulk DBselection Update"

    Dim oMainQueryT = "Truncate Table DBSelectionsSTAGE"
    Using con As New SqlClient.SqlConnection(RacingConStr)
        Using cmd As New SqlClient.SqlCommand(oMainQueryT, con)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using

    ESTP = "Step 1 Bulk DBselection Update"

    Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(RacingConStr)
        bulkCopy.DestinationTableName = "DBSelectionsSTAGE"
        bulkCopy.WriteToServer(DBSelectionsDS.Tables("DBSelectionsDetails"))
        bulkCopy.Close()
    End Using

    ESTP = "Step 2 and 3 - replace DBselection with staged data"

    oMainQueryT = _
        "SET XACT_ABORT ON;" + _
        "BEGIN TRAN;" + _
        "TRUNCATE TABLE dbo.DBSelections;" + _
        "ALTER TABLE dbo.DBSelectionsSTAGE SWITCH TO dbo.DBSelections;" + _
        "COMMIT;"
    Using con As New SqlClient.SqlConnection(RacingConStr)
        Using cmd As New SqlClient.SqlCommand(oMainQueryT, con)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using

    Data_Base.TextBox25.Text = "Replaced data - DONE "
    Data_Base.TextBox25.Refresh()

Catch ex As Exception

    ErrMess = "ERROR - occured at " & ESTP & " " & ex.ToString
    Call WriteError()
    Call ViewError()

End Try