Connection pooling is slower than keeping one connection open

Question

I have observed an interesting behavior of the performance of connection pooling in an client application we created. When ever the user clicks on an object, more object specific data is loaded from the database. This takes somewhere between 10 and 30 queries per click depending on the object.

This was done by using connection pooling and each query was dispatched on a new connection from the pool and the connection was closed after the query ran.

I have analyzed the queries in the profiler for performance optimization and saw that there where a lot of audit login/logout entries. Additionally the performance was not optimal eventhough the queries themselfs where running well (only index seek/scan operators).

Just for trying it out I disabled the pooling and modified the code to keep one connection per client application and reusing it. This made the entire application a lot more responsive, and all the audit login/logout entries disappeared from the profiler.

How is this possible? Shouldn't the connections stay open or if they actually stay open at least not be this slow? Is it possible that we are using the SqlConnection class wrong resulting in disabled pooling?

I have read the other posts regarding pooling but have not found anything about a perceivable speed difference between pooling connections and reusing the same connection.

SqlConnection con = new SqlConnection(_connectionString);

The connection is handed off to a wrapping class Session which provides transactional functionality.

class Session{
    Session(connection);
    Abort();
    Commit();
}

The connection is closed in Abort() and Commit(). One of these is always called.

Could you post working example of the issue you're experiencing? — empi, Jul 26 '13 at 15:09
That is difficult. For one it is proprietary code from my employer. For another it is embedded in a large application. — caldis, Jul 26 '13 at 15:12
Do you use a consistent connection string for all connections? i.e. format. — Daniel Kelley, Jul 26 '13 at 15:13
Can you show how you are establishing / opening / using / closing the connections? — Aaron Bertrand, Jul 26 '13 at 15:13
This is a total guess......but years ago I remember something. Take a look at : SqlConnection.ClearAllPools() ....... and maybe add that to your test (pre-test) code. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.clearallpools.aspx — granadaCoder, Jul 26 '13 at 15:19
and maybe the singular version : http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.clearpool.aspx — granadaCoder, Jul 26 '13 at 15:20
http://stackoverflow.com/questions/279401/sql-connection-pooling-and-audit-login-logout - Some good background. — Keith Payne, Jul 26 '13 at 15:20
From the previous link that I posted: While the MSDN article says that the event will only be raised for non-reused connections, the SQL Server documentation contradicts this statement: "The Audit Login event class indicates that a user has successfully logged in to Microsoft SQL Server. Events in this class are fired by new connections or by *connections that are reused from a connection pool."* — Keith Payne, Jul 26 '13 at 15:23
@KeithPayne I have read that. That is not the reason why I suspect that the pooling does not work. The only reason I suspect that, is that the performance increased dramatically when I changed the connection handling. — caldis, Jul 26 '13 at 15:24
This is a long shot, but [check this](http://www.digitallycreated.net/Blog/48/entity-framework-transactionscope-and-msdtc) regarding transactions unintentionally being promoted to a DTC which might result in noticeable performance decrease. — lightbricko, Jul 26 '13 at 15:26
@lightbricko This seems possible. We are currently using 2008R2 and .Net 3.5 — caldis, Jul 26 '13 at 15:29
@caldis simply open the connection manually as suggested in the link and see if that helps. — lightbricko, Jul 26 '13 at 15:31
Can you give some details around "lot more responsive"? Obviously using a single open connection object cannot be slower than using multiple connection objects being pooled behind the scenes. Can you atleast quantify the difference / speedup? — Vivek, Jul 26 '13 at 15:34
@Vivek Hardly. Loading a list of Objects within a folder which took approximatly 5 seconds is now reduced to < 1 second. As the application is not multithreaded at no time there were multiple threads using connections simultaneously. There was always just one connection dispatching queries. But that connections was open and closed all the time. — caldis, Jul 26 '13 at 15:39
@caldis My point exactly. It's *near impossible* that only SQL connection overheads can be taking 4 out 5 seconds of any operation. Can you isolate that the SQL connection calls took 4 seconds in the old code? — Vivek, Jul 26 '13 at 16:16
@Vivek as I just departed into the weekend I won't be able to debug this until monday. — caldis, Jul 26 '13 at 17:21

score 0 · Answer 1 · answered Jul 28 '13 at 01:16

If I understand you correctly - the connection is being "new" per session. if you want all instances to share the connection you should make it static.

put it in global.asax:

public static SqlConnection con;

protected void Application_Start(object sender, EventArgs e)
{
    con = new SqlConnection(_connectionString);
}

in that way you will be sharing the same connection between your sessions.

Connection pooling is slower than keeping one connection open

1 Answers1

Linked