ADO/DAO speed w/Mdb - dissapointed! 6

[Guest_imported]

I have a table in an Access 97 Mdb with about 25,000 records, and about 45 fields, a unique key plus about 5 other indexes.

I use DAO Jet 3.51. The MDB is local.

When I create a simple recordset with a select statement to pull in all fields and 25,000 records, using either a snapshot or dynaset, and then using MoveLast, it takes about .02 seconds (using query preformer) to get the data.

I am testing this also with ADODB 2.5 and Jet 4.0 3rd update. I used a connection and a recordset, keyset or static, client cursor, lock optimistic.

It takes 4.2 to 4.8 seconds to do the same.

I ran this test several times and got same results.

I thought ADO was suppose to be better????
WHAT'S GOING ON????

Would an access 2000 mdb make a difference?

Very frustrated.

 

[Guest_imported]

This is really stupid!!

I tested it with ADO and Jet 3.51 OLEDB provider - same results as ADO and Jet 4.0!
4.2 to 4.8 seconds

Then I used DAO 3.6 and Jet 4.0
.16 seconds each time

Then I used DAO 3.51 and Jet 3
.02 - .03 seconds each time

Then I used DAO 2.5 /3.5
.02 - .03 seconds each time


(These tests are not including connection or database opening times).

So, Jet 4.0 with DAO is alittle slower.

But ADO causes it to be EXTREMLY slower.

Too bad. ADO has alot of what DAO was missing. But for that speed difference, is it worth a change?

Anyone can tell me how to improve it???

 

[CasperTFG]

I can bet you it's in yoru code. THere are quicker ways of making calls in ADO than there are in DAO.
Craig, mailto:sander@cogeco.ca

Si hoc legere scis, nimis eruditionis habes

 

[SBerthold]

So, please show me the quicker ways, or at least one quicker way than the code below, and I will test it.

Because there is only one response to my question, I almost want to think that my question is such a dumb one and I am just ignorant,
or this (slower access) is a fact with ADO and Jet.

My ADO code is actually only a few short lines. You can see this below.

The first time a button is clicked, the recordset init. takes a little longer, but after that, the 2 time on, the times it takes is a little faster:

The below example uses 2 command buttons on a form, and references to DAO 3.51 (or DAO 3.6) and ADO 2.5.
If you use an 2000 MDB then change for the ADO connection Type 4 to Type 5.

Just drop the below into the code window of a new form and add two command buttons.
You will need a MDB and will need to change the path and table name.
----------------------------------------------------------

Option Explicit

Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long
Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
Private conn As ADODB.Connection
Private db As DAO.Database
Private cStartTicks As Currency
Private cStopTicks As Currency
Private cFrequency As Currency

Private Const MDB_NAMEANDPATH$ = "C:\Test\Test.MDB"
Private Const TABLENAME$ = "TestTable"

Private Function DAO_Recordset()
Static DBIsOpen As Boolean
Dim rs As DAO.Recordset

If Not DBIsOpen Then
cFrequency = 0: cStartTicks = 0: cStopTicks = 0
QueryPerformanceFrequency cFrequency
QueryPerformanceCounter cStartTicks

Set db = DBEngine.Workspaces(0).OpenDatabase(MDB_NAMEANDPATH, False, False)

QueryPerformanceCounter cStopTicks
Debug.Print "DAO Connected in: " & (cStopTicks - cStartTicks) / cFrequency

DBIsOpen = True
End If

cFrequency = 0: cStartTicks = 0: cStopTicks = 0
QueryPerformanceFrequency cFrequency
QueryPerformanceCounter cStartTicks

Set rs = db.OpenRecordset("SELECT * FROM " & TABLENAME, dbOpenDynaset)
rs.MoveLast
Debug.Print "DAO RecordCount: " & rs.RecordCount

QueryPerformanceCounter cStopTicks
Debug.Print "DAO got records In:" & (cStopTicks - cStartTicks) / cFrequency

rs.Close
Set rs = Nothing

End Function


Private Function ADO_Recordset()

Static ConnIsOpen As Boolean
Dim rs As ADODB.Recordset

If Not ConnIsOpen Then
cFrequency = 0: cStartTicks = 0: cStopTicks = 0
QueryPerformanceFrequency cFrequency
QueryPerformanceCounter cStartTicks

Set conn = New ADODB.Connection

conn.CursorLocation = adUseClient

'conn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & MDB_NAMEANDPATH
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MDB_NAMEANDPATH & ";Jet OLEDB:Engine Type=4"

QueryPerformanceCounter cStopTicks

'This is not a true connection time. ADO will only connect to the ds when a recordset
'is opened for the first time.
Debug.Print "ADO Connected in: " & (cStopTicks - cStartTicks) / cFrequency

ConnIsOpen = True
End If

cFrequency = 0: cStartTicks = 0: cStopTicks = 0
QueryPerformanceFrequency cFrequency
QueryPerformanceCounter cStartTicks

'Test #1---------------------------------------------------------------------
' Set rs = conn.Execute("SELECT * FROM " & TABLENAME, , adCmdText)
'Test #2---------------------------------------------------------------------
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "SELECT * FROM " & TABLENAME, conn, adOpenStatic, adLockBatchOptimistic, adCmdText
'-----------------------------------------------------------------------------

rs.MoveLast
Debug.Print "ADO RecordCount: " & rs.RecordCount

QueryPerformanceCounter cStopTicks
Debug.Print "ADO got records in: " & (cStopTicks - cStartTicks) / cFrequency

rs.Close
Set rs = Nothing

End Function

Private Sub Command1_Click()
ADO_Recordset
End Sub

Private Sub Command2_Click()
DAO_Recordset
End Sub

Private Sub Form_Load()
Command1.Caption = "ADO test"
Command2.Caption = "DAO test"
End Sub

 

[SBerthold]

Anyone?

I just tried this also with an 2000 MDB. No difference except the fact that DAO 3.6 /Jet 4 is just slightly slower.

Pleaassse! Anyone? I need some help.
I see that I need to change to ADO sooner or later, and I know what responses I will get when things slow down that much.

 

[petermeachem]

For Access 97 (or less) I use
DRIVER={Microsoft Access Driver (*.mdb)};dbq=D:\VTCT\DATA\COLLEGES.MDB
for a connection string. I found some mdb's that would not open with other strings. There don't seem to be any particularly good rules about this. This opened and movedlast on an 5000 record table with no delay that I could see. Certainly much less than a second. Peter Meachem
peter @ accuflight.com

 

[gonzoaz]

You can change the the cursor location to conn.CursorLocation = adUseServer 'access perfers you to user server cursor vs client...Sql is the opposite
also the default cachesize is 1 depending on the number of records that you pull on the average bump it up to something more reasonable and it will run faster also.
ie. CacheSize = 200
Also if you need to set the the recordset to
adOpenStatic, adLockReadOnly
This should also help to speed it up because it requires less overhead....More work though with doing SQl strings vs just setting the fields to be values.

 

[Brewman76]

A quick note about the above answer, opening with a client side cursor will automatically open as "adOpenStatic" no matter what you pass in as that parameter. Anyways... on to my 2 cents.

Tweaks to SBB's above code listing are all fine and dandy but that misses the bigger picture and leaves the original question unanswered.

First I commend SBB for attempting to justify a switch to a newer and supposedly better technology. Anyone in the tech business finds out soon enough that newer is not necessarily better. That aside, one must also consider that the definition of "better" does not equate to "can open a 25,000 row recordset in Access faster". Jet (and henceforth DAO) is for opening "databases", with Access I believe it uses a native connection. A native connection gives your application a fewest layers of abstraction possible between the data and your application. ADO, and more precisely the OLE DB driver you are using, is for opening "data sources". A data source is a more abstract representation of a data set and expands beyond a traditional database. As with most things computers, there is always a give and take between speed and flexibility. This flexibility puts more layers between an app and the data than with Jet. An application using Jet can quickly interact with a small number of databases fast. An application using OLE DB can seemlessly interact with an almost countless number of sources reasonably well.

So what does this mean? In short, Jet and DAO were written with the exact environment you are testing in mind, SBB. The single user, VB front end and Access back end application. That is where Jet shines and is still a viable option for those who need a relatively quick and dirty app and don't need to consider expandability, security, upsizing to SQL Server etc etc... ADO has the big picture in mind, the person writing the 1000 user app that needs to connect to Oracle, DB2 and dBASE all at the same time to the person using Microsoft Index Server to the person just starting out with their first VB and Access application.

To end this with a note of practicality, using ADO in an application as opposed to DAO will necessitate changes in the way data is accessed and updated. As you have noticed, opening a large recordset is more expensive with ADO so decisions have to made to justify why such a large recordset is needed in the first place and explore alternative methods of performing whatever task is required.

Hope this helps give you some insight.

--Adam

 

[petermeachem]

ADO is slower, but not 4 seconds slower. There is something wrong somewhere.
25000 records is fine for access, quite small actually. Peter Meachem
peter @ accuflight.com

 

[CajunCenturion]

I too question the results of the test. Not because of the anything unusual within the test itself, but rather, how sterile is the testing environment, or in other words, what other applications were running on the machine at the time you were performing this test. As an example, you may have a service running in the background that chewed up some processor cycles during the ADO test, but not during the DAO tests. The would account for the drastic difference between the times. Also, were these tests run within the IDE, or with compiled programs. To account for caching and buffering, how many times did you run this test?

I would also expect ADO to be slower, but the magnitude indicated in the results suggests that an unknown factor has skewed the results of this test. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CCLINT]

The tests are probably legitiment. And also the dramatically increase in time (pending free memory and processor speed).

The main, and probably the only problem is this: Cursor location.

Change the recordset cursor location from adUseClient to adUseServer and the problem will be solved.

You can still keep the connection cursor location set to Client...it won't matter because the Recordset cursor will the one that will be used.

Later I will try to explain why this slowness of a client cursor vs. a server cursor, for a database that is local. [/b][/i][/u]*******************************************************[sub]
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[SBerthold]

The problem is with the cursor location. ADO is about 10 times slower than DAO (on my machine with a local MDB). 25,000 records are captured in about 1/4th of a second. With 100,000 records this increases to about 1 sec.
It also makes a very big difference whether or not an ORDER BY is used in the Open statement. But for just sums or counts on 100,000 records it screams (milli-seconds - almost as fast as DAO). For displaying single records or small recordsets it also is fast enough. For displaying large amounts of data it is still much slower - but I guess this is where Caching and the Fetch Data methods come into play.

Any more input, from anyone, on the subject would surely help everyone. Then these posts can be referred to others in the future.

 

[johnwm]

As I can't get 25000 records displayed on my screen at once, I usually try and design my queries in such a way that they only call up as much data as I actually need.

That way it doesn't matter much how long large recordsets take to fetch. Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[CCLINT]

Or use a Async method.

Pulling in or looping thru 25,000 records in order to perform an update, or when displaying 1 record at a time is the wrong method. If you are updating all the records that meet a specified criteria, then use an Action query. If you are only showing one record at a time, then either use a Keyset/Static server side cursor, or, use a client static cursor and pull-in, (open the recordset), based only one record at a time (setting criteria on the recordsets command text to limit the records returned).
For the latter, 2 recordsets would be best:
One rs that uses a server or client side static cursor,on all records, but retrieving only the values for the primary/unique key and, when the user clicks an arror to get the next record - which navvigation is based on this narrow recordset - take the primary key and open a second recordset new using the primary field and value from the first rs as the criteria for the second rs, and then pull in all of the required fields for that one record.

If you need to have the user view 25,000 records, say in a Grid, then use a client side cursor of type static using as an optional parameter the constants:

adCmdText Or adAsyncFetch

as in:

rsADO.CursorLocation = adUseClient
rsADO.CacheSize = 50
rsADO.Open "SELECT * FROM myTable", conn, adOpenStatic, adLockBatchOptimistic, adCmdText Or adAsyncFetch

Set DataGrid1.DataSource = rsADO

Then, what happens first, is the cache will fill up with 50 records, the grid will show these immediately, and then in the back ground, the remaining records will be pulled in and also placed into the grid.
The only problem with all this is if you use an ORDER BY in the command text. Then the records would need to be sorted first, before displaying. Use indexes for this.

You will not be able to get a record count until this is complete. Or more correct to say is: you could get a recordcount with the rsADO.RecordCount method, but anything that you do with the recordset object, before all the records have been retrieved by the process, that causes the cursor to move to the last record (as in the case of rsADO.recordcount) will cause the recordset to first fill with data before you can do anything else, basically cancelling the async processing - the thread that your project is running on will tied up until the recordcount is complete.

Instead, using the Event:

Private Sub rsADO_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
Label1.Caption = rsADO.RecordCount
End Sub

Then, once the fetching of all records in done, you will have, and automatically be able to display, a record count.

You also have the following event that you can use:

Private Sub rsADO_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)


You will also have the possibility to stop the fetching in case the user decides to close the form before all records are retrieved.

This is similar to what you will see in ACCESS when opening a table with thousands of records in the database window.

[/b][/i][/u]*******************************************************[sub]
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[zyon]

whe u are geting all the records are you binding imidiatly the recordset????? some controls make the binding very slow!!! if u dont need to see all the records at same time, u can use 2 method's.

1st - select [indexed_field] >>>> only the indexed in one 'datadource' then u can move the next record or previous or next or last records using the index to retrieve all the other data u need using other 'datasource'

u could improve a litle bit the performance...

If u need to bind everything try to use the following:

adodcregvisdata.LockType = ReadOnly

In read only mode u could gain some extra time...

there are some tricks u can use on the conection string, like the cursor type

The only way u to solve the problem is to try avry type of connection and see the one that best work for your case...

Sorry the english

 

[petermeachem]

The point is why is there such a huge speed reduction when using ado instead of dao and not how can I redesign my programme to get round this problem? Peter Meachem
peter @ accuflight.com

 

[Jaco]

A few more words about using ORDER BY in queries. Sometimes using indexing instead (as CCLINT advised) is not the best solution (i.e when you need a descending sorting). I discovered that it's also good to use recordset's sort property. It's faster than using ORDER BY in a query

Jaco

 

[CCLINT]

Er, that is what Sort does: It creates an index on the field if one doesn't already exist.

And, the sort/filter function will work only on a Client side cursor, unless the interface supports it.
This is alright if, when there is a large amount of data being pulled in, the time it takes to open the recordset doesn't matter. Using a client side cursor, once the data is loaded, is much faster and effectiver than using a server side cursor - and you can rely on the fact that most operations (the methods that will be ran by the client side engine - the OLEDB service component) will be identical with different providers, because navigating in the recordset, and data, is then provider independent. [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!