asked on

VB6 reading MS Access 97/2000 db using ADO vs. DAO

I have a scheduling application written in VB 6. The database is in MS Access 97 (or in 2000, makes no difference) and is sitting on a shared network drive. The application is only used by 4 or 5 people at one time. I have created a "Data Access Layer" component to do all of the database reading and writing.

When I use ADO for the database access it is VERY slow. When I use DAO for the database access it is a LOT faster. The exact same query reading 12,000+ "people names" from the database using ADO takes over 2.5 seconds and using DAO it takes less than 0.5 seconds!! There is a similar difference when reading lists of City names, product names, etc.

ADO seems to be more stable, causes fewer database corruptions and provides the "disconnected recordset" option which is not available in DAO. But it is unacceptably slow - or it seems to be.

Should I expect to see this kind of degradation in response from ADO or should it be as fast as DAO?

Thanks much,

Paul W.

ASKER CERTIFIED SOLUTION

Sethi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Dirk Haest

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PAXSoftware

ASKER

Thanks for the responses. I know that DAO is old, and no longer supported. I started out using ADO in this version of the application, but was horrified by the slow response over a network. My customers are comparing this to an older version of the application that did use DAO, and it had a pretty snappy response. The response with ADO is slow.

If I want to move the database to SQL Server (or Oracle, or DB2, etc) I am sure I will use ADO, but many of my customers do NOT want to shell out the money for that (small businesses).

I guess I am asking if I should expect to see such a slow response in ADO? I have tried playing with the parameters in the ADO call and it does not seem to make a difference.

x50Fade

Have you tried playing around with the curser location...

AdUseClient
AdUseServer

leonstryker

>>I guess I am asking if I should expect to see such a slow response in ADO?

This may also depend on your query structure. Although DAO is faster, there should not be huge differences. Lets take a look at a sample query and see if it can be improved.

Leon

PAXSoftware

ASKER

The query I use in ADO or DAO is identical. I actually have the query defined in the MS Access database and then make reference to that query in the application code: As an example, retrieving the list of people (used to build a drop-down combo-box) is quite simple:

SELECT CM_PPL_Person.*
FROM CM_PPL_Person
WHERE (((CM_PPL_Person.SYS_Active)=True) AND ((CM_PPL_Person.SYS_Deleted)=False))
ORDER BY CM_PPL_Person.CM_LastName, CM_PPL_Person.CM_FirstName, CM_PPL_Person.CM_BirthDate, CM_PPL_Person.CM_PersonID;

The structure of the "CM_PPL_Person" table is quite simple - just stuff to identify a person:

CM_PersonID Long Integer Primary Key
CM_FirstName String
CM_MiddleName String
CM_LastName String
CM_PreferredName String
CM_SalutationID Long Integer Alternate Index - not unique
CM_Suffix String
CM_BirthDate Date/Time Alternate Index - not unique
CM_GenderID Byte Alternate Index - not unique
CM_MaritalStatusID Long Integer Alternate Index - not unique
CM_Deceased Yes/No
SYS_Status Long Integer
SYS_Valid Yes/No
SYS_Active Yes/No
SYS_Deleted Yes/No

and then I have a "Create/Update stamp" identifying when/where/who created and last updated each record. And there is a non-unique compound index built on:
CM_LastName
CM_FirstName
CM_MiddleName
CM_BirthDate

As for the Cursor Location - I specifically set it to use the client side cursor. I thought that if you wanted to use disconnected recordsets in ADO then you had to use client side cursors.

Paul W.

leonstryker

First of all try this for the query:

SELECT *
FROM CM_PPL_Person
WHERE SYS_Active = True AND SYS_Deleted = False
ORDER BY CM_LastName, CM_FirstName, CM_BirthDate, CM_PersonID;

You may also try listing the columns you want as opposed to selecting all (*). You should index SYS_Active and SYS_Deleted fields since these are the fields you are searching by.

Make sure your cursor type is static:
objRS.CursorType = adOpenStatic

Leon

martin_watts

If your customers do not want to shell out for SQL Server (understandably), maybe MSDE is an alternative?

PAXSoftware

ASKER

Thanks for your help, however, this time I did not learn anything new. I am going to ask the moderator to delete this question and refund my points.

leonstryker

This question should in no way be deleted or refunded. Recommend

Split: Sethi, Dhaest

Leon