Tip 3—Connection Pooling
Setting up the TCP connection between your Web application and SQL Serverâ„¢
can be an expensive operation. Developers at Microsoft have been able to take
advantage of connection pooling for some time now, allowing them to reuse
connections to the database. Rather than setting up a new TCP connection on
each request, a new connection is set up only when one is not available in
the connection pool. When the connection is closed, it is returned to the
pool where it remains connected to the database, as opposed to completely
tearing down that TCP connection.
Of course you need to watch out for leaking connections. Always close your
connections when you're finished with them. I repeat: no matter what anyone
says about garbage collection within the Microsoft® .NET Framework, always
call Close or Dispose explicitly on your connection when you are finished
with it. Do not trust the common language runtime (CLR) to clean up and close
your connection for you at a predetermined time. The CLR will eventually
destroy the class and force the connection closed, but you have no guarantee
when the garbage collection on the object will actually happen.
To use connection pooling optimally, there are a couple of rules to live by.
First, open the connection, do the work, and then close the connection. It's
okay to open and close the connection multiple times on each request if you
have to (optimally you apply Tip 1) rather than keeping the connection open
and passing it around through different methods. Second, use the same
connection string (and the same thread identity if you're using integrated
authentication). If you don't use the same connection string, for example
customizing the connection string based on the logged-in user, you won't get
the same optimization value provided by connection pooling. And if you use
integrated authentication while impersonating a large set of users, your
pooling will also be much less effective. The .NET CLR data performance
counters can be very useful when attempting to track down any performance
issues that are related to connection pooling.
Whenever your application is connecting to a resource, such as a database,
running in another process, you should optimize by focusing on the time spent
connecting to the resource, the time spent sending or retrieving data, and
the number of round-trips. Optimizing any kind of process hop in your
application is the first place to start to achieve better performance.
The application tier contains the logic that connects to your data layer and
transforms data into meaningful class instances and business processes. For
example, in Community Server, this is where you populate a Forums or Threads
collection, and apply business rules such as permissions; most importantly it
is where the Caching logic is performed.
From
http://msdn.microsoft.com/msdnmag/is...erformance/#S4
"Steve Ricketts" wrote:
Thanks for the reply! The connection string looks like:
Provider=SQLOLEDB;Data Source=xx.xx.xx.xx; Initial Catalog=MyDB; User ID=sa;
Password=somepassword;
Just one connection string for all users. I haven't tried opening and
closing the connection but I will do so.
This is the run-time environment for a web based training application. Each
page of a training "course" may access the web service 10-30 times per page
and there are thousands of students that could be accessing it concurrently.
It doesn't seem to make sense to open and close the database connection when
everone that makes a request to the web service will want to read or write
into the database. The clients make a request to the service and receive a
reply. No state information is maintained so I couldn't say whether this is
a "disconnected" architecture or not. To the client it is, to the database
it's not.
I'll let you know what the open/close test shows.
Thanks again,
Steve
"Eugen" <Eu***@discussions.microsoft.comwrote in message
news:F7**********************************@microsof t.com...
Hi Steve,
How does your connection string looks like? Does it change for each user
that connects to the database? Or are you using a dedicated account to
connect to SQL Server? Have you tried to open and close the connection
with
each call to the database and look at the connections created? And why do
you
need to keep a connection open for the lifetime of the web service? This
doesn't correspond to the ADO.NET disconnected architecture, right?
"Steve Ricketts" wrote:
I have a webservice written in VB.net that opens a connection to SQL
Server
2000 with a connection string. The connection remains open for the life
of
the web service so the ADODB.connection is defined as public and used by
a
single subroutine, SQLexecute. All recordset definitions are defined in
the
individual subroutines and destroyed before exit of the subroutine. I
would
expect only one connection to be made to the SQL Server but when I access
the webservice it appears that EVERY read or write to the database
creates a
new connection and uses a new local port!
I used TCPview to look at the ports being used and sp_who to look at the
SQL
spid's and there are hundreds of connections established after only a
couple
of sessions with the web service. I counted the number of reads and
writes
to the SQL Server and they are essentially the same number as the number
of
open (established) ports to the SQL server. It's as if it creates a new
connection for every read or write and never closes them.
Can anyone shed any light on why these connections are being created and
how
to stop this from happening?
Thanks for any direction you can provide.
Steve
