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
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
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:
- Processor : Intel Core i7-6700HQ CPU @ 2.60GHz
- RAM : 16.0 GB
- OS : Windows 10 x64
- Processor : Intel Xeon CPU E5-2603 v4 @ 1.70GHz (2 processors)
- RAM : 32.0 GB
- OS : Windows Server 2012 x64
- 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
MaxBufferSizekey value from 0 in Access Connectivity Engine in the registry (does this need a restart?)
- Measure the time it takes to run
Close()to ensure it is not the garbage collector
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.
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?