OT: Performance with ADO.NET (OLEDB) + Access .MDB file is terrible

OT: Performance with ADO.NET (OLEDB) + Access .MDB file is terrible

Old forum URL: forums.lhotka.net/forums/t/6451.aspx


rsbaker0 posted on Wednesday, February 18, 2009

Our application is somewhat database agnostic, and the legacy product we are replacing supports several versions of Access, SQL Server, and Oracle.

The new CSLA-based product performs *abysmally* with an Access .MDB file. Before you reply, "of course it does" ;) this is true even with a very small database on a single machine.

The problem seems to be in "round trips". Any individual query can be handled OK, but the application is currently chatty and processing of multiple queries -- even those that return just a single row -- is at least an order of magnitude slower than accessing a similar database on SQL Express over the network. We can easily sustain 50 or more queries/second for cached data on a SQL Express box. The same sequence can take 5-10 times longer even on the same machine with an .MDB file.\

With the legacy application, performance with a small .MDB file would be quite snappy in a modest single machine environment.

Anyone have any thoughts on tweaks that might help? The entire .MDB file would fit into memory 100 times over, so I don't understand why read performance that doesn't involve the network would be so bad.

Fintanv replied on Wednesday, February 18, 2009

Are these queries all returning editable data or read-only data.  If the latter would a caching strategy help?

rsbaker0 replied on Wednesday, February 18, 2009

Some caching might help, but overall it's still not going to be really usable.

Unless I can improve it, I'm going to deprecate support for Access (or at least flag it as not recommended).

Still, the performance difference from the legacy application is amazing. I know ADO.NET will not match DAO for a .MDB file, but I didn't expect it to literally be 10 times (or more) slower.

(With SQL Server/Oracle, the new application is faster than the legacy app)

JoeFallon1 replied on Thursday, February 19, 2009

I use an .mdb for logging hits on my web site.

I use ADO.Net code to insert records. It seems fine. I only have a single page that pulls data out for review but it too seems fine. Makes me wonder if there is something else going wrong in your environment.

Not sure what advice to give you - just want to say that I don't think this combination (Access and ADO.Net) is really as bad as you are making it out to be. At least not for everyone.

Joe

 

rsbaker0 replied on Thursday, February 19, 2009

^^^^^

A single fetch works OK, even for returning a medium amount of data. It's multiple simple queries (keyed reads of various tables) that seem slow. It's not just my machine either -- this has been repeated on others.

Are you using parameters in your insert? How about any interesting values in the connection string? (And what version .MDB file is it?)

ajj3085 replied on Monday, February 23, 2009

Maybe this is a stupid question, but did you check the indexes on your tables?  Also, did you try the repair / compact options?  You need to run those from time to time, IIRC.

rsbaker0 replied on Monday, February 23, 2009

No suggestion that might help is stupid. ;)

Yes, the database is indexed and freshly compacted.

My naive take on the problem is that the "query plan" (such as it is with an Access .MDB) isn't being cached or saved and that the performance problem might be caused by the a complete reparsing of repetitive identical queries versus some sort of caching.

Maybe I can post some metrics that would illustrate.

FatPigeon replied on Friday, February 27, 2009

Are you opening a new connection on each query - I dont think access pools connections so if you could hold a connection open and use it for a number of queries then things may speed up.

 

 

Copyright (c) Marimer LLC