SqlConnection performance issues

  • Thread starter Thread starter Christoph
  • Start date Start date
C

Christoph

Where can I go to read about performance issues when
dealing the sql connections in ADO.NET? Specifically, I
am looking for some place that discusses the pros and cons
of using a single connection vs. using multiple connections.
We are wanting to write a wrapper class that handles all
the specific SQL functionality and we'd like to read more
(good, specific) information on sql connections in ADO.NET
so we can determine whether or not we should make the
wrapper handle just one connecion or make it work with
some sort of connection pool.

thnx,
Christoph
 
Here's a comparison of Connected vs. Disconnected ADO.NET objects
http://www.devx.com/vb2themax/Article/19887 This should help too
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenetchapt12.asp

As far as your wrapper class goes, don't worry about the One Connection vs
multiple. Remember that connection objects != connections to the db. This
is an oversimplification but there's no benefit to having only one
connection object and if you plan to do multithreaded data access (which you
should - moving your DataAccess to a seperate thread from the UI is often a
very good idea), you are begging for trouble if you have only one
connection. The thing to remember is CLOSE Those connections as SOON as you
are done with them. Otherwise they don't get returned to the pool and can't
be reused. Also, don't wait for dispose to deal with them indirectly, call
Close as soon as you are finished with them, or let the DataAdapter open the
connections on it's own (it will close them on it's own as well if you let
it open it).

Also, here are the performance guidelines regarding connections put forth by
the Patterns and PRactices group:


Connections
Check Description
Open and close the connection in the method.
Explicitly close connections.
When using DataReaders, specify CommandBehavior.CloseConnection.
Do not explicitly open a connection if you use Fill or Update for a
single operation.
Avoid checking the State property of OleDbConnection.
Pool connections.


Let me know if you have any further questions...

Bill

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
 
The Patterns & Practices team at Microsoft put together a great guide for
..NET application performance that includes a whole chapter on ADO.NET. I'd
recomend taking a look there for performance-related guidance.

You can download the guide from:
http://msdn.microsoft.com/library/en-us/dnpag/html/ScaleNet.asp?frame=true&_r=1

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top