CODEWITHSUNDEEP
c#npgsql
Tejal Barot
Tejal Barot

Reputation: 80

The connection pool has been exhausted

When inserting data into database using parallel foreach I get the following error:

The connection pool has been exhausted'

after inserting some amount of data into the database

try
{
    var connection = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;

    Parallel.ForEach(Enumerable.Range(0, 1000), (_) =>
    {
        using (var connectio = new NpgsqlConnection(connection))
        {
            connectio.Open();
            using (var command = new NpgsqlCommand("fn_tetsdata", connectio) { CommandType = CommandType.StoredProcedure })
            {
                command.Parameters.AddWithValue("firstname", "test");
                command.Parameters.AddWithValue("lastname", "test");
                command.Parameters.AddWithValue("id", 10);
                command.Parameters.AddWithValue("designation", "test");

                command.ExecuteNonQuery();
            }
            connectio.Close();
        }
    });
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}

Upvotes: 0

Views: 4643

Answers (2)

D Stanley
D Stanley

Reputation: 152626

I assume you're using parallelism to improve performance. If that's the case then first you need a baseline. Run the 1,000 queries in serial, creating a new connection each time (which in reality just pulls one from the pool).

Then try it with the same connection object and see if the performance improves.

Then try it with the came command object, just changing the parameter values.

Then try it in parallel with the same connection co you're not creating 1,000 connection objects, which you've already tried.

I would be surprised if you got a significant performance improvement by using parallelism, since Parallel improves the performance of CPU-bound tasks, and data queries are generally much more bound by I/O than CPU.

Upvotes: 2

Murray Foxcroft
Murray Foxcroft

Reputation: 13775

Constrain the amount of parralelism with MaxDegreeOfParallelism, by default it could be exceeding the number of DB connections you have. Find a balance between parallelising your work and not killing the DB :)

Parallel.ForEach(yourListOfStuff,
    new ParallelOptions { MaxDegreeOfParallelism = 10 },
    stuff => { YourMethod(stuff); }
);

Upvotes: 6

Related Questions