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

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.
0
PAXSoftware
Asked:
PAXSoftware
2 Solutions
 
SethiCommented:
DAO is definately faster than ADO as it was made exclusively for Access. So you may see some speed difference between both the technologies and this difference widens on network and when data is huge.
0
 
DhaestCommented:
DAO is old technology, and microsoft stop supporting it, and it's working fine with access or desktop database

  ADO is new technology from microsoft, and it's working faster and have more feathers than DAO, and when using it with sql server and oracle it be faster than DAO

if you would like to convert from DAO to ADO look at this page from microsoft to help you

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp



Here's some good links for more info.

http://www.databasejournal.com/features/mssql/article.php/1490571

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate_topic2.asp

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q225048&;
0
 
PAXSoftwareAuthor Commented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

AdUseClient
AdUseServer
0
 
leonstrykerCommented:
>>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
0
 
PAXSoftwareAuthor Commented:
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.

0
 
leonstrykerCommented:
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
0
 
martin_wattsCommented:
If your customers do not want to shell out for SQL Server (understandably), maybe MSDE is an alternative?
0
 
PAXSoftwareAuthor Commented:
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.
0
 
leonstrykerCommented:
This question should in no way be deleted or refunded.  Recommend

Split: Sethi, Dhaest


Leon
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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