connection pooling

Posted on 2005-03-30
Medium Priority
Last Modified: 2008-06-25
this term has been passed around our development team, we have heard that connection pooling is the route we should take for our site application.
The site app will have multiple users hitting the our SQL server simultaneously, how does connection pooling work and should i use it?
Question by:takingstandup
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 20

Assisted Solution

ihenry earned 120 total points
ID: 13669449
William Vaughn has two great articles about this SQL Server connection pooling

Swimming in the .NET Connection Pool

The .NET Connection Pool Lifeguard

And more from MSDN

Connection Pooling for the .NET Framework Data Provider for SQL Server

Connecting to an OLE DB Data Source Using ADO.NET

Connecting to an ODBC Data Source Using ADO.NET

Accepted Solution

jay_eire earned 1200 total points
ID: 13669481
Hi takingstandup,
If you plane to have hundreds of users hitting your Web site simultanesusly, and your opening a database connection for each user this can have a BIG impact on the your servers performance.
So you have whats called connection pooling, basically When database connections are pooled, a set of connections is kept open so that they can be shared among multiple users. When you request a new connection, an active connection is removed from the pool. When
you close the connection the connection is placed back in the pool.

Connection stings
For you to use connection pooling, you must ensure that your ASP.NET pages use the same exact connection string whenever you open a database connection, only connections opened with the same connection sting can be placed in the same connection pool.
Connections are pooled only when they are opended with connection strings that exatly match character by charcter. So its a good idea for you to create your connect string in one place and use this same connection within all your ASP.NET pages.
So put your connection string in the web.config file and retrieve it from this file whenever you need to open a connection.

closeing your connection
you have to explicitly close whatever connection you open as quickly as possible, if you dont explicity close a connection the connection is never added back into the connection pool so use the Close() method.

theres a few options you can add into a SQL server connection string that will determine how it will work............
Connection Lifetime - you can destroy a connection after a certain amount of time
Connection Reset - this indicated wheater connections shoulbe rest when the are returned to the pool
Max Pool Size - lets you state the maximum number of connections allowed in a single connection pool, by default its set to 100
Min Pool Size -lets you set the minimum number of connections allowed in a single connection pool, by default its set to 0
Pooling - this is the important one, its determines wheater connection pooling is enabled or disabled.

hope this helps
Jay Eire

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question