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

[Steve White [MSFT]]

It's recommended that you make use of the SqlProvider connection pool which
is included in the .NET Framework. This has undergone extensive performance
and scalability testing and it is unlikely a custom implementation of a
connection pool would see significant improvements in these areas.

FYI in order to leverage the built-in connection pool you need to always use
the same connection string - this implies the same user account is used to
authenticate against the database. Check out this link:
http://msdn.microsoft.com/library/de...taProvider.asp

--
Provided by the Microsoft PSfD UK team.
This posting is provided "AS IS" with no warranties, and confers no rights.

"Mithun Verma" <mi*****@MAQSoftware.com> wrote in message
news:O6******************@TK2MSFTNGP10.phx.gbl...

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