CODEWITHSUNDEEP
asp.netsql-serverdatabasemultithreadingconnection-pooling
user1703963
user1703963

Reputation: 25

SQL Server, connection pools vs static connection for special cases

I sort of know the answer to this, but cannot really grasp the underlying concept. I know you are always instructed to use connection pooling now. But imagine this scenario.

I need to read data from one database, and one table, multiple times.

Connection pooling is going to inject microseconds of overhead, but why not eliminate that by using a single connection for everything and locking around that?

Since it is one database, with one table. Isn't it pretty unlikely that we will be able to get any performance boost from multithreaded connection pools?

Just hoping for some clarity here. And maybe some simple resources which would explain WHY, connection pooling ALWAYS is better.

Thanks. I know this is not the greatest question, and I appreciate your time. I am specifically in the .net environment, but this is a basic concept across programming correct?

Upvotes: 1

Views: 264

Answers (1)

usr
usr

Reputation: 171178

  1. With one global connection you need to be prepared to handle spurious connection failues. Those can always happen (network hiccup, ...).
  2. You absolutely do get concurrency when using multiple concurrent statements against a single table. SQL Server does not usually lock tables exclusively (exceedingly rare).
  3. You will forget to use the synchronization protocol somewhere (lock everywhere). You will get it wrong eventually and have to fight races.
  4. If you have a slow runaway query that would block the entire app. It will appear "hung" to browsers.
  5. You serialize all HTTP requests on the global lock. You only use one CPU. You won't scale at all. Your app will not handle burst well.

Having a single global connection is really a bad idea. Why not just use pooling? That saves you the development work of using synchronization. It is even less work.

Of course, pooling is not always better. You can construct pathological cases where it isn't. I never encountered a case where I needed to keep a connection open for longer than the current HTTP request, though.

Upvotes: 2

Related Questions