我可以将Parallel.For与sql命令一起使用吗？ [英] Can I use Parallel.For with sql Commands?

问题描述

我有一类范围

public class avl_range
{
    public long start { get; set; }
    public long end { get; set; }
}

如果我使用普通的 FOR 可以完美地工作，但是必须等待每个命令完成并且每个查询需要8秒，所以10个查询需要80秒。

If I use a normal FOR works perfect, but have to wait for each command to finish and each query take 8 seconds, so 10 queries take 80 seconds.

在并行版本中，如果我只打印范围完美，但是如果尝试执行命令，则说明该过程已在进行中。

In the Parallel version If I only print the ranges works perfect, but if try to execute the command say is already in progress.

{操作已在进行中。}

{"An operation is already in progress."}

我该如何解决？

var numbers = new List<avl_range>();
using (var conn = new NpgsqlConnection(strConnection))
    {
        conn.Open();

        Action<avl_range> forEachLoop = number => //Begin definition of forLoop
        {
             // only the console write line works ok
            Console.WriteLine(number.start + " - " + number.end);

            using (var cmd = new NpgsqlCommand())
            {
                cmd.Connection = conn;                            
                cmd.CommandText = String.Format( "SELECT * FROM avl_db.process_near_link({0}, {1});"
                                                 , number.start
                                                 , number.end);
                // here cause the error.
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(reader.GetString(0));
                    }
                }
            }
        };

        Parallel.ForEach(numbers, forEachLoop);
    }
 );

仅供参考：我正在尝试解决此问题，因此发布了之前

FYI: Im trying to solve this issue I post it before

推荐答案

不能同时使用Npgsql连接-在任何给定时间点只能运行一个命令（换句话说，就是，不提供MARS支持。）

An Npgsql connection can't be used concurrently - only one command may be running at any given point in time (in other words, no MARS support).

打开多个连接以并行执行查询肯定很有意义。尽管建立新的物理连接非常昂贵，但是连接池非常轻巧，因此重用物理连接的开销很小。不这样做的主要原因是，如果您需要在同一事务中进行多个操作。

It may definitely make sense to open multiple connections to perform your queries in parallel. Although establishing a new physical connection is expensive, connection pooling is extremely lightweight, so there's very little overhead in reusing physical connections. The main reason not to do this is if you need your multiple operations to be in the same transaction.

这篇关于我可以将Parallel.For与sql命令一起使用吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！