OleDBConnection.Close() very slow on server 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&lt;Pallet&gt;)con.Query&lt;Pallet&gt;(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?
In my experience,

One of the access database exceeded 20000 records and the size was around 150 MB. It started to slow down and performance plummeted. So moved to SQL.

You may consider using SQL compact edition, or sql lite