I am working on a few applications which connect to an MS Access database backend (.mdb) to read/insert/update records.

Everything is working fine, but I noticed that my db operations were quite slow. The backend is accessed by other users, but I still get the issue when querying a copy of the access file which no one else connects to.

I managed to narrow this down so that I can now see the offending code is the line

 connection.Close()

Called on an open OleDBConnection which has just executed some query, e.g:

var con = new OleDbConnection(connectionString);
con.Open();
var query = "SELECT * FROM subGRCReceived WHERE GRVNo=@grv";
var args = new DynamicParameters();
args.Add("@grv", grvNumber);
// Using Dapper
var pallets = (List<Pallet>)con.Query<Pallet>(query, args);
con.Close(); // This is often taking between 7-10 seconds

I can confirm that this is occurring when using using/con.Close()/con.Dispose(), and using or not using Dapper makes no difference.

I did notice that this only seems to happen with web based prjojects (ASP MVC or WCF soap service) and not Console applications. The issue seems to be intermittent, but occurs frequently enough for it to be a pain for the user (especially when navigating to a page uses 2-3 db queries, as this can take as long as 20 seconds to load).

The problem does not lie with the code itself, as I am able to host the same application on my laptop on the same network as the server and the speed is perfect (~200ms per request). See the specs of the 2 machines below:

Laptop Details:

• Processor : Intel Core i7-6700HQ CPU @ 2.60GHz
• RAM : 16.0 GB
• OS : Windows 10 x64

Server Details

• Processor : Intel Xeon CPU E5-2603 v4 @ 1.70GHz (2 processors)
• RAM : 32.0 GB
• OS : Windows Server 2012 x64

Setup

• 32 bit MS Access 2016
• 2016 Microsoft ACE OLE Engine
• 64 it OS

What I have tried

• Moved the database to same folder as application
• Disabled antivirus (ESET)
• Increased the MaxBufferSize key value from 0 in Access Connectivity Engine in the registry (does this need a restart?)
• Measure the time it takes to run GC.Collect() before calling Close() to ensure it is not the garbage collector

Workarounds

Threading

I tried calling Close() from a new thread, which seemed to work after 1 request, but if I try accessing the application again I am getting unhandled win32 errors on the server (Even though I wrapped my thread and connection.Close() calls in try/catch. I suspect this might be failing because the thread might take 7 seconds to close the connection, but the IIS worker process gets terminated before that, so there may be some missing resources that Close() needs. It might be nice if I could get this working, but I understand that this i bad practise in MVC, and also does not actually solve the issue.

Persistent Connection

I could also just have 1 OleDBConnection and keep it open throughout the session. I did this with the WCF service (1 connection per request) and it works find, however I get the feeling that it wont work quite as well with ASP MVC, and after doing a bit of research it looks like this is not a good idea.

I have been struggling with this for a week now and its driving me crazy, does anyone have any advice at all?