当前位置: 首页 > 面试题库 >

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

长孙骏
2023-03-14
问题内容

我试图找出如何使用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 create temp table语句中:

创建插入字符串:

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);
...

Since I’ve seen people telling me (on stack exchange
https://dba.stackexchange.com/questions/44217/fastest-way-to- insert-30-thousand-rows-in-sql-server/44222?noredirect=1#comment78137_44222 )
That I should use SQLBulkCopy and that would be faster I believe that I should
improve the way I do it. So if anyone can suggest how I can improve my
SQLBulkCopy code OR tell me if there is a better insert statement that can
improve my application’s performance that would be great.


问题答案:

Your problem may be in localTempTable.AcceptChanges(); Since it commit your
changes.
If you do the next , I think it will run faster

    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);

    }

From MSDN - DataSet.AcceptChanges

Commits all the changes made to this DataSet since it was loaded or since
the last time AcceptChanges was called.



 类似资料:
  • 我正在尝试从一个oracle数据库迁移到另一个。我的选择查询非常复杂,它返回大约2000万条记录。当我尝试执行时: 大约需要20个小时。 有什么不同的迁移方式吗?你能给我一些建议吗? 谢谢你的建议。 编辑:我创建了一个新表格,如下所示 将表XYZ创建为从yyy join zzz join ttt等中选择a,b,c,bla blaa 大约需要2分钟。 现在我正在尝试从XYZ插入到我的原始表。 还剩1

  • 问题内容: 我有以下用C#编写的代码,但是据此,我需要4-5天的时间才能将数据从Oracle数据库迁移到Elasticsearch。我将以100批为单位插入记录。是否还有其他方法可以使400万条记录的迁移速度更快(如果可能的话,可能不到一天)? 问题答案: 该功能将对性能产生负面影响,并且您正在运行数千次。您已经在使用-它不会一次将所有四百万行都拉到您的计算机上,它基本上是一次流一次或几行。 这必

  • 问题内容: 我有以下格式的数据: 我需要将其插入到临时表中,如下所示: 这样我就可以将其与另一个表中的数据进行比较。 上面的数据代表“节点”列的单独行。 是否有一种简单的方法可以将所有这些数据全部插入一个命令中? 另外,数据实际上将以字符串形式进入,因此…我需要能够将其连接到SQL查询字符串中。显然,如果需要,我可以先对其进行修改。 问题答案: 尝试类似的东西

  • 问题内容: 这似乎很简单,但我坚持使用简单的insert语句。请参见以下内容: 在临时表行中,实际列表是从其他位置动态拉出的(这是出于演示目的)。我收到以下错误消息: 错误代码:1054。“字段列表”中的未知列“ insert_table.resource_id” 现在,我可以在整个temp表上运行一次select,它返回正常值,只是在update语句中失败。我正在MySQL工作台上运行它。完全不

  • 我已经创建了复制分支用户权限的方法。权限通常为0到120个项目。运行此方法将此(0到120)项插入到7个用户ID需要35-55秒才能插入到表中。 我需要优化这个过程,因为数据插入的性能很慢。我在想如果超过10个用户,那么这个过程会花费太长时间。有没有办法提高插入数据的性能速度? 提前谢谢你!

  • 问题内容: 在iPhone上,使用FMDB获取SQLite数据库中最后插入的行的ID的最佳方法是什么? 有没有比做更好的方法: 问题答案: 如果可以保证您的列是自动递增的列,那很好。 但不管怎样,有一个专门的SQLite函数称为: 在FMDB中,您使用方法(在内部运行上述方法):