Connection pooling is slower than keeping one conn

问题:

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.

回答1:

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.