Why is ADO so much slower than DAO?

[Dragon55]

I am considering upgrading my VB6 apps to use ADO and Jet4.0. I have always used the generic data control up till now. So I made an Access 2000 version of one of my (replicated) databases and tried several methods of accessing the table to be diplayed in a DataGrid. (31,000 records, 26 fields)

But every method I tried turned out to be WAY slower than the old way. (DAO - 7 seconds, ADO - 37 seconds) Is this kind of performance typical of ADO, or am I missing something?

One thing I haven't messed with yet is the cursor. I know there are different kinds of cursors to access the data, but I don't know much about it. Could this be my problem?

 

[Karl Blessing]

depending on how you use ADO it can become very slow, be careful of the cursortypes you use, also keep in mind, ADO can allow multiple connections, DAO cant, ADO would seem to tie in with much more features, so you can kind of see DAO as being a lightweight version, that only allows a single connection at a time.

some tips to take note of

using a cursortype of forwardonly is the fastest, but you will not have a count of records.

from fastest to slowest (in my opinion)

Forwardonly
Static
Keyset
Dynamic

It also depends what you are running ADO on, from your comment sounds like ADO connecting to a Access database, ADO will have a much better performance on something like SQL server, and would be more compatible, as SQL is meant to be multithreaded connectivity.

You could still use DAO, they do have some updates for it, but microsoft seems to make the notion they're ditching support on DAO.

 

[Dragon55]

OK, I was able to get the time down to 16 seconds with the following code:


Option Explicit
Private cnWIP As ADODB.Connection
Private rsWIP As ADODB.Recordset

Private Sub Form_Load()

Dim sConnStr As String
Dim sSQL As String

Set cnWIP = New ADODB.Connection
Set rsWIP = New ADODB.Recordset

Me.Show
Me.Refresh

sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConnStr = sConnStr & "Data Source=C:\VB Applications\Testing\Work In Process.mdb;"
sConnStr = sConnStr & "Persist Security Info=False"
cnWIP.ConnectionString = sConnStr
cnWIP.Open

sSQL = "SELECT * FROM [WIP] ORDER BY [Job Number], [Serial Number]"
rsWIP.Open sSQL, cnWIP, adOpenStatic

Me.Caption = rsWIP.RecordCount 'This just let's me know it's done...

Set grdWIP.DataSource = rsWIP 'because this doesn't work.
End Sub

Private Sub Form_Unload(Cancel As Integer)
rsWIP.Close
Set rsWIP = Nothing
cnWIP.Close
Set cnWIP = Nothing
End Sub


But I still consider that too long to make my users wait. Can anybody suggest anything to speed it up?

And here's something else I can't figure out: The line "Set grdWIP.DataSource = rsWIP" doesn't do anything. It doesn't produce an error, but it doesn't fill the grid, either. Can anyone tell me why not? MSDN says you can use any ADO data source to fill a DataGrid, but I can't get it to work.

Bob Dragon

 

[mohmehran]

As kb244 wrote, the only thing you can do is to to open the table as Static. (Forward-Only cannot be used since you are filling a grid) Also If you don't make any updates to the dtabase using the grid, when you open the table use adLockReadOnly as locking method. This will improve performance also.

At last as DB proffessional say, it is possible that a piece of code runs faster when you use RDO (or DAO). But here is a tradeoff between performance and the Universality of the ADO. Most of the times ADO wins.

About the DataSource of the grid, I think you are using Microsoft FlexGrid control, which only accepts Intrinsic (internal to VB) data controls. You can switch to the newer version of FlexGrid, which is named Microsoft Hierarchial FlexGrid control. I don't think that the conversion takes much effort. Then you can use it along ADO Data Control.

 

[Dragon55]

Thanks for your input, mohmehran, but as you can see in the rs.open statement, it is opened as Static. And I didn't specify the LockType because adLockReadOnly is the default.

As for the grid I'm using, it's the new DataGrid control, which definately accepts ADO data sources.


I would expect some kind of a performance hit with the increase in functionality of ADO - I just didn't expect it to be SO MUCH worse.

Now I have a very difficult decision to make: Whether to go ahead and upgrade to ADO, or just stick with DAO, which works OK. It seems like a lose-lose decision.

Unless somebody else has any suggestions or ideas. (Please!)

Bob Dragon

 

[listener22]

Don't know if this helps, but most users probably don't need all 21000 records
all at once. Try using the jet syntax to limit the number of records fetched at first,
I think it's a "TOP 100" or how ever many you want the first time. If that still takes
16 seconds, forget this approach. But if it is faster, then you can consider whether
you want to increase the complexity of the program, by retrieving records in sets
of some number, in order to increase its speed. You'll need a button that the users
can hit to get the next set (and maybe that's not acceptable to them), and you'll
need a way to track which set of records a user is on. I've done this by adding an
active-x dll project that has different methods for interacting with the database.
You can access the projects methods directly from the ui, or run them through
a business logic tier.

 

[nsbajwa]

well bob i may have a remedy.Instead of populating a flex grid why dont u go for a list view and ADO probably it will improve the speed(at least in my case it did)

declare listname as new list and first populate the headers,then go for the data fields in another sub.

nsbajwa

 

[willcas]

Hey Dragon55, There is another option you can add which is the adCmdText option which you place last after the locktype and cursor type try it and see if it helps it should make a difference.

Enjoy
Will

 

[swilliams]

Just to compare, I was writing a page for the intranet at my ex employer using ColdFusion. I was interested in ASP so I did the exact same page in ASP to compare the two. The ColdFusion page was using ODBC links to a datasource (Access 97) and the ASP page was using ADO. It was all on MS technology - NT, IIS.
The ColdFusion code had to be run through its own parser to produce the HTML, but the ASP page was obviously just VB script on the page.
I probably could have speeded up the ADO connection, but my findings were that the ColdFusion page was twice as quick to serve up the results that the ASP - hence ADO - way.

Simon [sig][/sig]