CODEWITHSUNDEEP
c#sql-server-2008
Shaul Behr
Shaul Behr

Reputation: 38101

Having lots of open connections vs repeatedly opening and closing them

I'm investigating some performance issues in our product. We have several threads (order of tens) that are each doing some form of polling every couple of seconds, and every time we do that we are opening a connection to the DB, querying, then disposing.

So I'm wondering, would it be more efficient for each thread to keep an open connection to the DB so we don't have to go through the overhead of opening a connection every 2 seconds? Or is it better to stick with the textbook using blocks?

Upvotes: 1

Views: 444

Answers (3)

Chris Townsend
Chris Townsend

Reputation: 3162

As mentioned, connection pooling should take care of it but if you are beating on the database with messaging or something like that to check on the status of things every few seconds then you could be filling up the database pool very quickly. If you are on SQL Server, do an SP_WHO2 on the database in a query window and you'll see a lot of information: number of spids (connections open), blocking, etc.

Upvotes: 1

Piskvor left the building
Piskvor left the building

Reputation: 92792

In general, connection setup and teardown is expensive; doing this multiple times in tens of threads might be crippling; note however that the solution you use might already be pooling connections for you (even if you're not aware of it), so this may not be necessary (see your manuals and configuration).

On the other hand, if you decide to implement some sort of connection pooling by yourself, check that your DB server could handle tens of extra connections (it probably should).

Upvotes: 0

Henk Holterman
Henk Holterman

Reputation: 273844

First thing to learn about is Connection pooling. You're already using it, don't change your code.

The question becomes: how many connections to claim in my config file?
And that's easy to change and measure.

Upvotes: 3

Related Questions