ADO and RDO vs. DAO

[AM LOPEZ]

Hi vb-world:
I have a question, I'm using the DAO data control connected with a Microsoft Access database and the behavior it's very acceptable when I make an SQL query, it takes almost 1 second to show me the records, the database it's in a server. I made a test using an ADO and RDO data control and when I make the same SQL query it takes almost 18 seconds to show the records with the same database, the number of records are 2900, I’m using a connection without DSN.
How can I improve the speed to show the records? I don’t believe that the ADO and RDO components designed for remote data they are very slow to show records with a simple SQL query.
Thanks in advance....
Angel Maldonado López.


------------------

[DVint]

I'm a developer using VB5 with DAO connecting to Access tables. I've been getting ready to upgrade to VB6 and your post give me pause. I had no idea there was that kind of performance difference between DAO and ADO. I have nothing useful to pass on to you, I just wanted to say that. Thanx
Dave

------------------

[haveitall]

There is a massive difference between the different connection methods. If you are using Access as your database, DAO is by far the better method to use. It is referenced in Microsoft Technet, that DAO with Access 97 databases is up to 10 times faster than using ADO or RDO methods, as these are primarily designed for SQL Server databases. The reason is that DAO uses the MS Jet driver model more efficiently than ADO and RDO. You can still use DAO in VB6. Hope this answers your question.

[DrWatson]

Hi,

Before I give my opinion, I just want to say if you want to upgrade from DAO to ADO - it is a massive task. If using an ACCESS database, stick to the DAO, but if you have to move to a SQL server (6.5 or 7.0), rather thinking of upsizing your database (browse TechNet for details) first and at a later stage gradually re-write your code. The Visual Basic Programming Journal also states that this is a very difficult move because of the events in the controls (or coding techniques) differ very much from each other.

If you are using ADO and a SQL server, decide what sort of Cursor you want to use - Server side or Client side. I have installed a production system on SQL 6.5 and the client is on a Terminal Server and this chows up the resources terribly because the Cursor is a client side cursor. So be very carefull how you implement your cursors. A very good book to work through is "Hitchiker's Guide to Visual Basic and SQL Server - 6th edition" by Bill Vaughn (Microsoft Press Books). Here he describes in detail how to handle ADO and have very nice samples and a custum MSFlex Grid control that uses ADO.

Happy coding!

Dr Watson