• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

MS Access Efficiency, or Lack of Same

    I had thought to use MS Access for our small installations, and offer MS SQL Server for the larger installations. But I'm getting some absolutely ridiculous response times with MS Access databases (accessed on a server via ADO using a client-side cursor), and am beginning to seriously doubt whether it is a practical proposition for a real-world, heavy-duty application.
     Specifically, I fetch a 300-byte Access record by its primary (sequential number) key and display it. I test efficiency by stepping through the file sequentially. When I fetch one record per step, response is (barely) adequate. When I fetch two records per step, response is over a second, with a single user in development mode. I do not consider that even marginally acceptable. God help our poor users when the application is complete (with many more records accessed), and they're fighting each other for server access.
     I am seriously considering discarding MS Access and going with CodeBase or Foxpro or MySQL or some other database which yields at least moderately sensible response times.
     I had heard that MS Access was a dog, but this is ridiculous! Does anyone have any real-world experience to share concerning the selection of a DBMS?
1 Solution
You must have some major network problems, as ALL of my 50+ users are using Access-97 as the backend for either VB front-end apps, or Access Front-end database apps(Split DB with forms/queries/Reports in Front end and linked to Tables in the Back-end), and no one complains about any performance issues.

I have no direct knowledge of how many users are hitting any one database at any point in time(there are currently 9 different apps running - 9 different Databases) but no one has yet to complain about performance.

The problem is *not* MSAccess it is ADO
if you use DAO and access MSAccess as an isam db it is *much* faster then SQLServer
i have a msaccess db on a server here and it is simultaniuosly acssesed by up to 30 users at the same time and there is no problem at all and at the moment the customer table has over 500 thousand records and the invoice table well over 700 thousand records
and it is a 10 mb network
if you use ADO with any other db then SQLServer, and on top of that you use these db's as if they were client-server db's you'l have unacceptable performance with any db, and that includes such speed monsters as foxpro
the bottom line is to get the best performance from any db you have to use its call-level interface
and universal data access as good as it sounds and as much as it is hyped by ms, just does not cut it.
it is possible to have acceptable performance with an access db using ADO but then the code wil *not* run against SQLServer
and if you code with ADO specifficaly for SQLServer it will not run againts access
and if you code with ADO for both db's you'l be doing injustice to both
if your program has to be used againts SQLServer and against a cheap(free) db use the MSDE

GebhartBobAuthor Commented:
    Holy Cow! Is it really true that ADO is grossly inefficient? Microsoft is touting it to the skies, so I figured it was the wave of the future, and used it.
     Does everybody agree that one must use DAO to get acceptable performance out of MS Access (or, apparently, any other database)?
     If that's true, it's back to the ol' drawing board for yours truly! Rats!
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I am using tons of ADO in my VB API, with Access 2000 as back end in my Win2K Server, the speed is sucks.

Perhaps i should consider SQL Server, do you agree ?
>>Does everybody agree that one must use DAO to get acceptable performance out of MS Access (or,apparently, any other database)?<<
That is not what i said
if you want *very* fast performance from access you have to use DAO and do *not* use DAO against access as if access was a client-server db
use it as an ISAM db
ie manipulate its indexes and use seek
for most things a user needs to do interactively you do not need SQL
example if a user wants to browse every customer who lives in new-york do not run a query (select * from customers where city ='new-york')
but say customers.index="city"
customers.seek "=","new-york"
and now every movenext you do will be a customer from new-york and the beauty of this is that the user has the impression of always working with every record in the table while in reality you are always getting just 1 record from the table
suppose you have a 1 million customers in a table can you image the users amazemend when you sort this table immediately not 1 second but immediately on any field in any order, you of cource did not sort anything you just used another index
DAO is the fastest against msaccess *if msaccess is used as an ISAM db*
on the other hand if you use msaccess as if it was a client-server db DAO will be *very* slow
you can use access as an ISAM db via ADO to and its speed will be very acceptable
if you use msaccess as if it was a client-server db ADO will be faster then DAO
the bottom line is
if you want the best speed from *any* db you'l have to use its call-level interface and use it as it is intended to be used
against access thats DAO
againts SQLServer or MSDE thats ADO (i think since db-library does not seems to be used anymore)
againts oracle use oracle tools
againts paradox use paradox tools
againts foxpro use foxpro
if you need to have 2 database engines
I think your best bet is ADO used against SQLServer and MSDE
what i mean to say is:
if you use VB and you need a cheap version of your program as well as an expensive version and you want acceptable performance in both versions and you want one code base your natural choise seems to be ADO,SQLServer,MSDE
I created a performance analysis for MS Access using DAO.  I am about to modify it to produce results for ADO. See question:


The example creates a file with 10 fields and 100,000 record in less than 4 seconds.

I will have the comparison for DAO & ADO Access vs SQL completed by next Tuesday. (I am out of office till then).

My general feeling is that DAO is about 50 times quicker than ADO.

If you use DAO and use the OpenTable and manual specification of indexes the record finding is quick.


Set DB=DBEngine.OpenDatabase("C:\MyDB.MDB")
Set RS=DB.OpenRecordset("MyTable",dbOpenTable,dbReadOnly)

RS.Seek "=", MyKey$

The reason for using SQL is that an Access DB is vulnerable on a Network as each PC is responsible for its own updates.  It is possible to have one PC with a faulty Network driver which corrupts the database for everybody.

To reduce the risks of corruption don?t use memo fields. And always use pessimistic locking.

SQL server is faster when searching for data as SQL is just making local disk access but with Access the whole file has to be past through the network to each PC.
GebhartBobAuthor Commented:

     I changed from SQL statements to the ISAM technique you suggested, leaving access via ADO (Jet implements Seek, though other providers don't), because I will be using Access via a server.

     I simply replaced two SQL statements which accessed two records with the appropriate Seek commands. That's absolutely the only change I made.

     The difference was enormous. Whatever those SQL statements do, they sure take their sweet time about it.

     Thanks very much for your help.

Glad to hear that bob
you of cource realize that your code wont run anymore against SQLServer
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now