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.
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)
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
^^^^^
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?)
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.
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