Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
237 views
in Technique[技术] by (71.8m points)

c# - Can I use Parallel.For with sql Commands?

I have a class of range

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

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."}

How can I solve this?

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...