[RESOLVED] ADO v DAO - Why is DAO faster?

[lintz]

I've always used ADO when using Access databases but just recently I've taken over a project where DAO has been used.

I've extracted a module from the project to use is another project (which I started using ADO). The problem is the ADO code takes around 5 times longer than the DAO code.

I have attached a sample project which shows how much quicker the DAO code completes compared to the ADO code. The database is a cut down version of what is used so 5 times longer can equal a long wait....

Why would ADO be so much slower than DAO?

[rory]

they arent being created the same way ..

this line ...
rs.Open sSQL, strcn, adOpenStatic, adLockPessimistic

Change to this ..
Set rs = strcn.Execute(sSQL, , adCmdText)

Also Getrows would be even faster ..
but thats for another day.

Edit ... seems the connection gives an error ..
The connection code is not being done correctly ..

[randem]

It is slower because your technique is all wrong. I am amazed that it worked at all. You are basically opening a connection to the database each time you open a recordset. Not a good idea.

Try this:

[rory]

he got me lost with all that code with exclamations in it
lintz, ADO is the successor to DAO BTW ..
Opening a connection when you open the recordset is not the issue though.

[randem]

Opening a connection when you open the recordset is not the issue though.

Really??? Did you look at the code I posted. That was basically the only change I made and look at the difference between the two times in his project and the one I posted.

[rory]

Really??? Did you look at the code I posted. That was basically the only change I made and look at the difference between the two times in his project and the one I posted.

sorry my bad .. yeah i see now why he is getting the speed diff .
Got a little lost up in that code is all .. 4am and all

[lintz]

Thanks randem

[rory]

actually with all the tests above i was getting DAO as faster. ..
But when i put ADO in front of DAO then i ADO was faster
So basically its a timing issue ... is all.

also you can speed that ADO up where you have loops, using GetRows ..
And Stored Queries, as well as setting the variables ...

[lintz]

What is GetRows and how do I use them?

So it's now 5am where you are

[rory]

yeah 5am and im off to sleep in a minute, but basically get rows is like this ..
Example using a Stored Query ..

Set objRs = objConn.Execute("Execute sq_MyQuery", , adCmdText)
    If Not objRs.EOF Then
        arr = objRs.GetRows()
        Set objRs = Nothing
        iCnt = UBound(arr, 2)
        For i = 0 To iCnt
            sField0 = arr(0, i)
            sField1 = arr(1, i)
            '// do something 
        Next i
    Else
        Set objRs = Nothing
    End If

Note - this is just one example of GetRows.
Basically it lets you grab all the results into an array ..
then just loop through the array.

This partilcular method is proabably the fastest loop ..
when you have alot of results ... it just spits it out at you.

[leinad31]

There are other considerations other than speed for using ado over dao, such as possible future database migration.

And to speed things up, you might want to consider using aggregates such as Sum() rather than iterating through recordsets.

[lintz]

Nice one leinad31, using SUm will definately fasten it up for me!

[luca90]

yeah 5am and im off to sleep in a minute, but basically get rows is like this ..
Example using a Stored Query ..

Set objRs = objConn.Execute("Execute sq_MyQuery", , adCmdText)
    If Not objRs.EOF Then
        arr = objRs.GetRows()
        Set objRs = Nothing
        iCnt = UBound(arr, 2)
        For i = 0 To iCnt
            sField0 = arr(0, i)
            sField1 = arr(1, i)
            '// do something 
        Next i
    Else
        Set objRs = Nothing
    End If

Note - this is just one example of GetRows.
Basically it lets you grab all the results into an array ..
then just loop through the array.

This partilcular method is proabably the fastest loop ..
when you have alot of results ... it just spits it out at you.

hi Roy....
but during the loop in array is possible to know wath is the current filed name ...
similar:

   Set objRs = objConn.Execute("Execute sq_MyQuery", , adCmdText)
    If Not objRs.EOF Then
        arr = objRs.GetRows()
        Set objRs = Nothing
        iCnt = UBound(arr, 2)
        For i = 0 To iCnt
            sField0 = arr(0, i)
debug.print namefiled of sField0
            sField1 = arr(1, i)
debug.print namefiled of sField1
            '// do something 
        Next i
    Else
        Set objRs = Nothing
    End If

[si_the_geek]

Like this:

debug.print objRs.Fields(i).Name

By the way, rather than replying to this old thread (and thus sending emails to everyone who has replied/subscribed) you should have posted a new thread.

[luca90]

Like this:

debug.print objRs.Fields(i).Name

By the way, rather than replying to this old thread (and thus sending emails to everyone who has replied/subscribed) you should have posted a new thread.

Sorry for:
By the way, rather than replying to this old thread (and thus sending emails to everyone who has replied/subscribed) you should have posted a new thread.

tks for explain

but really my qst is:

is possible to decide wath filed get from getrows array???

i dont need to gett all fileds from the statement but only PROVA1, PROVA3, ecc...

similar:
arrResults = RSSQL2.GetRows(PROVA1, PROVA3...ecc)

[si_the_geek]

Specify the fields you want in the SQL statement, eg:

SELECT Prova1, Prova3 FROM tablename ...

[luca90]

Specify the fields you want in the SQL statement, eg:

SELECT Prova1, Prova3 FROM tablename ...

ok! tks.

[brucevde]

is possible to decide wath filed get from getrows array???
i dont need to gett all fileds from the statement but only PROVA1, PROVA3, ecc...

To specify which fields you want to be included in the Array use the 3rd argument of the GetRows method. The 3rd argument accepts an array of fieldnames or ordinal positions (or a single field/position).

Dim arrResults As Variant
arrResults = rsData.GetRows(, , Array("PROVA1", "PROVA3"))
arrResults = rsData.GetRows(, , Array(0, 2))

[luca90]

To specify which fields you want to be included in the Array use the 3rd argument of the GetRows method. The 3rd argument accepts an array of fieldnames or ordinal positions (or a single field/position).

Dim arrResults As Variant
arrResults = rsData.GetRows(, , Array("PROVA1", "PROVA3"))
arrResults = rsData.GetRows(, , Array(0, 2))

tks Bruce & si_the_geek!