在数据库C＃多个并行插入 [英] C# multiple parallel inserts in database

问题描述

我有一个约3000行的DataTable。每个这些行的需要在一个数据库表中插入。目前，我运行一个foreach循环为下：

I have a datatable with around 3000 rows. Each of those rows need to be inserted in a database table. Currently, i am running a foreach loop as under:

obj_AseCommand.CommandText = sql_proc;
obj_AseCommand.CommandType = CommandType.StoredProcedure;
obj_AseCommand.Connection = db_Conn;
obj_AseCommand.Connection.Open();

foreach (DataRow dr in dt.Rows)                
{
    obj_AseCommand.Parameters.AddWithValue("@a", dr["a"]);
    obj_AseCommand.Parameters.AddWithValue("@b", dr["b"]);
    obj_AseCommand.Parameters.AddWithValue("@c", dr["c"]);

    obj_AseCommand.ExecuteNonQuery();
    obj_AseCommand.Parameters.Clear();
}

obj_AseCommand.Connection.Close();

您可以请告知我怎么能并行数据库中执行SP，因为上述方法需要大约10 。分钟插入3000行

Can you please advise how can I do parallelly execute the SP in database since the above approach takes about 10 minutes to insert 3000 rows.

推荐答案

的侧面说明：10分钟，3000行过大，甚至具有广泛表单个线程。什么是您的PROC做什么？我认为处理是不平凡的，因此对于存储过程的需要，但如果你只是做简单的插入，按@ 3DD的评论，的 SqlBulkCopy的将产生一个合理的条案每分钟〜1M的行插入。

Side Note : 10 minutes for 3000 rows is excessive even with a wide table and a single thread. What does your proc do? I've assumed the processing isn't trivial, hence the need for the SPROC, but if you are just doing simple inserts, as per @3dd's comment, SqlBulkCopy will yield inserts of ~ 1M rows per minute on a reasonably narrow table.

您可以使用TPL并行做到这一点，例如：特别是与 Parallel.ForEach 的 localInit 过载：

You can do this in parallel using TPL, e.g. specifically with the localInit overload of Parallel.ForEach:

Parallel.ForEach(dt.Rows,
    () =>
    {
        var con = new SqlConnection();
        var cmd = con.CreateCommand();
        cmd.CommandText = sql_proc;
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();

        cmd.Parameters.Add(new SqlParameter("@a", SqlDbType.Int));
        // NB : Size sensitive parameters must have size
        cmd.Parameters.Add(new SqlParameter("@b", SqlDbType.VarChar, 100));
        cmd.Parameters.Add(new SqlParameter("@c", SqlDbType.Bit));
        // Prepare won't help with SPROCs but can improve plan caching for adhoc sql
        // cmd.Prepare();
        return new {Conn = con, Cmd = cmd};
    },
    (dr, pls, localInit) =>
    {
        localInit.Cmd.Parameters["@a"] = dr["a"];
        localInit.Cmd.Parameters["@b"] = dr["b"];
        localInit.Cmd.Parameters["@c"] = dr["c"];
        localInit.Cmd.ExecuteNonQuery();
        return localInit;
    },
    (localInit) =>
    {
        localInit.Cmd.Dispose();
        localInit.Conn.Dispose();
    });

备注：

• 除非你真的知道自己在做什么，一般我们应该离开TPL对并行度决定。但是，这取决于有多少争（读：对于数据库的工作锁）资源，限制并发任务的上限可能被要求（试行错误可能是有用的，例如，使用的4，8，16个并发任务等并发操作尝试看到这使大多数的吞吐量，并监视SQL Server上锁定和CPU负载。


• 同样，留给第三方物流的默认分区通常是不够好，在整个任务划分为数据行。


• 每个任务都需要自己单独的SQL连接。


• 而不是创建并在每次调用处理命令，一旦每个任务创建它，然后继续重用同样的命令，每次只需更新的参数。


• 使用LocalInit /本地最后lambda表达式每次做任务设置和清理，如处置命令和连接。


• 您还可以考虑使用的 。准备（） 如果你正在使用的AdHoc SQL或的


• 的Sql版本>我假设枚举 DataTable的行是线程安全的。你要仔细检查，当然这一点。

• Unless you really know what you are doing, in general we should leave TPL to decide on the degree of parallelism. However, depending on how much contention (read: locks for database work) for resources, restricting the upper limit of concurrent tasks may be required (trial and error may be useful, e.g. try with concurrencies of 4, 8, 16 concurrent tasks etc to see which gives most throughput, and monitor the locking and CPU load on your Sql Server.
• Similarly, leaving TPL's default partitioner is usually good enough to partition the DataRows across the tasks.
• Each Task will need its own separate Sql Connection.
• Rather than creating and disposing the command on each call, create it once per task and then keep reusing the same Command, just updating parameters each time.
• Use the LocalInit / Local Finally lambdas to do per task set up and cleanup, like Disposing commands and connections.
• You could also consider using .Prepare() if you are using AdHoc Sql or Sql versions prior to 2005
• I'm assuming enumerating a DataTable's rows is thread safe. You'll want to double check this of course.

这篇关于在数据库C＃多个并行插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！