I decided to hash together a quick and simple survey component for this blog (to be released publicly as open source as well). While the data access is via interfaces and dynamically instantiated helper classes, and thus data source agnostic, the first concrete class I decided to make was one that uses Microsoft Access as the back-end data store. For low-end uses like this the performance of MS Access/JET can be more than adequate (the non-transactional performance can actually be superior to SQL Server in some scenarios). Using the OLEDB ADO.NET components made pretty quick work of it, and it is refreshing to go back to such a simple, file-based data store, versus the SQL Server and DB2 architectures that I've been using for the past couple of years.

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

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

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