CODEWITHSUNDEEP
c#parallel-processing.net-coredapperparallel.foreach
Slope
Slope

Reputation: 3

C#: Connection Errors opening SqlConnection inside Parallel.ForEach

I have a .NET Core C# console application that performs a large number of calculations and then writes the results to a SQL Server 2016 Developer edition database using Dapper (and Dapper.Contrib). The issue I'm having is that when I run a lot of items in parallel (greater than 1000, for example), I start getting intermittent connection failures on the .Open() call, saying

A network-related or instance-specific error occurred...

This often happens after several thousand rows have already been inserted successfully.

A simplified version of the code would look like the following:

        Parallel.ForEach(collection, (item) =>
        {
                var results = item.Calculate(parameters);

                dal.Results.Insert(results);

                allResults.AddRange(results);
        });

And inside the Insert method, it looks like this:

    public override void Insert(IEnumerable<Result> entities)
    {
        using (var connection = GetConnection())
        {
            connection.Open();

            using (var transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
            {
                connection.Insert(entities, transaction);

                transaction.Commit();
            }
        }
    }

Some other things about the code that I don't think are affecting this but might be relevant:

Thanks in advance for any guidance on this issue!

Upvotes: 0

Views: 1464

Answers (1)

Risto M
Risto M

Reputation: 2999

There is no advantage to execute heavily IO-bound db-operations in parallel.

You should create fever but bigger bunches of data to be inserted with minimun amount of database transactions. That can be achieve with several ways:

So try following: Execute CPU-intensive calculations in parallel loop and save allResults into database after loop.

Upvotes: 1

Related Questions