How fast can you loop an ADO recordset? 9

[sunaj]

I was doing a mini speed test on looping recordsets - a task that I seem to do all the time in my programs...
Results:

Static,Optimistic, Explicit fields : 6.9180
ForwardOnly,ReadOnly (default), Explicit fields : 0.5117
ForwardOnly,ReadOnly (default), Predefined fields : 0.3125


I was kind of suprised by the last one, Using predefined fields [set f= Rst.fields(..)] is >60 % faster than the explicit method (Rst.fields(..)].

Commments, improvements?

Test code:
---------------------------------------------------------
[sup]
Option Explicit

Private Sub Command1_Click()
Const n As String = "10000"
Dim conn As Connection
Dim Rst As Recordset
Dim s As String, t As Single
Dim f1 As ADODB.Field
Dim f2 As ADODB.Field

Set conn = New Connection
conn.Open "Provider=SQL OLEDB;Integrated Security=SSPI;Persist Security Info=False;User ID=dbo;Initial Catalog=;Data Source="


Set Rst = New Recordset
Rst.Open "SELECT top " & n & " D.Data, D.Pres FROM tblData D", conn, adOpenStatic, adLockOptimistic
t = Timer
If Not Rst.EOF And Not Rst.BOF Then
Do
s = CStr(Rst.Fields("Data&quot) & " " & CStr(Rst.Fields("Data&quot)
Rst.MoveNext
Loop Until Rst.EOF
End If
Rst.Close
Set Rst = Nothing
Text1.Text = "Static,Optimistic, Explicit fields : " & Format(Timer - t, "0.0000&quot & vbCrLf

Set Rst = New Recordset
Rst.Open "SELECT top " & n & " D.Data, D.Pres FROM tblData D", conn
t = Timer
If Not Rst.EOF And Not Rst.BOF Then
Do
s = CStr(Rst.Fields("Data&quot) & " " & CStr(Rst.Fields("Data&quot)
Rst.MoveNext
Loop Until Rst.EOF
End If
Rst.Close
Set Rst = Nothing
Text1.Text = Text1.Text & "ForwardOnly,ReadOnly (default), Explicit fields : " & Format(Timer - t, "0.0000&quot & vbCrLf

Set Rst = New Recordset
Rst.Open "SELECT top " & n & " D.Data, D.Pres FROM tblData D", conn

t = Timer
Set f1 = Rst.Fields("Data&quot
Set f2 = Rst.Fields("pres&quot

If Not Rst.EOF And Not Rst.BOF Then
Do
s = CStr(f1) & " " & CStr(f2)
Rst.MoveNext
Loop Until Rst.EOF
End If
Rst.Close
Set Rst = Nothing
Text1.Text = Text1.Text & "ForwardOnly,ReadOnly (default), Predefined fields : " & Format(Timer - t, "0.0000&quot & vbCrLf

conn.Close
Set conn = Nothing
End Sub
[/sup]
---------------------------------------------------------
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[CajunCenturion]

There are a couple of things that I would do. Firstly, from the first loop

Set Rst = New Recordset
Rst.Open "SELECT top " & n & " D.Data, D.Pres FROM tblData D", conn, adOpenStatic, adLockOptimistic
t = Timer

If Not Rst.EOF And Not Rst.BOF Then
   Do
      s = CStr(Rst.Fields("Data")) & "  " & CStr(Rst.Fields("Data"))
      Rst.MoveNext
   Loop Until Rst.EOF
End If

Rst.Close
Set Rst = Nothing
Text1.Text = "Static,Optimistic, Explicit fields : " & Format(Timer - t, "0.0000") & vbCrLf
    
Set Rst = New Recordset
Rst.Open "SELECT top " & n & " D.Data, D.Pres FROM tblData D", conn

t = Timer

The bolded area defines the work between the two timers - that means that the italicized area is included in the time for the loop, including things that are skewing the results for what is actually being timed, the recordset looping and access times.

I'm not too surprised by the third loop being faster than the second because by predefining, you'll removed one object level of indirection (that being the recordset) from accessing the field values. I would also check into the With statement, which at least in theory, also would remove that level of indirection. Something like the following:

Set Rst = New Recordset
With Rst
   .Open "SELECT top " & n & " D.Data, D.Pres FROM tblData D", conn, adOpenStatic, adLockOptimistic
  t = Timer
  If Not .EOF And Not .BOF Then
     Do
        s = CStr(.Fields("Data")) & "  " & CStr(.Fields("Data"))
        .MoveNext
     Loop Until .EOF
   End If
   .Close
End With
Set Rst = Nothing

You might also want to predefine your spaces as a constant - not sure, but that might also improve somewhat.

Just as a general thought, I would also try and make sure that nothing else that could be causing system interrupts is running in the background, and that cacheing actions are not also skewing the results. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[sunaj]

CajunCenturion
Thanks for your answer.

The code that is timed is:

If Not Rst.EOF And Not Rst.BOF Then
Do
s = CStr(Rst.Fields("Data&quot) & " " & CStr(Rst.Fields("Data&quot)
Rst.MoveNext
Loop Until Rst.EOF
End If

Rst.Close
Set Rst = Nothing
Text1.Text = "Static,Optimistic, Explicit fields : " & Format(Timer - t, "0.0000&quot & vbCrLf

(opening the next recordset is not a part of the measured time). It is same same for all loops - I could leave the .close and set Rst= nothing out but it makes no (not measureable) difference.

Using with or not also makes very little or no difference (as goes for using a constant for the spaces).

I have very rarely seen people set the fields before the loop - but that actually makes a big difference.... and I was just wandering if there might be other tweaks out there that could increase performance likewise.



Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[CajunCenturion]

You're right - I missed the Timer call inside the Format function of the text assignment.

I've never pre-assignments either, but I'm going to start, it does make a difference.

I now only have ??? many lines to change to take advantage of this improvement. :-D Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[vududoc]

May I suggest the following:

IF rs.BOF = FALSE AND rs.EOF = FALSE THEN
rs.MOVELAST
lngRecMax = rs.RECORDCOUNT
rs.MOVEFIRST
FOR iCnt = 0 TO lngRecMax - 1 STEP 1
sData = rs.FIELDS(&quot;<ColumnName>&quot.value
MEXT
END IF

You'll increase the speed because you don't check for an EOF condition each time you pass through the loop. Also using &quot;adLockReadOnly&quot; may help since you're only reading data.

 

[sunaj]

vududoc
The default cursor of a recordset is 'ForwardOnly' and the default locktype is 'ReadOnly'.
ForwardOnly recordsets does not support .recordcount (because you have to move the cursor to the end of the recordset to find out how many rows it contains).

Your example does not compete in speed for 2 reasons:
1)ForwardOnly recordsets are ~12 times faster to loop than recordsets that supports .recordcount
2).recordcount is a very slow operation, it takes more time than to check the .eof for each row (a quick test shows that .recordcount is ~15% slower than checking .eof for each row). This means that even though you, for other reasons, have opened your recordset with a cursor that supports .recordcount it is still faster to loop with cheking .eof for each row than to use .recordcount in a for-next loop.

CajunCenturion I too have ???+ lines of code to change, should I be glad or sad?



Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[Foada]

sunaj,
Thank you for sharing this with the forum. I was starting to get concerned that maybe this forum had lost its focus. It seems as of the last couple of months that the threads have gotten really off track and that the participants have forgot that this is a professional forum. I too, have a great deal of code to re-write/replace or new versions/platforms apps to write. Once again thanks for sharing your work/research with the forum. Well deserved star from me. If you choose to battle wits with the witless be prepared to lose.

 

[CCLINT]

I have mentioned this before.
Maybe things like this need to be more often put together completely in a post and worked out by all of us who are interested, with-out just one or two people doing the work.

I have (also?) been missleading myself and answering to many simple questions caused by some lazy people, or getting into a p contest because I was just offering another method and someone felt insulted, or someone offers another method which is not as effective, an when this is pointed out then some feel insulted and then destroy the value of the thread with an un-called-for post. Or attacks are made indirectly just to put a person down.

VB Professional forum has also been removed, which had alot of good threads, (and alot of reference may have been made to threads there which are now lost), but it wasn't used enough - this is our faults. That forum should have been where we communicate things, like the above, toward one another and develop several solutions, do testing together, decide which solution is best for which situation, etc.

Why aren't we posting questions, ideas and comments on a regular basis, explaining maybe a way something is currently being done, and see if one of the other professional have a better way? Pride?
There is almost always a better way, or rather, one should be open for the possibility that there could be a better way. Why aren't we asking one another?

We all should have been there in the VB Professional forum regularly with a question or two, a comment or a suggestion.

We have an unmatched amount of experience collectivly here.
But the experience doesn't always get shared properly, and too much time may be wasted reviewing every thread, or possibly trying to prove something really useless, meaningless, or, one is concerned about those stars, or where you are on the tops list.

While this may give some sort of personal satifaction, this doesn't help us one bit in our advancements . It doesn't really put more money in the pocket either.

Some of the problems mentioned above, I have contributed to myself.

I, for one, plan in the future not to even look at what seems to be a thread with a basic question unless:

1. It hasn't received any answers at all after a certain amount of time (this I already try sometimes to do)

2. Many posts have been already made, meaning that there may be indeed a problem with the question, or it has indeed turned into a professional, and deeper discussion.

3. The question could be turned into an interesting subject for all of us by adding some deeper thoughts to it.

Maybe the beginners posts should be answered by the beginner and intermidiates; Intermediate posts be answered by the same and the higher skilled; The higher skilled by the same and the professionals, and these last two working out new or better ideas.

Every licensed VB user asking a question in this forum, also:
has the help files; can access the MS Knowledge base; Can review the same questions answered already(sometimes dozens of times) in this or other forums; download examples; have plenty of examples on the VB CD, or already installed on the HD; can go to a search engine and type the question in and with-in a few minutes fine those answers.

How often have we done the same for ourselevs, or for others, and found the answer with-in minutes?

Sure, if this forum is the easy way, because alot of peaple are lazy, and alot of people answer those lazy people, then why not.

Each us us should try to throw us a question once every week or two, just to start a deeper discussion on an interesting subject - it doesn't matter if we have personally already have a solution to fit most of our needs or not, and doesn't mean we do not have an answer at all. There are enough topics where we all can improve in.
And, once a discussion is ripe, then the solution, or a thread pointer(which may not be a good idea), should be placed in the FAQ.



I have mentioned before what sunaj has suggested, but it is always good to get a real discussion going on something like this, and sometimes, as time goes along, the discussion needs to be refreshed and updated.

Some of the fastest techniques are not always obvious. And others may seem more proper or better. And sometimes &quot;Ugly&quot; code is even more efficient (take for instance the Gosub under VB as opposed to calling a seperate proceedure - much much faster, but at the costs of possibly having to duplicate code, or loosing the overview if not properly documented - this I guess cannot be used under VB.Net. Why?).
Setting a reference to the fields before a loop (or to anything involved in multiple calls) is always faster.

Another fast technique that can enhance this, when at the same time going against some people's standards is not using the Recordcount of BOF/EOF calls when looping through a recordset.

While using as the upper limits of a loop a RecordCount value, this slows the whole process down prior to entering the loop, and is not always reliable, and, as pointed out, cannot be used with a ForwardOnly cursor - usually the fastest cursor in most cases.
Using a EOF check is a sure way, but will slow the loop down (sometimes tremendously)

So, just loop(no condition) until it errors out.
Use in line error handling and an &quot;Exit For/Do&quot; for this, when speed and reliability are both important.
Any comments on this?

Do
s = CStr(Rst.Fields(&quot;Data&quot) & &quot; &quot; & CStr(Rst.Fields(&quot;Data&quot)
On Error resume next
Rst.MoveNext
If Err.Number = AdErrNoCurrentRecord Then
On Error Goto Error Handler
Exit Do
'This &quot;Else&quot; may not even be needed
'Else
' Goto ErrorHandler 'Or Err.Raise xzy
'End IF
On Error Goto Error Handler
Loop

(AdErrNoCurrentRecord = 3021 if DAO is used.)


[/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!

 

[sunaj]

CCLINT
You've got a lot of points and I agree on most of them. I also find it annoying that people feel that it is a personal attack if I post a different opinion...
However, Even though I find the more 'professional discussions' the mosts interesting, I don't mind answering the 'easy questions'. I've used this and other forums (and still do!) to solve my problems if I get stuck (and my question probably often seems very basic to the people with greater insight).

I can only encourage people to post if they find something new to them (chances are that it will also be new to at least some others in this forum) - as the 'setting reference of fields' was to me.

On the loop thing:
I was not aware that you had pointed this out before - it the first time that I realized how big a difference it makes.

The error handler is (in my simple test) slightly faster yet:

ForwardOnly,ReadOnly (default), Predefined fields : 0.3398
ForwardOnly,ReadOnly (default), Predefined fields, ErrHndl : 0.3203


Re. Gosub, see

http://www.vb2themax.com/Item.asp?PageID=TipBank&ID=7

Ps. I like the stars - and here is one for you!

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[CCLINT]

>I don't mind answering the 'easy questions'

sunaj: I just alittle annoyed when it is sometimes(often) obvious that people are not making a whole lot of effort to learn on their own. Of course, if one doesn't want to answer them, they do not need to.
Answer them, who ever wants and has the time to do so...

I was just trying to get at a little something different, and also us put our knowledge(and time) together a little more collectively and effectively in order to produce greater things:

I think this thread you started is exactly what I mean (even if that may not have come accross so).
[/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!

 

[PraveenMenon]

CCLINT,
I will change the code with a With Statement
with rst
Do
s = CStr(.Fields(&quot;Data&quot) & &quot; &quot; & CStr(.Fields(&quot;Data&quot)
On Error resume next
.MoveNext
If Err.Number = AdErrNoCurrentRecord Then
On Error Goto Error Handler
Exit Do
'This &quot;Else&quot; may not even be needed
'Else
' Goto ErrorHandler 'Or Err.Raise xzy
'End IF
On Error Goto Error Handler
Loop

end with

If the objec Rst is large, then this will be the optimised way to do it.


Great Thread Guys.. lets keep on working...

All the best..
Praveen Menon

 

[CCLINT]

PraveenMenon: Using sunaj and my example together, for this example, do you really need a &quot;With&quot; statement(in the loop I mean)?
(I had orig. copied the wrong section of sunja's code)
Otherwise, as sunaj pointed out:
>Using with or not also makes very little or no difference...



Set f1 = Rst.Fields(&quot;Data&quot
Set f2 = Rst.Fields(&quot;pres&quot

Do
s = CStr(f1) & &quot; &quot; & CStr(f2)
On Error resume next
Rst.MoveNext
If Err.Number = AdErrNoCurrentRecord Then
On Error Goto Error Handler
Exit Do
'This &quot;Else&quot; may not even be needed
'Else
' Goto ErrorHandler 'Or Err.Raise xzy
'End IF
On Error Goto Error Handler
Loop
[/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!

 

[CCLINT]

>Using &quot;With&quot; or not also makes very little or no difference...

Actually, it is usually about about 8%-10% faster.

But, as always, the question concerning using &quot;With&quot; has to be raised:

1. at what point should I use it? (when referencing 2 or 3 properties, or say, when I reference 10 properties, and, if in a loop or not.

2. Even though it is usually 8-10% faster, at what point is it not needed, even when I reference 10 properties?

The speed difference in time, not percent, is so minimal that usnless the proceedure is part of a big collection of tasks or processes that are being run together, one after the other, before giving control or results back to the user, it probably isn't needed. Otherwise, Yes.
It also does make code sometimes easier to read....
[/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!

 

[sunaj]

I found another tweak that increases performance considerably:

Set the CacheSize of the recordset. In this case I'm so 'lucky' that I know how many rows I'll retrieve (which of course rarely is the case). Setting the CacheSize to the number of rows retrieved increases the speed by another 50-60% ! (because the database only has to be contacted once).

Adding Rst.CacheSize = CLng just after set rst = new recordset cuts the loop time down to just under 50% percent of the fastest loop (predefinded fields).
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[mattKnight]

Hi all,

for some reason, this thread has escaped my notice...

I've read it and learned some very interesting stuff, this often the case.

I have an easy[/bold] question :-0

is there any time penalty or advantage in using

recordset.fields("MyField")

over

recordset!MyField
'assuming of course MyField is the first field in table

over

recordset.fields(0)
'assuming of course MyField is the first field in table

I can see that using pre-assigned values is quicker, but still interesting to ask.

However, I am more concerned by CClintconcerns over the professionalism in this forum. I agree that there are many questions raised that could be answered by users doing their own research and learning either using MSDN or Forum resources. However, it is much easier to ask, especially if the questioner has not truely understood what their question is, than to wade through the archives (even using the search) maybe I need to learn search techniques
so far I agree, but I think that for the &quot;professionals&quot; (or other label) to not involve themselves in the easy questions will detract from the forum in a major fashion. There are many times when I have answered a question and someone else has pointed out an alternative solution. In the cases where the better solution has been posted, I have learned and in the times where it is less suitable an interesting discussion has ensued. IMHO, this forum will be a much poorer place without as full participation fom everyone as they can give, according to their skill and time availability.

There have been some lively discussions in this forum that have resulted from, on the face of it, easy questions. For my part, I try to look at questions and ask what can I learn from answering this...

Perhaps we should transfer this question to a differnt thread and throw it open to evryone for their thoughs, in stead of it being obscured by a very thorough and in-depth answer to a an ADO performance question.

I look forward to reading your thoughts people (BTW I am not that p'd off by it honest)

Matt

 

[CCLINT]

mattKnight:
>There are many times when I have answered a question and someone else has pointed out an alternative solution

Very True. Therefore my comment under point #3.
I guess my major concern is with those who do not reallly contribute to the forum at all, but are looking for just a fast answer with-out making an effort at all on their own, or even respond with a note as to whether or not the answers helped, or with even just a 'thank you'.
Again, this is not a give-me only forum.
Ones who are active and at times suggest help themselves and who respond to answers given, or have tried to find an answer theirself are excluded from my comments, of course.

Therefore, maybe it would be helpful to start checking the posters stats to see if this is true or not.

===================================================================
>is there any time penalty or advantage in using recordset.fields(&quot;MyField&quot

Yes there is.
Using the absolute field position .fields(0) is faster than using the field's name.

Using recordset!MyField is supposed to be even more efficient, but I prefer not to use this syntax for a couple of reasons.

If you are not looping through fields and/or refering to more than one or two field properties of the same field, then using absolute field position (unless you prefer using: !MyField) is the fastest way, however least flexible. If you change the field's absoulute position, you have to also change the code, unless you use consants, making the code changes easier.
Other than that, using a set field variable, as in sunaj's example, seems to be most efficient.
[/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!

 

[pgk]

Hi Folks,
A very interesting thread with loads of info. Thanks for sharing it us. A great help to new programmers like me and hence I have given 3 guys here stars.

I was not clear on Sunaj's last tweak: how do you determine the cache size to be used? Hope it helps. Let me know what happens.
With regards,
PGK

 

[sunaj]

Thanks for the star

In the case of my test I knew how many records that I was going to return, which of course was optimal (CacheSize=no of records). If you don't know, you'll have to guess. The default value is 1 (!), so if you know the order of magnitude then (100dres, 1000ands?) you could set the cachesize to that.


Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[mattKnight]

CCLint,

Having read your response and the 'original' post, I do now understand and appreciate your position. I still feel that

> IMHO, this forum will be a much poorer place without as full participation fom everyone as they can give, according to their skill and time availability.

However, I do look forward to reading & being involved in some more generic discussions!

Matt

 

[Dazz22]

Just thought I would make a contribution to the &quot;easy question&quot; debate.

As a programmer I would put myself in the beginner/intermediate bracket, so there is still alot to learn and i think this place is a good a place as any.

On a daily basis I try and read through all the questions posted and there is always something new to learn. For me, alot of this new insight comes from a question which a variety of people have been able to answer. The answer obviously came easy to the person who answered the post, but they may have posted a piece of code which is new to me, or would help me improve my existing code. What I am trying to say is, how do you define an easy question, when you have such a variety of skills ranging from beginner to genius? Obviously, some questions are plain silly and don't deserve a response, but I guess the answer may be useful to someone??

There are certain members (CajunCenturion, Foada, CClint, Sunaj) of this forum who I have a great amount of respect for. They obviously put alot of effort into keeping this forum alive & professional. Whenever I read one of their posts I feel like I am learning the 'correct' way to achieve things. However, I think they may also be one of the reasons why I don't try and reply to posts as often as I would like. The main reasons being that 1) I am normally beaten to giving the answer as i have to do a bit of research first, and 2) I always feel that there will be a better answer on the way from one of the more experienced guys!

This is not an attack guys, but I think CClint was on the right track when he said that some of the easier questions should be answered by the less experienced members. In the long run it will only benefit us less experienced programmers and help us to learn things we didn't know previously. This isn't to say that your input should disappear altogether. On the whole it is invaluable.

I hope this all came across correct??
I guess there will be no reply's to my next question!! To get what you want, you have to go through the crap, but to get through the crap you have to know what you want...