在SQL Server中使用C#插入3万行数据到临时表的最快方法

Question

在SQL Server中使用C#插入3万行数据到临时表的最快方法

19

我试图找出如何使用C#在SQL Server中提高临时表的插入性能。有些人说我应该使用SQLBulkCopy，但似乎它比简单构建SQL插入字符串要慢得多。下面是我使用SQLBulkCopy创建表的代码：

public void MakeTable(string tableName, List<string> ids, SqlConnection connection)
    {

        SqlCommand cmd = new SqlCommand("CREATE TABLE ##" + tableName + " (ID int)", connection);
        cmd.ExecuteNonQuery();

        DataTable localTempTable = new DataTable(tableName);

        DataColumn id = new DataColumn();
        id.DataType = System.Type.GetType("System.Int32");
        id.ColumnName = "ID";
        localTempTable.Columns.Add(id);

        foreach (var item in ids)
        {
             DataRow row = localTempTable.NewRow();
             row[0] = item;
             localTempTable.Rows.Add(row);
             localTempTable.AcceptChanges();
        }


        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = "##" + tableName;
            bulkCopy.WriteToServer(localTempTable);

        }
    }

这样我的插入操作需要很长时间才能执行完。我用另一种方法来使我的插入操作更快:

我将插入操作的代码作为字符串创建，并在SQL临时表创建语句中连接它:

创建插入操作字符串：

public string prepareInserts(string tableName, List<string> ids)
    {
        List<string> inserts = new List<string>();

        var total = ids.Select(p => p).Count();
        var size = 1000;

        var insert = 1;

        var skip = size * (insert - 1);

        var canPage = skip < total;

        while (canPage)
        {
            inserts.Add(" insert into ##" + tableName + @" (ID) values " + String.Join(",", ids.Select(p => string.Format("({0})", p))
                        .Skip(skip)
                        .Take(size)
                        .ToArray()));
            insert++;
            skip = size * (insert - 1);
            canPage = skip < total;
        }

        string joinedInserts = String.Join("\r\n", inserts.ToArray());

        return joinedInserts;

    }

在创建查询后，在SQL语句中使用它们：

inserts = prepareInserts(tableName, ids);

var query = @"IF EXISTS
                                            (
                                            SELECT *
                                            FROM tempdb.dbo.sysobjects
                                            WHERE ID = OBJECT_ID(N'tempdb..##" + tableName + @"')
                                            )
                                                BEGIN
                                                    DELETE FROM ##" + tableName + @"
                                                END
                                            ELSE
                                                BEGIN
                                                    CREATE TABLE ##" + tableName + @"
                                                    (ID int)
                                                END " + inserts;

            var command = new SqlCommand(query, sqlConnection);
...

自从我看到有人在stack exchange（https://dba.stackexchange.com/questions/44217/fastest-way-to-insert-30-thousand-rows-in-sql-server/44222?noredirect=1#comment78137_44222）告诉我应该使用SQLBulkCopy，这样会更快，我相信我应该改善我的做法。所以如果有人能建议我如何改进我的SQLBulkCopy代码或告诉我是否有更好的插入语句可以提高我的应用程序性能，那将是非常棒的。

- Jenninha

这个字符串列表的数据最初来自哪里？ - Dan Bracuk

这将是来自另一个应用程序的一组键。我还没有完成这个链接。现在它是一个由我创建的带有一些测试ID的数组。 - Jenninha

这30,000个ID很可能来自某个数据库。如果是这样的话，我会寻找一种使用SQL完成所有操作的方法。 - Dan Bracuk

1

它可能不会来自SQL，这就是为什么我需要为我的情况创建一个临时表的原因。 - Jenninha

3个回答

6

我使用StopWatch对象自己运行这段代码来测量时间。每次迭代中的AcceptChanges使得速度变慢。

public void MakeTable(string tableName, List<string> ids, SqlConnection connection)
{
    SqlCommand cmd = new SqlCommand("CREATE TABLE ##" + tableName + " (ID int)", connection);
    cmd.ExecuteNonQuery();

    DataTable localTempTable = new DataTable(tableName);

    DataColumn id = new DataColumn();
    id.DataType = System.Type.GetType("System.Int32");
    id.ColumnName = "ID";
    localTempTable.Columns.Add(id);

    System.Diagnostics.Stopwatch sw1 = new System.Diagnostics.Stopwatch();        

    sw1.Start();
    foreach (var item in ids)
    {
        DataRow row = localTempTable.NewRow();
        row[0] = item;
        localTempTable.Rows.Add(row);

    }
    localTempTable.AcceptChanges();
    long temp1 = sw1.ElapsedMilliseconds;
    sw1.Reset();
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "##" + tableName;
        bulkCopy.WriteToServer(localTempTable);

    }
    long temp2 = sw1.ElapsedMilliseconds;
}

当AccpetChanges在foreach循环内部时的结果

enter image description here

而当它不在循环内部时

enter image description here

差异为3个数量级 :)

- Ron Biggs

0

使用 IDataReader，它会运行得更快

而不是 cmd.ExecuteNonQuery(); 执行

cmd.ExecuteReader()

- Enfantcool

3

你说得没错，使用IDataReader比使用DataTable更好，但是你关于使用cmd.ExectuteReader()的说法是不正确的，因为代码实际上并没有返回任何结果集，所以应该使用cmd.ExecuteNonQuery();。 - Scott Chamberlain

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Mzf · Accepted Answer

你的问题可能在于localTempTable.AcceptChanges();，因为它会提交你的更改。
如果你做下一个步骤，我认为它会更快运行。

    foreach (var item in ids)
    {
         DataRow row = localTempTable.NewRow();
         row[0] = item;
         localTempTable.Rows.Add(row);

    }

    localTempTable.AcceptChanges();

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "##" + tableName;
        bulkCopy.WriteToServer(localTempTable);

    }

来自MSDN - DataSet.AcceptChanges

提交所有自从加载数据集以来或上次调用AcceptChanges以来所做的更改。