批量插入Sql Server [英] Mass Insert Into Sql Server

问题描述

我试图从 Vb.Net 向 SQL Server 2005 中插入大量记录.尽管插入工作正常，但我正在尽最大努力使其尽可能快.目前，100,000 条记录需要大约 11 分钟.将大量记录从应用程序插入 SQL Server 的建议方法是什么?

I am attempting to insert a mass of records into SQL Server 2005 from Vb.Net. Although the insertion is working fine, I am doing my best to try to make it as fast as possible. Currently, it takes ~ 11 mins for 100,000 records. What would be the suggested approach to inserting a large number of records into SQL Server from an Application?

我当前的方法基本上是打开连接，遍历我的信息列表并触发单个 sql 插入语句，然后关闭连接.有人对如何做到这一点有更好的建议吗?

My current apporach is basically opening the connection, iterating through my list of information and firing off individual sql insert statments, and then closing the connection. Anyone have a better suggestion on how to do this?

当前功能:

Public Sub BatchInsert(ByVal ParamCollections As List(Of SqlParameter()))

    Dim Conn As SqlConnection = New SqlConnection(DBHelper.DatabaseConnection)
    Using scope As TransactionScope = New TransactionScope()
    Using Conn

        Dim cmd As SqlCommand = New SqlCommand("sproc_name", Conn)

        Conn.Open()
        cmd.CommandType = CommandType.StoredProcedure

        For i = 0 To ParamCollections.Count - 1

            cmd.Parameters.Clear()
            cmd.Parameters.AddRange(ParamCollections(i))
            cmd.ExecuteNonQuery()

        Next

        Conn.Close()
        scope.Complete()
    End Using
    End Using

End Sub

推荐答案

使用 SqlBulkCopy 类，它将能够比单个插入更快地运行那些 100K 行.

Use the SqlBulkCopy class, it will be able to run through those 100K rows much faster than individual inserts.

哦，如果可以，我会敦促您实施 IDataReader 能力类，提供 SqlBulkCopy.WriteToServer(IDataReader) 方法，这将允许您按顺序生成数据，一次一行.例如，如果您从文本文件导入，构建一些使用 yield return 的 IEnumerable 方法并将其转换为 IDataReader 对象将允许您非常自然地向服务器提供数据.

Oh, and if you can, I would urge you to implement a IDataReader capable class, to feed the SqlBulkCopy.WriteToServer(IDataReader) method, this will allow you to produce data sequentially, one row at a time. If you are importing from a text file, as an example, building some IEnumerable<T> methods that uses yield return and converting it to a IDataReader object will allow you to feed data to the server very naturally.

为了应对 BCP 回滚能力的损失，可以将数据传输到临时表中，然后在服务器上执行正常的 INSERT INTO 语句，从临时表中批量传输数据表插入到生产表中，这将允许您将事务用于最后一个传输部分，并且仍会比原始的单个插入语句运行得快得多.

To counter the loss of rollback ability with BCP, you can transfer the data into a temporary table, and then execute normal INSERT INTO statements on the server afterwards, bulk-transferring the data from the temporary table into the production table, this will allow you to use a transaction for the last transfer part, and will still run a lot faster than your original individual insert statements.

和 这是一个例子(C#、但应该很容易转换为 VB.Net)批量加载 API 的用法.

and Here's an example (C#, but should be easy to convert to VB.Net) of the usage of the bulk load API.

这篇关于批量插入Sql Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！