Solved

MS Access Efficiency, or Lack of Same

Posted on 2001-07-19
9
198 Views
Last Modified: 2012-08-13
    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?
0
Comment
Question by:GebhartBob
9 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 6300440
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.

0
 
LVL 6

Expert Comment

by:pierrecampe
ID: 6300539
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

0
 

Author Comment

by:GebhartBob
ID: 6300555
    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!
0
 
LVL 1

Expert Comment

by:frankylew
ID: 6300629
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 ?
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 6

Accepted Solution

by:
pierrecampe earned 100 total points
ID: 6300756
>>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
etc...
if you need to have 2 database engines
I think your best bet is ADO used against SQLServer and MSDE
0
 
LVL 6

Expert Comment

by:pierrecampe
ID: 6300798
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
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6300830
I created a performance analysis for MS Access using DAO.  I am about to modify it to produce results for ADO. See question:

http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20145389

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.

e.g.

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

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.
0
 

Author Comment

by:GebhartBob
ID: 6303006
Pierre---

     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.

---Bob
0
 
LVL 6

Expert Comment

by:pierrecampe
ID: 6303690
Glad to hear that bob
you of cource realize that your code wont run anymore against SQLServer
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem to the message 13 63
vba find the last empty column 10 81
vb6 programatically bring window to front 8 53
Using "ScreenUpdating" 6 45
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now