ASP.NET - Pooling in Connection string - Asked By Naresh Kumar on 01-Dec-10 01:46 AM

Hi All,

Can I use Pooling if I use windows authentication in the application.

<add name="ConnectionString" connectionString="server=AAAAA;database=BBBBB;Integrated Security=true poooling=true; Max Pool size = 200; Min pool size=0 " providerName="System.Data.SqlClient"/>

Please let me know the issues if I use pooling in this case, or I could not find any issues with this.

Thanks in advance.

Danasegarane Arunachalam replied to Naresh Kumar on 01-Dec-10 02:07 AM
Yes, you can use the connection pooling with Windows Authentication.
Reena Jain replied to Naresh Kumar on 01-Dec-10 02:38 AM
hi,

Opening a database connection is a resource intensive and time consuming operation. Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. Connection pool manager maintains a pool of open database connections. When a new connection requests come in, the pool manager checks if the pool contains any unused connections and returns one if available. If all connections currently in the pool are busy and the maximum pool size has not been reached, the new connection is created and added to the pool. When the pool reaches its maximum size all new connection requests are being queued up until a connection in the pool becomes available or the connection attempt times out.

Connection pooling behavior is controlled by the connection string parameters. The following are four parameters that control most of the connection pooling behavior:

  • Connect Timeout - controls the wait period in seconds when a new connection is requested, if this timeout expires, an exception will be thrown. Default is 15 seconds.
  • Max Pool Size - specifies the maximum size of your connection pool. Default is 100. Most Web sites do not use more than 40 connections under the heaviest load but it depends on how long your http://www.15seconds.com/issue/040830.htm# operations take to complete.
  • Min Pool Size - initial number of connections that will be added to the pool upon its creation. Default is zero; however, you may chose to set this to a small number such as 5 if your application needs consistent response times even after it was idle for hours. In this case the first user requests won't have to wait for those database connections to establish.
  • Pooling - controls if your connection pooling on or off. Default as you may've guessed is true. Read on to see when you may use Pooling=false setting.
so modify your connection string
<add name="ConnectionString" connectionString="server=AAAAA;database=BBBBB;Integrated Security=true poooling=true; Max Pool size = 100; Min pool size=0 " providerName="System.Data.SqlClient"/>

here is  a good link to understand this
http://msdn.microsoft.com/en-us/library/8xx3tyca%28VS.71%29.aspx

hope this will help you
Manoranjan Sahoo replied to Naresh Kumar on 01-Dec-10 03:17 AM
yes, you can use connection pooling when using windows authentication.
Connection Pooling can increase the database performance to a huge extent.
Connecting to the database is resource intensive and a relatively slow operation in an application but the most crucial of them all. A Connection Pool is a container of open and reusable connections. A Connection Pool is released from the memory when the last connection to the database is closed. The basic advantage of using Connection Pooling is an improvement of performance and scalability while the main disadvantage is that one or more database connections, even if they are currently not being used, are kept open. The Data Providers in ADO.NET have Connection Pooling turned on by default; if you need to turn it off, specify Pooling = false in the connection string being used. Connection Pooling gives you an idle, open, reusable connection instead of opening a new one every time a connection request to the database is made. When the connection is closed or disposed, it is returned to the pool and remains idle until a request for a new connection comes in. If we use Connection Pooling efficiently, opening and closing of connections to the database becomes less resource expensive.

for more details check below links :
http://www.codeproject.com/KB/aspnet/simplecodes.aspx
http://aspalliance.com/1099