Looping variables - Integer vs Long

[Trevor Best]

I did a test once using a looping variable, first dimmed as Integer,
then as Long. I found the Integer was quicker at looping. I knew this to
be true back in the 16 bit days where the CPU's (80286) word size was 16
bits same as an integer.

Now with a 32 bit CPU I would have expected the long to be faster as
it's the same size as the CPU's word size so wouldn't need sawing in
half like a magician's assistant to calculate on like an integer would.
So why is an Integer still faster than a Long?

Is VBA still stuck in 16 bits somewhere?

[lylefair]

Strange! I would have predicted the longs as faster.
Can you post your looping code so that we can test and see this marvel?

[polite person]

On Fri, 28 Oct 2005 12:43:07 +0100, Trevor Best <no****@localhost.invalid> wrote:

I did a test once using a looping variable, first dimmed as Integer,
then as Long. I found the Integer was quicker at looping. I knew this to
be true back in the 16 bit days where the CPU's (80286) word size was 16
bits same as an integer.

Now with a 32 bit CPU I would have expected the long to be faster as
it's the same size as the CPU's word size so wouldn't need sawing in
half like a magician's assistant to calculate on like an integer would.
So why is an Integer still faster than a Long?

Is VBA still stuck in 16 bits somewhere?

see
http://groups.google.com.au/group/mi...98955791f1afee
towards the end of the thread for some information on excodes, the "fuller explanation" cited there
seems to have vanished.

[Steve Jorgensen]

On Fri, 28 Oct 2005 12:43:07 +0100, Trevor Best <no****@localhost.invalid>
wrote:

I did a test once using a looping variable, first dimmed as Integer,
then as Long. I found the Integer was quicker at looping. I knew this to
be true back in the 16 bit days where the CPU's (80286) word size was 16
bits same as an integer.

Now with a 32 bit CPU I would have expected the long to be faster as
it's the same size as the CPU's word size so wouldn't need sawing in
half like a magician's assistant to calculate on like an integer would.
So why is an Integer still faster than a Long?

Is VBA still stuck in 16 bits somewhere?

What's really odd is that I read something from Microsoft once stating that
VBA converts bytes and integers to a Long for processing, so it's more
efficient to define everything that way to start with. I would have expected
Long to give the better performance.

[David W. Fenton]

Trevor Best <no****@localhost.invalid> wrote in
news:43***********************@news.zen.co.uk:

I did a test once using a looping variable, first dimmed as
Integer, then as Long. I found the Integer was quicker at looping.
I knew this to be true back in the 16 bit days where the CPU's
(80286) word size was 16 bits same as an integer.

Now with a 32 bit CPU I would have expected the long to be faster
as it's the same size as the CPU's word size so wouldn't need
sawing in half like a magician's assistant to calculate on like an
integer would. So why is an Integer still faster than a Long?

Is VBA still stuck in 16 bits somewhere?

Shouldn't one choose the variable type according to how many items
you're looping?

That is, if you need to loop 60K items, then you *can't* use an
integer, no?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Trevor Best]

lylefair wrote:

Strange! I would have predicted the longs as faster.
Can you post your looping code so that we can test and see this marvel?

OK, from my OP it was in the past <g>, I just did one now and the Long
was faster :-\

Output from code below:
Int Loop: 1.359
Lng Loop: 1.265625

Sub LoopTest()
Dim intLoop1 As Integer
Dim lngLoop1 As Long
Dim intLoop2 As Integer
Dim lngLoop2 As Long

Dim sngStart As Single
Dim sngETInt As Single
Dim sngETLng As Single

sngStart = Timer()
For intLoop1 = 1 To 10000
For intLoop2 = 1 To 10000
Next
Next
sngETInt = Timer() - sngStart

sngStart = Timer()
For lngLoop1 = 1 To 10000
For lngLoop2 = 1 To 10000
Next
Next
sngETLng = Timer() - sngStart

Debug.Print "Int Loop: " & sngETInt
Debug.Print "Lng Loop: " & sngETLng

End Sub

[Trevor Best]

I should have tested it again before posting, it's now the other way
round, I'm using A2K2 BTW. If anyone who has 97 care to try it and
report back, the code is in response to Lyle's followup.

[Trevor Best]

David W. Fenton wrote:

Shouldn't one choose the variable type according to how many items
you're looping?

That is, if you need to loop 60K items, then you *can't* use an
integer, no?

But if you know your loop is < 32K...

I generally use Longs anyway.

[Arno R]

"Trevor Best" <no****@localhost.invalid> schreef in bericht news:43***********************@news.zen.co.uk...

I should have tested it again before posting, it's now the other way
round, I'm using A2K2 BTW. If anyone who has 97 care to try it and
report back, the code is in response to Lyle's followup.

Here are my results from the Netherlands (tested in A97)

1st test:
Int Loop: 0,827875
Lng Loop: 0,79675

2nd test:
Int Loop: 0,8275
Lng Loop: 0,796375

3rd test:
Int Loop: 0,827375
Lng Loop: 0,81225

Arno R

[lylefair]

Yes, I found that longs were faster.

BUTTTTTTTTTTTTTT ...

Well, it's morning here and I am still a little fuzzy headed but would
you, please, reverse the order of calling the loops (see below) and
tell me if longs are faster when the integer loop is called second.
(note that I have not changed the order of the debug.print calls).
My results don't make any sense to me at all, so maybe I am doing
something tres stupid!

Sub LoopTest()
Dim intLoop1 As Integer
Dim lngLoop1 As Long
Dim intLoop2 As Integer
Dim lngLoop2 As Long

Dim sngStart As Single
Dim sngETInt As Single
Dim sngETLng As Single

sngStart = Timer()
For lngLoop1 = 1 To 10000
For lngLoop2 = 1 To 10000
Next
Next
sngETLng = Timer() - sngStart

sngStart = Timer()
For intLoop1 = 1 To 10000
For intLoop2 = 1 To 10000
Next
Next
sngETInt = Timer() - sngStart

Debug.Print "Int Loop: " & sngETInt
Debug.Print "Lng Loop: " & sngETLng

End Sub

[Arno R]

"lylefair" <ly***********@aim.com> schreef in bericht news:11*********************@g44g2000cwa.googlegro ups.com...

Yes, I found that longs were faster.

BUTTTTTTTTTTTTTT ...

Well, it's morning here and I am still a little fuzzy headed but would
you, please, reverse the order of calling the loops (see below) and
tell me if longs are faster when the integer loop is called second.
(note that I have not changed the order of the debug.print calls).
My results don't make any sense to me at all, so maybe I am doing
something tres stupid!

Sub LoopTest()
Dim intLoop1 As Integer
Dim lngLoop1 As Long
Dim intLoop2 As Integer
Dim lngLoop2 As Long

Dim sngStart As Single
Dim sngETInt As Single
Dim sngETLng As Single

sngStart = Timer()
For lngLoop1 = 1 To 10000
For lngLoop2 = 1 To 10000
Next
Next
sngETLng = Timer() - sngStart

sngStart = Timer()
For intLoop1 = 1 To 10000
For intLoop2 = 1 To 10000
Next
Next
sngETInt = Timer() - sngStart

Debug.Print "Int Loop: " & sngETInt
Debug.Print "Lng Loop: " & sngETLng

End Sub

3 tests with the above code tested in A97:
Int Loop: 0,828125
Lng Loop: 0,811625

Int Loop: 0,843625
Lng Loop: 0,812125

Int Loop: 0,843125
Lng Loop: 0,811625
Same tests but now in A2k:
Int Loop: 0,858875
Lng Loop: 0,781125

Int Loop: 0,859125
Lng Loop: 0,796

Int Loop: 0,859375
Lng Loop: 0,811875

So Longs are still faster here with this reversed order.
I am too 'fuzzy' (even if it's afternoon here) to try to find out why subsequent calls of this code lead to a longer time for the 'long' ;-)

I think I will 'go' for the long, as I always do just to avoid the famous 'overflow' error.

Arno R

[lylefair]

I see I didn't post my results:

Int Loop: 1.493219
Lng Loop: 1.683812

[lylefair]

Still fuzzy ... this is with AC2K3 Windows XP home and Intel Centrino
Processor at 1.5

[Douglas J. Steele]

"Arno R" <ar***********@tiscali.nl> wrote in message
news:43********************@dreader2.news.tiscali. nl...

3 tests with the above code tested in A97:
Int Loop: 0,828125
Lng Loop: 0,811625

Int Loop: 0,843625
Lng Loop: 0,812125

Int Loop: 0,843125
Lng Loop: 0,811625
Same tests but now in A2k:
Int Loop: 0,858875
Lng Loop: 0,781125

Int Loop: 0,859125
Lng Loop: 0,796

Int Loop: 0,859375
Lng Loop: 0,811875

So Longs are still faster here with this reversed order.
I am too 'fuzzy' (even if it's afternoon here) to try to find out why
subsequent calls
of this code lead to a longer time for the 'long' ;-)

I think I will 'go' for the long, as I always do just to avoid the famous
'overflow' error.

Arno R

Looks as though Access 2003 changed things:

Access 97
Int Loop: 0.7320625
Lng Loop: 0.7002813

Int Loop: 0.7297813
Lng Loop: 0.7097188

Int Loop: 0.7325938
Lng Loop: 0.7203438
Access 2003
Int Loop: 0.7296875
Lng Loop: 0.8619688

Int Loop: 0.7106562
Lng Loop: 0.8199688

Int Loop: 0.7412813
Lng Loop: 0.7815313

Mobile Intel Pentium 4 2.66Ghz, 512 Mb Ram

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

[David W. Fenton]

Trevor Best <no****@localhost.invalid> wrote in
news:43***********************@news.zen.co.uk:

David W. Fenton wrote:

Shouldn't one choose the variable type according to how many
items you're looping?

That is, if you need to loop 60K items, then you *can't* use an
integer, no?
But if you know your loop is < 32K...

Well, of course. My point is that you choose the data type for your
counter according to the requirements of the particular looping
operation.
I generally use Longs anyway.

I tend to use longs because I don't want to have to revisit the code
and fix it when the number of items exceeds the limits of an integer
data type. But in some limited cases where I definitely know that
can never ever happen, I use an integer.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"lylefair" <ly***********@aim.com> wrote in
news:11*********************@g47g2000cwa.googlegro ups.com:

I see I didn't post my results:

Int Loop: 1.493219
Lng Loop: 1.683812

Try making an MDE with your code and see if there's a difference
between the two versions, just to take the possibility of the
compilation state of the code/database out of the mix.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
news:t8********************@rogers.com:

"Arno R" <ar***********@tiscali.nl> wrote in message
news:43********************@dreader2.news.tiscali. nl...

3 tests with the above code tested in A97:
Int Loop: 0,828125
Lng Loop: 0,811625

Int Loop: 0,843625
Lng Loop: 0,812125

Int Loop: 0,843125
Lng Loop: 0,811625
Same tests but now in A2k:
Int Loop: 0,858875
Lng Loop: 0,781125

Int Loop: 0,859125
Lng Loop: 0,796

Int Loop: 0,859375
Lng Loop: 0,811875

So Longs are still faster here with this reversed order.
I am too 'fuzzy' (even if it's afternoon here) to try to find out
why subsequent calls
of this code lead to a longer time for the 'long' ;-)

I think I will 'go' for the long, as I always do just to avoid
the famous 'overflow' error.

Arno R

Looks as though Access 2003 changed things:

Access 97
Int Loop: 0.7320625
Lng Loop: 0.7002813

Int Loop: 0.7297813
Lng Loop: 0.7097188

Int Loop: 0.7325938
Lng Loop: 0.7203438
Access 2003
Int Loop: 0.7296875
Lng Loop: 0.8619688

Int Loop: 0.7106562
Lng Loop: 0.8199688

Int Loop: 0.7412813
Lng Loop: 0.7815313

Mobile Intel Pentium 4 2.66Ghz, 512 Mb Ram

The differences are so small, a few hundredths of a second in 10K
iterations, that I think it hardly makes a difference. For there to
be even a second's difference between the two, there would have to
be ~500,000 iterations. In that case, you couldn't use an integer,
anyway. Consider that, because of the upper limit of an integer, you
could never have a loop of more than about 3X the iterations in the
test, and then you'd barely be getting to the point of around 1/2
second difference between the two, either way.

So my conclusion:

It makes no difference from a performance standpoint which you
choose in any real-world sense.

So, again, the main issue is choosing the data type according to the
limits of the collection you're looping.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[lylefair]

Why not always use longs?

[David W. Fenton]

"lylefair" <ly***********@aim.com> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:

Why not always use longs?

Because it's not the narrowest possible data type for the purposes
of the loop at hand?

Here's another question:

Are For/Each loops faster than an indexed-based loop using a counter
(long or integer)?

That is, is this:

Dim ctl As Control

For Each ctl In Me.Controls
[]
Next ctl

faster than:

Dim i As Integer ' or Dim l As Long

Fpr i = 0 to (Me.Controls.Count - 1)
[]
Next i

I don't know, and tend not to use counter-based loops except when
dealing with collections of items for which there is no dedicated
object type to use in the For/Each structure.

I'd think that the For/Each loop would be faster because it doesn't
have to do an additional lookup on the index, but maybe that's not
correct.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[lylefair]

David W. Fenton wrote:

"lylefair" <ly***********@aim.com> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:

Why not always use longs?

Because it's not the narrowest possible data type for the purposes
of the loop at hand?

Why use the narrowest possible data type?
What's the advantage of this in the context of this thread which is, I
think, about the speed of loops?

[rkc]

David W. Fenton wrote:

Are For/Each loops faster than an indexed-based loop using a counter
(long or integer)?

That is, is this:

Dim ctl As Control

For Each ctl In Me.Controls
[]
Next ctl

faster than:

Dim i As Integer ' or Dim l As Long

Fpr i = 0 to (Me.Controls.Count - 1)
[]
Next i

I don't know, and tend not to use counter-based loops except when
dealing with collections of items for which there is no dedicated
object type to use in the For/Each structure.

I'd think that the For/Each loop would be faster because it doesn't
have to do an additional lookup on the index, but maybe that's not
correct.

I would think the opposite since either a variant or an object is
implicitly created and set equal to each member of the collection
by index in turn.

[Chris2]

"Trevor Best" <no****@localhost.invalid> wrote in message
news:43***********************@news.zen.co.uk...

I did a test once using a looping variable, first dimmed as Integer,
then as Long. I found the Integer was quicker at looping. I knew this to be true back in the 16 bit days where the CPU's (80286) word size was 16 bits same as an integer.

Now with a 32 bit CPU I would have expected the long to be faster as
it's the same size as the CPU's word size so wouldn't need sawing in
half like a magician's assistant to calculate on like an integer would. So why is an Integer still faster than a Long?

Is VBA still stuck in 16 bits somewhere?

I modified the code somewhat to include running the loops in both
orders.

Sub LoopTest_IntLong_BothWays()
Dim intLoop1 As Integer
Dim lngLoop1 As Long
Dim intLoop2 As Integer
Dim lngLoop2 As Long
Dim intLoop3 As Integer
Dim lngLoop3 As Long
Dim intLoop4 As Integer
Dim lngLoop4 As Long

Dim sngStart As Single
Dim sngETInt1 As Single
Dim sngETLng1 As Single
Dim sngETInt2 As Single
Dim sngETLng2 As Single
'--------------------------------------
sngStart = Timer()
For intLoop1 = 1 To 10000
For intLoop2 = 1 To 10000
Next
Next
sngETInt1 = Timer() - sngStart

sngStart = Timer()
For lngLoop1 = 1 To 10000
For lngLoop2 = 1 To 10000
Next
Next
sngETLng1 = Timer() - sngStart
'--------------------------------------
sngStart = Timer()
For lngLoop3 = 1 To 10000
For lngLoop4 = 1 To 10000
Next
Next
sngETLng2 = Timer() - sngStart

sngStart = Timer()
For intLoop3 = 1 To 10000
For intLoop4 = 1 To 10000
Next
Next
sngETInt2 = Timer() - sngStart
'--------------------------------------
Debug.Print "#1 Int Loop: " & sngETInt1
Debug.Print "#1 Lng Loop: " & sngETLng1
Debug.Print "#2 Lng Loop: " & sngETLng2
Debug.Print "#2 Int Loop: " & sngETInt2

End Sub

Pentium III 533Mhz; 512MB RAM
Win2k SP-4; Access 2k SP-3; (JET SQL 4.0 SP-8: not that JET matters
here)
'--------------------------------------
#1 Int Loop: 4.112625
#1 Lng Loop: 4.38925
#2 Lng Loop: 4.384625
#2 Int Loop: 4.117813
'--------------------------------------
#1 Int Loop: 4.384625
#1 Lng Loop: 4.648812
#2 Lng Loop: 4.556375
#2 Int Loop: 4.237688
'--------------------------------------
#1 Int Loop: 4.198375
#1 Lng Loop: 4.439063
#2 Lng Loop: 4.438562
#2 Int Loop: 4.127062
'--------------------------------------
#1 Int Loop: 4.19625
#1 Lng Loop: 4.437938
#2 Lng Loop: 4.436438
#2 Int Loop: 4.134937
'--------------------------------------

Sincerely,

Chris O.

[rkc]

David W. Fenton wrote:

I'd think that the For/Each loop would be faster because it doesn't
have to do an additional lookup on the index, but maybe that's not
correct.

You're batting 1000 in the right department.

For Each is ridiculously faster than For Next.

17,000 member collection.

For Each: 3.955875

For Next: 34.49275

[Thelma Lubkin]

David W. Fenton <dX********@bway.net.invalid> wrote:

<snip>
: ...and tend not to use counter-based loops except when
: dealing with collections of items for which there is no dedicated
: object type to use in the For/Each structure.

But counter-based loops are convenient when you need the
counter value within the loop's code block, e.g. aray(i) = ...

--thelma

: David W. Fenton http://www.bway.net/~dfenton
: dfenton at bway dot net http://www.bway.net/~dfassoc

[Trevor Best]

Chris2 wrote:

I modified the code somewhat to include running the loops in both
orders. [snip] Pentium III 533Mhz; 512MB RAM
Win2k SP-4; Access 2k SP-3; (JET SQL 4.0 SP-8: not that JET matters
here)

Strange as your ints were consistently faster than your longs, on mine I
get:
#1 Int Loop: 1.406
#1 Lng Loop: 1.26475
#2 Lng Loop: 1.374125
#2 Int Loop: 1.421125
-------------------
#1 Int Loop: 1.359
#1 Lng Loop: 1.265625
#2 Lng Loop: 1.359
#2 Int Loop: 1.405625
-------------------
#1 Int Loop: 1.3585
#1 Lng Loop: 1.281125
#2 Lng Loop: 1.374875
#2 Int Loop: 1.405875
-------------------

Athlon 2800+ 1GBRAM, Win2KSP4, Access 2002 SP3

I did try it on my SO's machine, similar spec to yours (P500) and got
similar results so it may be down to CPU type.

[Trevor Best]

David W. Fenton wrote:

It makes no difference from a performance standpoint which you
choose in any real-world sense.

I'm in no way concerned about real world execution time, if my procedure
took a few nano seconds longer to execute than doing it another way then
the universe isn't going to implode or anything.

But there's no harm in doing things in the most efficient way possible
if it's no trickier to do than a less efficient way.

My concern for starting the thread was the unexpected results I got the
first time I ran this test, which was long ago and most probably when I
still had a PIII CPU (where the unexpected results tend to occur). I
didn't make a big deal out of it back then, it's just another recent
thread in this ng reminded me of it.

[David W. Fenton]

"lylefair" <ly***********@aim.com> wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:

David W. Fenton wrote:

"lylefair" <ly***********@aim.com> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:

> Why not always use longs?

Because it's not the narrowest possible data type for the
purposes of the loop at hand?

Why use the narrowest possible data type?
What's the advantage of this in the context of this thread which
is, I think, about the speed of loops?

Because it's been demonstrated that the difference in loops is not
significant in any loop that could be handled by an integer. Thus,
there's no reason to prefer one over the other for performance
reasons, so the only reason to choose one over the other is because
it's the appropriate narrowest data type for the purpose.

Declaring your counter as an integer tells another programmer
reading the code something about the number of iterations. There
are, in fact, very few cases where processing more than 32K-odd
items is best done in a loop, so declaring your counter as a long
ought to be an exceptional situation that screams "There are a whole
buttload of items in this collection that we're looping through!" If
you use a long in all loops, you lose that information.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:g0******************@twister.nyroc.rr.com:

David W. Fenton wrote:

Are For/Each loops faster than an indexed-based loop using a
counter (long or integer)?

That is, is this:

Dim ctl As Control

For Each ctl In Me.Controls
[]
Next ctl

faster than:

Dim i As Integer ' or Dim l As Long

Fpr i = 0 to (Me.Controls.Count - 1)
[]
Next i

I don't know, and tend not to use counter-based loops except when
dealing with collections of items for which there is no dedicated
object type to use in the For/Each structure.

I'd think that the For/Each loop would be faster because it
doesn't have to do an additional lookup on the index, but maybe
that's not correct.

I would think the opposite since either a variant or an object is
implicitly created and set equal to each member of the collection
by index in turn.

Well, the point is how you look up the item. In the former example,
you use ctl to operate on it -- you have your reference already
created for you. In the latter, though, you still have to look it up
by index. I don't know if the overhead involved in setting the
implicit reference (I think you're correct on that) is more than the
overhead for looking up the item by index.

I'm not sure why you mention variants, as there are none in the
example I give, and I'd never use a variant for any loop that has a
dedicated object variable type. Navigating the items in a listbox is
one case where you need variants, for instance, or vavigating a
custom collection that's storing control references.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:2P*******************@twister.nyroc.rr.com:

David W. Fenton wrote:

I'd think that the For/Each loop would be faster because it
doesn't have to do an additional lookup on the index, but maybe
that's not correct.

You're batting 1000 in the right department.

For Each is ridiculously faster than For Next.

17,000 member collection.

For Each: 3.955875

For Next: 34.49275

That actually surprises me that it would be such a great difference.

Where did you get a 17K-member collection to work on? !!!!

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Trevor Best <no****@localhost.invalid> wrote in
news:43*********************@news.zen.co.uk:

David W. Fenton wrote:

It makes no difference from a performance standpoint which you
choose in any real-world sense.
I'm in no way concerned about real world execution time, if my
procedure took a few nano seconds longer to execute than doing it
another way then the universe isn't going to implode or anything.

But there's no harm in doing things in the most efficient way
possible if it's no trickier to do than a less efficient way.

But counting milliseconds of difference as efficiency seems
completely wrong to me. If you insist on preferring one data type
over the other (remember, there's only a choice for collections
smaller than the upper limit of the integer data type, though that
probably accounts for 99.9% of real-world loops), especially if
you've decided on Long, then you're sacrificing code clarity, in my
opinion.

Also, keep in mind that it seems that different versions of Access
and even different CPUs seem to flip the results in favor of the
other variable type, so if you're optimizing for millisecends,
you're only going to be successful on *some* of the platforms on
which the code could run. If you know what all those are, well,
that's OK, but I just don't see that it's worth the effort to figure
out if you've got a consistent platform to optimize to.
My concern for starting the thread was the unexpected results I
got the first time I ran this test, which was long ago and most
probably when I still had a PIII CPU (where the unexpected results
tend to occur). I didn't make a big deal out of it back then, it's
just another recent thread in this ng reminded me of it.

I'm always suspcious of VBA code tests, because order of execution
can make a difference, as well as the compilation state of the code
in the rest of the MDB you're testing in (assuming you're not using
a frsh MDB). When the differences are so slight, they look to me to
be well within the margin of error for what's being measured.

Given that no human being would be able to tell which was faster
(even on the maximum-size integer loop), I don't see any point in
even considering performance for counter-based loops. Choose your
counter's data type to reflect the size of the collection being
looped and leave it at that.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[rkc]

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:g0******************@twister.nyroc.rr.com:

David W. Fenton wrote:

Are For/Each loops faster than an indexed-based loop using a
counter (long or integer)?

That is, is this:

Dim ctl As Control

For Each ctl In Me.Controls
[]
Next ctl

faster than:

Dim i As Integer ' or Dim l As Long

Fpr i = 0 to (Me.Controls.Count - 1)
[]
Next i

I don't know, and tend not to use counter-based loops except when
dealing with collections of items for which there is no dedicated
object type to use in the For/Each structure.

I'd think that the For/Each loop would be faster because it
doesn't have to do an additional lookup on the index, but maybe
that's not correct.

I would think the opposite since either a variant or an object is
implicitly created and set equal to each member of the collection
by index in turn.

Well, the point is how you look up the item. In the former example,
you use ctl to operate on it -- you have your reference already
created for you. In the latter, though, you still have to look it up
by index. I don't know if the overhead involved in setting the
implicit reference (I think you're correct on that) is more than the
overhead for looking up the item by index.

I'm not sure why you mention variants, as there are none in the
example I give, and I'd never use a variant for any loop that has a
dedicated object variable type. Navigating the items in a listbox is
one case where you need variants, for instance, or vavigating a
custom collection that's storing control references.

I mentioned Variant because other than an object it is the only
data type that can used in a For Each loop.

For Each [Variant or Object] in Collection.

[rkc]

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:2P*******************@twister.nyroc.rr.com:

David W. Fenton wrote:

I'd think that the For/Each loop would be faster because it
doesn't have to do an additional lookup on the index, but maybe
that's not correct.

You're batting 1000 in the right department.

For Each is ridiculously faster than For Next.

17,000 member collection.

For Each: 3.955875

For Next: 34.49275

That actually surprises me that it would be such a great difference.

Where did you get a 17K-member collection to work on? !!!!

The 17,000 member collection came from the A's and B's of a table
of all the words in the English language.

[Trevor Best]

David W. Fenton wrote:

Because it's been demonstrated that the difference in loops is not
significant in any loop that could be handled by an integer. Thus,
there's no reason to prefer one over the other for performance
reasons, so the only reason to choose one over the other is because
it's the appropriate narrowest data type for the purpose.
So why do MS do things like make the Instr() function return a variant
instead of a long? AFAIR a string cannot be more than 2GB.
Declaring your counter as an integer tells another programmer
reading the code something about the number of iterations. There
are, in fact, very few cases where processing more than 32K-odd
items is best done in a loop, so declaring your counter as a long
ought to be an exceptional situation that screams "There are a whole
buttload of items in this collection that we're looping through!" If
you use a long in all loops, you lose that information.

In a loop, 32K or less is still a buttload, personally I don't look at a
loop another programmer has done and wonder if it loops under or over 32K.

[Trevor Best]

rkc wrote:

The 17,000 member collection came from the A's and B's of a table
of all the words in the English language.

Did you find anything that rhymes with orange?

[Trevor Best]

David W. Fenton wrote:

But counting milliseconds of difference as efficiency seems
completely wrong to me. If you insist on preferring one data type
over the other (remember, there's only a choice for collections
smaller than the upper limit of the integer data type, though that
probably accounts for 99.9% of real-world loops), especially if
you've decided on Long, then you're sacrificing code clarity, in my
opinion.
I don't think the data type of a looping variable helps clarity in any way.
Given that no human being would be able to tell which was faster
(even on the maximum-size integer loop), I don't see any point in
even considering performance for counter-based loops. Choose your
counter's data type to reflect the size of the collection being
looped and leave it at that.

You might not notice when you first write a procedure but then sometime
your procedure gets called in a bigger loop. Using a long is a way to
future proof as much as possible ATM, I remember when strings in BASIC
were limited to 255 characters, now there's a lot more. Who knows what
other things will get such a boost. We've already seen functions like
LOF() fail because that's a long and limited to 2GB, files can be over
that now. WE have no control over built in functions like that failing
but we can cater for our own variables as much as possible.

I can't count the number of times I've had to re-dim a load of integers
as longs because the original programmer didn't foresee the loops going
over 32K.

[Wayne Gillespie]

On Mon, 31 Oct 2005 08:43:32 +0000, Trevor Best <no****@localhost.invalid> wrote:

rkc wrote:

The 17,000 member collection came from the A's and B's of a table
of all the words in the English language.

Did you find anything that rhymes with orange?

Or purple or silver?

[rkc]

Trevor Best wrote:

rkc wrote:

The 17,000 member collection came from the A's and B's of a table
of all the words in the English language.

Did you find anything that rhymes with orange?

Orange!
Orange, Orange
Bo, borange
Banana, Fanna, Foe, Forange
Fee, Fie, Moe, Morange
Orange.

[Trevor Best]

rkc wrote:

Trevor Best wrote:

rkc wrote:

The 17,000 member collection came from the A's and B's of a table
of all the words in the English language.

Did you find anything that rhymes with orange?

Orange!
Orange, Orange
Bo, borange
Banana, Fanna, Foe, Forange
Fee, Fie, Moe, Morange
Orange.

Gorringe.

[Trevor Best]

Wayne Gillespie wrote:

On Mon, 31 Oct 2005 08:43:32 +0000, Trevor Best <no****@localhost.invalid> wrote:

rkc wrote:

The 17,000 member collection came from the A's and B's of a table
of all the words in the English language.

Did you find anything that rhymes with orange?

Or purple or silver?

Urple
http://www.langmaker.com/db/eng_urple.htm

Ilver
http://www.ilvermotorboats.it/

[David W. Fenton]

Trevor Best <no****@localhost.invalid> wrote in
news:43***********************@news.zen.co.uk:

David W. Fenton wrote:

Because it's been demonstrated that the difference in loops is
not significant in any loop that could be handled by an integer.
Thus, there's no reason to prefer one over the other for
performance reasons, so the only reason to choose one over the
other is because it's the appropriate narrowest data type for the
purpose.

So why do MS do things like make the Instr() function return a
variant instead of a long? AFAIR a string cannot be more than 2GB.

So it can pass a Null directly through?

Declaring your counter as an integer tells another programmer
reading the code something about the number of iterations. There
are, in fact, very few cases where processing more than 32K-odd
items is best done in a loop, so declaring your counter as a long
ought to be an exceptional situation that screams "There are a
whole buttload of items in this collection that we're looping
through!" If you use a long in all loops, you lose that
information.

In a loop, 32K or less is still a buttload, personally I don't
look at a loop another programmer has done and wonder if it loops
under or over 32K.

Er, OK?

I don't know what you're arguing here.

First off, there's no clarity on whether long or integer is faster,
and the only thing that makes the decision based on "efficiency"
(I'd call it "faux efficiency") different from mine would be if the
long were uniformly faster. It is on some platforms and not on
others.

If it's the integer that is faster, then you're on exactly the same
page as me -- use integer for <32K, and long for over that.

Right?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:Pa*******************@twister.nyroc.rr.com:

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:g0******************@twister.nyroc.rr.com:

David W. Fenton wrote:

Are For/Each loops faster than an indexed-based loop using a
counter (long or integer)?

That is, is this:

Dim ctl As Control

For Each ctl In Me.Controls
[]
Next ctl

faster than:

Dim i As Integer ' or Dim l As Long

Fpr i = 0 to (Me.Controls.Count - 1)
[]
Next i

I don't know, and tend not to use counter-based loops except
when dealing with collections of items for which there is no
dedicated object type to use in the For/Each structure.

I'd think that the For/Each loop would be faster because it
doesn't have to do an additional lookup on the index, but maybe
that's not correct.

I would think the opposite since either a variant or an object is
implicitly created and set equal to each member of the collection
by index in turn.

Well, the point is how you look up the item. In the former
example, you use ctl to operate on it -- you have your reference
already created for you. In the latter, though, you still have to
look it up by index. I don't know if the overhead involved in
setting the implicit reference (I think you're correct on that)
is more than the overhead for looking up the item by index.

I'm not sure why you mention variants, as there are none in the
example I give, and I'd never use a variant for any loop that has
a dedicated object variable type. Navigating the items in a
listbox is one case where you need variants, for instance, or
vavigating a custom collection that's storing control references.

I mentioned Variant because other than an object it is the only
data type that can used in a For Each loop.

For Each [Variant or Object] in Collection.

Being a variant (i.e., a general-purpose data type) rather than a
specific data type, I'd expect it to not have the speed advantage
present for the narrow object types.

I wonder also if you could substitute:

Dim ctl As Object

for

Dim ctl As Control

and if there'd be a significant performance difference.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:gg*******************@twister.nyroc.rr.com:

David W. Fenton wrote:

Where did you get a 17K-member collection to work on? !!!!

The 17,000 member collection came from the A's and B's of a table
of all the words in the English language.

Sorry, I don't understand. What collection were you walking that you
populated from a data table?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[rkc]

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:gg*******************@twister.nyroc.rr.com:

David W. Fenton wrote:

Where did you get a 17K-member collection to work on? !!!!

The 17,000 member collection came from the A's and B's of a table
of all the words in the English language.

Sorry, I don't understand. What collection were you walking that you
populated from a data table?

A VBA.Collection populated from a DAO.Recordset.
It was a speed test. Nothing real life about it.

[David W. Fenton]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:PU*******************@twister.nyroc.rr.com:

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:gg*******************@twister.nyroc.rr.com:

David W. Fenton wrote:

Where did you get a 17K-member collection to work on? !!!!

The 17,000 member collection came from the A's and B's of a table
of all the words in the English language.

Sorry, I don't understand. What collection were you walking that
you populated from a data table?

A VBA.Collection populated from a DAO.Recordset.
It was a speed test. Nothing real life about it.

Aha. Well, then, from my point of view, not of much value.

I'm not really that interested in efficiency that shows up only in
circumstances that would never happen in real-world use.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[rkc]

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:PU*******************@twister.nyroc.rr.com:

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:gg*******************@twister.nyroc.rr.com :
David W. Fenton wrote:

>Where did you get a 17K-member collection to work on? !!!!

The 17,000 member collection came from the A's and B's of a table
of all the words in the English language.

Sorry, I don't understand. What collection were you walking that
you populated from a data table?

A VBA.Collection populated from a DAO.Recordset.
It was a speed test. Nothing real life about it.

Aha. Well, then, from my point of view, not of much value.

I'm not really that interested in efficiency that shows up only in
circumstances that would never happen in real-world use.

O.K. then I can tell you that looping through a collection of
20 controls when a form opens it makes no fucking difference at all.

[rkc]

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:Pa*******************@twister.nyroc.rr.com:

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:g0******************@twister.nyroc.rr.co m:

David W. Fenton wrote:
>Are For/Each loops faster than an indexed-based loop using a
>counter (long or integer)?
>
>That is, is this:
>
> Dim ctl As Control
>
> For Each ctl In Me.Controls
> []
> Next ctl
>
>faster than:
>
> Dim i As Integer ' or Dim l As Long
>
> Fpr i = 0 to (Me.Controls.Count - 1)
> []
> Next i
>
>I don't know, and tend not to use counter-based loops except
>when dealing with collections of items for which there is no
>dedicated object type to use in the For/Each structure.
>
>I'd think that the For/Each loop would be faster because it
>doesn't have to do an additional lookup on the index, but maybe
>that's not correct.

I would think the opposite since either a variant or an object is
implicitly created and set equal to each member of the collection
by index in turn.
Well, the point is how you look up the item. In the former
example, you use ctl to operate on it -- you have your reference
already created for you. In the latter, though, you still have to
look it up by index. I don't know if the overhead involved in
setting the implicit reference (I think you're correct on that)
is more than the overhead for looking up the item by index.

I'm not sure why you mention variants, as there are none in the
example I give, and I'd never use a variant for any loop that has
a dedicated object variable type. Navigating the items in a
listbox is one case where you need variants, for instance, or
vavigating a custom collection that's storing control references.
I mentioned Variant because other than an object it is the only
data type that can used in a For Each loop.

For Each [Variant or Object] in Collection.

Being a variant (i.e., a general-purpose data type) rather than a
specific data type, I'd expect it to not have the speed advantage
present for the narrow object types.

The speed advantage of a For Each is in the lookup of the Item.
The explanation on MSDN is that the For Each loop keeps track of
where it is so it is more efficient. Not exactly an in depth analysis.
I wonder also if you could substitute:

Dim ctl As Object

for

Dim ctl As Control
Of course you can.
and if there'd be a significant performance difference.

Not as far as a For Each vs. a For Next.