0

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?

  • Is there any good reason you aren't using SQL Server? – Colin 't Hart Jun 14 '17 at 12:32
  • @Colin'tHart Hi Colin, yes unfortunately the main system here is all on Access and I don't have full control. They plan to possibly move to SQL backend in the future, but not at this time. – Bassie Jun 14 '17 at 12:41
  • My suggestion would be to start pushing for the upgrade. Isn't it possible to attach an MDB to a SQL Server and share it that way? – Colin 't Hart Jun 14 '17 at 12:54
  • @Colin'tHart I'm not sure, I will have to check what is possible... Yes I agree that SQL will be a better option, but it's just me and 1 developer (Who is an Access specialist) so there isn't much enthusiasm to switch over from anyone else – Bassie Jun 14 '17 at 14:45
1

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

  • Thanks for your answer. When you say the performance plummeted, are you referring specifically to c# code accessing the DB, or do you mean the whole database slowed down? In my case, using the db with Access front end works just fine - its only slow when accessing via c# – Bassie Aug 22 '18 at 5:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.