What are the implications of using "Usage" in sqlConnections
I came across an article saying that with my sqlConnection like this:
using (SqlConnection sqlConnection = new SqlConnection(Config.getConnectionString()))
{
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(query, sqlConnection))
{
dataAdapter.Fill(dataSet);
}
}
improves performance because it places objects at the end of your method. So I have been programming Usage for a while now, after talking with some other developers, they said that instantiating and destroying an instance multiple times would not improve performance.
What are the performance implications for sqlserver and system resources if I use "Usage" for all my dataAccess methods. Will sqlServer hit harder because the connection is connected and reconnected multiple times?
source to share
SqlConnection
, connection pooling is enabled by default. Dispose()
just releases the pool connection earlier. This means that other code can reuse this connection, shortening connections to the SQL server and shortening the time it takes to establish a physical connection.
So yes: it can improve overall performance.
Alternatives:
- if your code breaks and you always remember the
Close()
connection then there probably is no difference - if your code throws an exception (which you didn't handle), or you forgot before
Close()
connecting, then you can leave unused connections lying around until there is enough memory pressure to run the GC and finalizer. This could mean that you need more physical connections to the SQL server (a pain) and every time a new underlying connection is required it must take a performance hit when establishing the actual database connection.
In general though - think of it IDisposable
as a contract; your work as a .NET developer notices the resources IDisposable
and proactively picks Dispose()
them up when you're done, ideally with using
if your use is severely limited (as in this case).
source to share
In most cases, it does not significantly affect performance.
The whole using () ... construct ensures that it is SqlConnection
deallocated / disposed of after its work. That's all there is - no magic performance boost ....
Of course - creating and deleting objects costs a little performance - either that, or then you unnecessarily keep the objects in your memory and the connections to your SQL Server open much longer than necessary.
I would vote for using the approach using() {...}
100% of the time - it is cleaner, safer, it just improves programming practice. The impact you can take is minor and not worth the trouble.
Mark
source to share
If you are performing multiple database operations one after the other, you should use the same connection instead of creating one connection for each. Otherwise, you must close the connection as soon as possible so that it is returned to the connection pool and can be reused.
You should always use the using
connection block so that you can be sure that they are closed properly. If you cannot close the connection object, it will remain in memory until the garbage collector deletes it without starting the database connection. This means that the next guard cannot reuse a pooled connection, but it must establish a completely new connection, which takes much longer.
source to share
there is an improvement in performance.
if you are using using
For example:
using (SqlConnection sqlConnection = new SqlConnection("ConnectionString"))
{
}
The compiler will automatically add try and finally
source to share