Can a stateless WCF service benefit from built-in

Question

I understand that a typical .NET application that accesses a(n SQL Server) database doesn't have to do anything in particular in order to benefit from the connection pooling. Even if an application repeatedly opens and closes database connections, they do get pooled by the framework (assuming that things such as credentials do not change from call to call).

My usage scenario seems to be a bit different. When my service gets instantiated, it opens a database connection once, does some work, closes the connection and returns the result. Then it gets torn down by the WCF, and the next incoming call creates a new instance of the service.

In other words, my service gets instantiated per client call, as in [ServiceBehavior(InstanceContextMode = InstanceContextMode.PerCall)]. The service accesses an SQL Server 2008 database. I'm using .NET framework 3.5 SP1.

Does the connection pooling still work in this scenario, or I need to roll my own connection pool in form of a singleton or by some other means (IInstanceContextProvider?). I would rather avoid reinventing the wheel, if possible.

Answer 1

Typical WCF application that accesses a(n SQL Server) database doesn't have to do anything in particular in order to benefit from the connection pooling. Even if an application repeatedly opens and closes database connections, they do get pooled by the framework (assuming that things such as credentials do not change from call to call).

The service instancing model creates and tears down an instance of your class, not an entire appdomain. The SqlClient connection pool is per AppDomain, so you'll get your free lunch.

Answer 2

Even though this is an old post, I feel it is important to add to it.

ADO.NET database connection pooling does NOT work in per-call WCF services, if you follow the typical scenario (instantiating ADO.NET objects within the service object).

While I do understand the above theory and arguments, they are just that: theory.

A simple Windows form application which goes through the step of open, query, close multiple times will show you that the first Open() call takes quite long such as 2 or 3 seconds, and subsequent calls and queries are fast - the effect of connection pooling.

If you put identical code into a per-call WCF service, you get the 2-3 seconds delay on EVERY CALL, the first call and all subsequent calls. Conclusion - ADO.NET database connection pooling does NOT work in per-call WCF services if you do the typical ADO instantiation within the service.

You would have to instantiate ADO objects in a custom service host, and add appropriate synchronization code if you need, or live with no database connection pooling.