What are the performance implications of using 'Using' on sqlConnections

Question

I came across an article saying that using my sqlConnection like this :

using (SqlConnection sqlConnection = new SqlConnection(Config.getConnectionString()))
            {
                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(query, sqlConnection))
                {
                    dataAdapter.Fill(dataSet);
                }
            }

increases performance because it disposes the objects at the end of your method. So i have been coding with 'Using' for a while now, after chatting with some other developers they said that that creating and destroying the instance multiple times wont really increase performance.

What are the performance implications on the sqlserver and system resources if I am using 'Using' on all of my dataAccess methods. Will the sqlServer be hit harder because of the connection being connected and reconnected multiple times?

Answer 1

It has no significant influence on performance in most cases.

All the using().... construct makes sure is that the SqlConnection is freed / disposed of after it's done its job. That's all there is - no magic performance boost....

Sure - creating and disposing objects does cost a bit of performance - it's either that, or then you unnecessarily keep objects in your memory and connections to your SQL Server open for much longer than needed.

I would vote for using the using() {...} approach 100% of the time - it's cleaner, it's safer, it's just better programming practice. The performance "hit" you might take is miniscule and not worth the trouble.

Marc

Answer 2

SqlConnection, by default, has connection pooling enabled. The Dispose() simply releases the connection to the pool sooner. This means other code can then re-use this connection, reducing the connections to the SQL server, and reducing the time to establish a physical connection.

So yes: it can improve overall performance.

The alternatives:

• if your code exits cleanly and you always remember to Close() the connection, then probably no difference
• if your code throws an exception (that you haven't handled), or you forget to Close() the connection, then you could be leaving unused connections lying around until there is enough memory pressure to trigger GC and the finalizer. This could mean you need more physical connections to the SQL server (a pain), and every time a new underlying connection is needed it has to take the performance hit of establishing the actual database connection

Overall, though - think of IDisposable as a contract; it is your job as a .NET developer to notice IDisposable resources, and actively Dispose() them when you are done, ideally with using if the usage is tightly scoped (like in this case).

Answer 3

It increases performance only in the sense that, after your connection instance has been disposed, the physical connection in the pool can be re-used by another thread. If you kept it open, then another thread trying to open a connection would add a new physical connection to the pool.

Answer 4

ADO.NET has such feature as connection pooling, therefore if you intensively open connections, most likely connection will not be disposed, only returned to the pool.

Answer 5

If you are doing several database operations after each other, you should use the same connection instead of creating one connection for each. Otherwise you should close the connection as soon as possible, so that it is returned to the connection pool and can be reused.

You should always use a using block for the connection, so that you are sure that they are closed properly. If you fail to close a connection object it will stay in memory until the garbage collector removes it, hogging a database connection. That means that the next opreation can't reuse the connection from the pool but it has to establish a completely new connection, which takes a lot longer.