Connection pool is slower than saving one connection

Question

I observed an interesting behavior of the connection pool performance in the client application that we created. When a user clicks on an object, more specific objects are loaded from the database. It takes from 10 to 30 requests per click, depending on the object.

This was done using the connection pool, and each request was sent to a new connection from the pool, and the connection was closed after the request was completed.

I analyzed the queries in the profiler to optimize performance and saw that there are a lot of audit entry / exit records. In addition, performance was not optimal even if the queries performed well (only index search / scan operators).

Just to verify this, I turned off the association and changed the code to save one connection to the client application and reuse it. This made the application more susceptible, and all audit entry / exit records disappeared from the profiler.

How is this possible? Should the connections remain open, or if they really remain open, at least not so slowly? Is it possible that we used the SqlConnection class incorrectly, which led to the pool being disabled?

I read other messages about the pool, but found nothing about the perceived speed difference between joining connections and reusing the same connection.

SqlConnection con = new SqlConnection(_connectionString); 

The connection is passed to the wrapping Session class, which provides transactional functionality.

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

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

Answer 1

If you understand correctly, the connection is "new" per session. if you want all instances to share a connection, you must make it static.

put it in global.asax:

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

this way you will use the same connection between sessions.