Looping through ADO recordset 1

[PatrickIRL]

Hi,

Recently had a comment made, by a consultant, on the way I looped through an ADO recordset. What I always do is this ...

Do While Not rs.eof
    rs.movenext
loop

The comment was that this was the slowest mthod possible for looping. Is this comment correct??? He made no reference to an alternative, which I suppose can be taken to mean "go look and search, prove me wrong", well that's the way I took it up anyway.

Can't find anything to the contrary to prove that one method is faster than another, any ideas??

Patrick

 

[TysonLPrice]

You didn't mention the type of data base you are using and what is surrounding your loop, like the cursors, type of call, etc. Here is an article using SQL 2000 that may give you some ideas:

http://www.sql-server-performance.com/visual_basic_performance.asp

 

[TysonLPrice]

Your question interested me because that's how I do it and looked around some more. Here's a couple more links I saw:

http://www.devx.com/vb2themax/Tip/18503

http://databases.aspfaq.com/database/should-i-use-recordset-iteration-or-getrows-or-getstring.html

I'd be interested in what the gurus here have to say.

 

[PatrickIRL]

I use this method for any database I've used, Access, SQL Server (7/2000), MySQL. Almost always use adOpenStatic, because I look for the recordcount.

Personally I don't have an issue with it but like you I was more curious than anything. Thanks for the links, will check them out.

Patrick

 

[Andrzejek]

I use:

Dim rs As ADODB.RecordSet
Dim i As Integer

Set rs = New ADODB.Recordset
rs.CursorType = adOpenForwardOnly
rs.LockType = adLockReadOnly
rs.CursorLocation = adUseClient
rs.open "Select ...", Cn

For i = 1 to rs.RecordCount
    'Do your magic here
    rs.movenext
Next i

rs.Close
Set rs = Nothing

I've been told that this is a faster way to loop because checking EOF takes time - after every loop cycle it has to be check if you have reached EOF which makes you go to the end of your recordset, and if the answer is "No, keep going" you go back to your location in a recordset and loop again. Then check for EOF - go to the end, and on, and on.

To be honest, I have never check the difference in performance, but maybe I should.

---- Andy

 

[PatrickIRL]

TysonLPrice:
The DEVX article is interesting, assigning fields and then looping, which can be quicker (I assume that's based on the number of records in the set).

Andrzejek:
That's a good point also.


Well, more work to do!!

Thanks all,

Patrick

 

[MakeItSo]

For i = 1 to rs.RecordCount
    'Do your magic here

Only as long as your magic doesn't involve any "INSERT or DELETE" statement. ;-)

[blue]Help us, join us, participate
IAHRA - International Alliance of Human Rights Advocates[/blue]

 

[Foada]

Check this thread222-394313

If you choose to battle wits with the witless be prepared to lose.

 

[gmmastros]

Obviously, performance is an issue or you wouldn't have raised the question. I think the .GetRow or the .GetStrings method of the recordset object is probably the fastest, then you loop through the recordset and do whatever needs to be done.

In my experience, the differences in pulling the data out of a recordset is minimal. More specifically, by changing the query, often times you can get better performance. Things like proper joins, using stored procedures, and optimizing your indexes have a bigger impact on overall performance than the method of pulling data out of a recordset.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JoeAtWork]

I've always used the .EOF method, mainly because it was always reliable, unlike .RecordCount.

Off the top of my head, I think if you are looking for improved performance, factors more important than how you iterate is the question of whether you need to iterate at all? If you can accomplish the same thing with a well-crafted SQL Statement (preferably contained in a stored procedure), that is almost always the better choice than looping through recordsets.

 

[BobRodes]

Strong thread, foada. The reason that the preassignment (creating a variable of type ADODB.Field and assigning one of the fields in the loop to it) is faster is because of the requirement to parse the string reference to the field at runtime. If you do that over and over inside a loop, you take a performance hit. If you do it outside the loop, and return an object pointer, you get better performance.

bob

 

[PatrickIRL]

Totally agree Bob, I've never seen this before and I've amended my code to use it, have to say it is snappier, star to foada for pointing me towards the thread but also a nod to those who contributed to that thread and this one.

Thanks all,

Patrick

 

[Foada]

I guess my memory is not as bad as I thought. It was a while back when we were discussing this.

If you choose to battle wits with the witless be prepared to lose.

 

[harebrain]

...checking EOF takes time - after every loop cycle it has to be check if you have reached EOF which makes you go to the end of your recordset, and if the answer is "No, keep going" you go back to your location in a recordset and loop again...

Can you support that that's what's really happening? Decades ago, when mainframes read nothing but sequential access files, a read instruction always read one record ahead, thus being aware of an impending EOF condition during the processing of the final record. I'd be amazed if such a simple technique had been forgotten. (Or is it a clever technique that has escaped the folks behind ADO? )

 

[BobRodes]

I would like to see that support too.