Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
DAO vs ADO
- Thread starter Tinso
- Start date
This is from msdn:
Transcend
![[gorgeous]](/data/assets/smilies/gorgeous.gif "[gorgeous] [gorgeous]")
Transcend
- Thread starter
- #4
DirectDrive
Programmer
My opinion is ADO is the better was to go regardless of if you can get away with DAO.
ADO allows connections to most common databases without the need for DSNs. When the day comes that your boss says that the company is migrating everything to Oracle, SQL Server, My SQL, etc, you will have no worries since you already know ADO.
The ability to develop app's without having to worry about DSN distribution is also a beautiful thing.
ADO allows connections to most common databases without the need for DSNs. When the day comes that your boss says that the company is migrating everything to Oracle, SQL Server, My SQL, etc, you will have no worries since you already know ADO.
The ability to develop app's without having to worry about DSN distribution is also a beautiful thing.
DAOs do work with ACCESS 2000 and are faster. I am updating a program that our company has been using for a couple years with DAOs to ADOs. So, we can goto SQL Server 2000. I have been running into slow update problems when the ADOs are running multiple connections through seprate classes. But, DAOs did it fine.
stoolpigeon
Programmer
You are right strongm and that is the only thing you need to really think about when you are considering between the two. DAO was written for JET. If you use anything else go with ADO. If you are moving away from JET at some point use ADO. But if it will always be a JET database you are working with- use ADO. It will perform better.
stoolpigeon
Programmer
for the love of pete- the next to the last sentence in my post should read
"But if it will always be a JET database you are working with - use DAO"
"But if it will always be a JET database you are working with - use DAO"
- Thread starter
- #13
Just a postscript:
I converted one of my applications from DAO 3.6 to ADO. The run updates about 8000 records in one table with a long integer primary key and about 10,000 records in another table with a compound index (long integer and a short string). The ADO version takes about 5 times longer! Lucky I saved the old code!
The database is Access 97, about 25 meg compacted.
Would SQL Server give a dramatic improvement in performance?
I converted one of my applications from DAO 3.6 to ADO. The run updates about 8000 records in one table with a long integer primary key and about 10,000 records in another table with a compound index (long integer and a short string). The ADO version takes about 5 times longer! Lucky I saved the old code!
The database is Access 97, about 25 meg compacted.
Would SQL Server give a dramatic improvement in performance?
How did you do the Update?
Try doing it against the connection object, which is the fastest method under ADO:
conn.CursorLocation = adUseServer
conn.Execute "UPDATE....", ,adExecuteNoRecords
Also, you have the option to run this async.
One other factor which will slow things down is the fact that you are probably using the JET 4 on a JET 3 database.
Using the native JET driver through DAO will still be faster, just because of the extra layer used under ADO and some other factors concerning the JET OLEDB provider, but while using ADO will be a little slower, it shouldn't be 5 times slower for this type of operation.
Try doing it against the connection object, which is the fastest method under ADO:
conn.CursorLocation = adUseServer
conn.Execute "UPDATE....", ,adExecuteNoRecords
Also, you have the option to run this async.
One other factor which will slow things down is the fact that you are probably using the JET 4 on a JET 3 database.
Using the native JET driver through DAO will still be faster, just because of the extra layer used under ADO and some other factors concerning the JET OLEDB provider, but while using ADO will be a little slower, it shouldn't be 5 times slower for this type of operation.
- Thread starter
- #16
I think I tried the execute from both the connection and the command. The timing comparison was based on the command object execute. Thanks for the tip about the difference.
The bigger issue is probably the Jet 4 provider on a Jet 3 db. The 'supports index' and 'supports seek' are both false for Jet 4 provider. I was forced to use a rather convoluted 'Find' to navigate to individual records for the compound index.
For now I will stick with the DAO. In a few months the corporate environment will be upgrading. Jet 4 will be part of the upgrade.
How is ADO.Net for speed?
The bigger issue is probably the Jet 4 provider on a Jet 3 db. The 'supports index' and 'supports seek' are both false for Jet 4 provider. I was forced to use a rather convoluted 'Find' to navigate to individual records for the compound index.
For now I will stick with the DAO. In a few months the corporate environment will be upgrading. Jet 4 will be part of the upgrade.
How is ADO.Net for speed?
- Thread starter
- #18
quicker to program or quicker to run ?
Likewise I have been playing around with .Net, but only at home. I can't compare the performance in the home environment. Home machine is faster, database is on the local hard drive etc.
.Net seems to have more layers ??
any idea what kind of licencing costs for a corporate setup of SQL Server?
the LAN/WAN has thousands of machines but I doubt if more than a dozen would be in the db at one time. Intranet ASP would be nice as well?
Likewise I have been playing around with .Net, but only at home. I can't compare the performance in the home environment. Home machine is faster, database is on the local hard drive etc.
.Net seems to have more layers ??
any idea what kind of licencing costs for a corporate setup of SQL Server?
the LAN/WAN has thousands of machines but I doubt if more than a dozen would be in the db at one time. Intranet ASP would be nice as well?
There are a few more points to make in this discussion. It is true that DAO was optimized around Jet, and RDO around ODBC. As such, these are the best object libraries to access their respective middle layers, at least with respect to performance.
The main point about ADO is that it is a layer that sits on top of OLE DB, which is a set of COM components that can be used to write data providers. Data providers are basically analogous to data drivers in the DAO/Jet/ODBC world.
So, anyone can write a data provider that is optimized to their data source. (Of course, they could further optimize to same by writing a proprietary API, but it would be harder to access it.)
This means that there is only one object library to have to know to go through a minimum of layers to get to the back end.
Other pros to ADO: a flatter object hierarchy makes for less objects running around in your code. Each object's methods are substantially overloaded to allow maximum flexibility in accessing their functionality. (Note, for example, the number of ways you can set up, say, recordset.open). Finally, the ability to build recordsets on the fly and not have them tie to a database allows ADO to work with non-relational data.
Cons: ODBC is an open standard. OLE is based on COM, and therefore is a proprietary standard. (Resistance is futile.....you will be assimilated......) There can be performance problems, especially if you don't evaluate carefully which layers you are going through. An egregious example would be to use the OLE DB Provider for Jet to access an ODBC database.
Bob Rodes
The main point about ADO is that it is a layer that sits on top of OLE DB, which is a set of COM components that can be used to write data providers. Data providers are basically analogous to data drivers in the DAO/Jet/ODBC world.
So, anyone can write a data provider that is optimized to their data source. (Of course, they could further optimize to same by writing a proprietary API, but it would be harder to access it.)
This means that there is only one object library to have to know to go through a minimum of layers to get to the back end.
Other pros to ADO: a flatter object hierarchy makes for less objects running around in your code. Each object's methods are substantially overloaded to allow maximum flexibility in accessing their functionality. (Note, for example, the number of ways you can set up, say, recordset.open). Finally, the ability to build recordsets on the fly and not have them tie to a database allows ADO to work with non-relational data.
Cons: ODBC is an open standard. OLE is based on COM, and therefore is a proprietary standard. (Resistance is futile.....you will be assimilated......) There can be performance problems, especially if you don't evaluate carefully which layers you are going through. An egregious example would be to use the OLE DB Provider for Jet to access an ODBC database.
Bob Rodes
"Just a postscript:
I converted one of my applications from DAO 3.6 to ADO. The run updates about 8000 records in one table with a long integer primary key and about 10,000 records in another table with a compound index (long integer and a short string). The ADO version takes about 5 times longer! Lucky I saved the old code!
The database is Access 97, about 25 meg compacted."
I was under the impression that Access 97 didn't support ADO? Am I confused? yep...
Sam
I converted one of my applications from DAO 3.6 to ADO. The run updates about 8000 records in one table with a long integer primary key and about 10,000 records in another table with a compound index (long integer and a short string). The ADO version takes about 5 times longer! Lucky I saved the old code!
The database is Access 97, about 25 meg compacted."
I was under the impression that Access 97 didn't support ADO? Am I confused? yep...
Sam
- Status
Similar threads
- Locked
- Question