0

The title is sort of clear, I have two simple code:

using (var conn = new SqlConnection(connectionString))
{
    using (var command = new SqlCommand(query, conn))
    {
        await conn.OpenAsync();
        return await command.ExecuteNonQueryAsync();
    }
}

and

using (var command = new SqlCommand(query, connection))
{
    return await command.ExecuteNonQueryAsync();
}

Where in the second example connection is created and opened once in the constructor. So the first one is the practice promoted by everyone (ex. here and here) but I see that the second example runs around 50% faster.

Is is correct to keep an open connection as long as I need it (in each DAL class for example) or should I open and close it each time? If keeping an open connection is a good practice why is it not promoted? And finally what is the role of connection pool? Isn't it supposed to keep the connection open and alive for next use? Because when I run SQL profiler tool it shows that each connection.Open() causes an audit login in the SQL side.

Also see here that says exactly the opposite of what I see in the profiler. It says that connection.Open() should not result in a spid operation.

Emad
  • 3,809
  • 3
  • 32
  • 44
  • You should probably start by reading the [official documentation...](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling) – Zohar Peled Jun 02 '19 at 11:31
  • Be aware that the events you see in a Profiler/Extended Events trace are not necessarily network round trips. An open/execute/close using a pooled connection may be done a single round-trip where the trace will show logout, sp_reset_connection completed, login, and finally the batch/rpc request execution request. – Dan Guzman Jun 02 '19 at 11:58
  • @ZoharPeled I did already. it says "We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool" but (1) it doesn't say when "you are finished" happens, each time, or at the end of the class and (2) the evidence is in the contrary – Emad Jun 02 '19 at 13:05
  • @DanGuzman The process of login and logout takes time themselves and profiler shows exactly that. I thought connection pooling is the mechanism to avoid this overhead. If it's not then what is it? – Emad Jun 02 '19 at 13:39
  • Check the EventSubClass of the login event to see if the connection is actually pooled. With a pooled connection, the duration of the login reflects the time need to restore the connection to that of a new connection state (as done by sp_reset_connection) and is typically sub-millisecond. The time reflected by login is the total time the connection was open. – Dan Guzman Jun 02 '19 at 16:05

0 Answers0