Connection Pooling and Microsoft Access

I decided to hash together a quick and simple survey component forthis blog (to be released publicly as open source as well). Whilethe data access is via interfaces and dynamically instantiatedhelper classes, and thus data source agnostic, the first concreteclass I decided to make was one that uses Microsoft Access as theback-end data store. For low-end uses like this the performance ofMS Access/JET can be more than adequate (the non-transactionalperformance can actually be superior to SQL Server in somescenarios). Using the OLEDB ADO.NET components made pretty quickwork of it, and it is refreshing to go back to such a simple,file-based data store, versus the SQL Server and DB2 architecturesthat I’ve been using for the past couple of years.

Just to validate that Access could offer the desired low resourceusage, along with the capacity to handle the odd spikes in volume,one of the first tasks was a quick benchmark to test my datamethods. I was shocked to see how slow they were. As a bit ofbackground, I always strive for minimal resource usage, usingfeatures such as dispose patterns (keeping connections, commands,and other resources active for the minimal amount of time), relyingupon the connection pooling of the underlying data providers todecide when it was appropriate to tear-down connections. This iscritical in web apps and web services.

Anyways, long story short – Access, and the other OLEDB->ODBCdata providers, do not by default enable connection pooling. Whatthis means is that each time my method was hit it would reopen thefile, reorient itself, get the data, close and release the file,all to repeat it again on the next iteration. This is absolutelyterrible for scalability, which I saw demonstrated in my littletest.

What I discovered (and this is old hat for Access developers,obviously) is that you need to addOLE DBServices=-1 to your connection string. With this directive,OLEDB turns on connection pooling. The results in my test wereoverwhelming, and the performance increased dramatically. This isobviously old hat for people who regularly target Access, but forthe Access part timer it is probably a thief stealing theirperformance.