Usage of Connection Pooling: What are the best practices?

[Mithun Verma]

Hello All,

I am working on a windows application which will talk to the database

through the Web services.



So i need to enhaance the performance, for which i m using connection

pooling.



I want to use pooling at the Web services end and not the default SQL server

pooling.



i have created a connection pool component which maintains a pool of

sqlconnection objects.



When a request for connection comes:

1. It checks the pool for open connection which are not currently in use. If

it finds one it returns it.

2. Else it creates a new connection object , opens it and adds it to the

pool.



Now my problem is that if i plan to ve a pool of 100

connections............if at some instant i ve 100 requests i ll return all

the avaialable connection objects



But this scenario was an exterme one and in most cases not more than 50

connections will be in use. So in that case i need to close the open

connection if it is unused for a given amount of time.



For that i created a thread which keeps checking if any of the connection

object timed out and if it has timed out then close it.



Will this approach be useful performance wise??



Will an extra thread running affect the applications perforamance??



Is there any other solution to this??



Is application level pooling needed in my application and will it help

improve the performance??



Please reply ASAP.



Regards,

Mithun

 

[Patrice Scribe]

It looks like to me like what SQL Server does ? What is the benefit to do it
yourself ?

Are you sure these connection issues are the problem ? For example if the
service is often called with the same expected results you could cache the
results so that they are available for a next request without having ot hit
the database...

Patrice

 

[William \(Bill\) Vaughn]

The Connection pooling built into ADO and ADO.NET data providers (and other
data access interface providers) does not occur at the server--it occurs at
the source--in the client or on the web server where the connection is
opened. It works pretty much as you describe--automatically.

See my article on my web site http://www.betav.com/sql_server_magazine.htm
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________