Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Speed Looping Through Arrays

Posted on 2000-05-08
Medium Priority
267 Views
The speed at which I loop through arrays seems to vary as i do it..

I have an array of strings with 100,000 items

as I loop through them and add values to each index.. it seems to slow down a great deal after about the 33000 index.

For x = 1 To 100000
If x Mod 1000 = 0 Then Me.Caption = x
Next x

when i run this.. it blazes through the first 30000 or so.. I almost cant see the values... then it slows down and i can see the numbers change..

any reason for this and what can i do to make the speed more consistant.
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 15
• 8
• 4
• +5

LVL 3

Author Comment

ID: 2789124
I will note that the following code segment slowed down at about x = 3000
For x = 1 To 100000
lsExecs(x) = Space(1024)
If x Mod 1000 = 0 Then Me.Caption = x
Next x

and this code got to about x = 7000 to 8000 when it slowed down

For x = 1 To 100000
lsExecs(x) = Space(512)
If x Mod 1000 = 0 Then Me.Caption = x
Next x

surprisingly.. it sped up again a bit at x = about 20000
0

LVL 18

Expert Comment

ID: 2789127
I think the array is paged out of memory at the higher values.
0

LVL 1

Expert Comment

ID: 2789145
I might be wrong, but I have an idea. Maybe VB calculates memory address offsets every time you index an element of the array. The only reason I think that is because you said it slows down at around 33000 index, which is right around where the integer range ends; maybe that causes VB to have to do extra stuff when calculating the offset.

Like I said I'm just guessing... I'm interested in what others will say.
0

LVL 1

Expert Comment

ID: 2789164
OK, I see you new comment now. It must be something to do with the way VB allocates and addresses space for the elements of the array. The bigger on element is, the sooner it slows down. Which means that those elements are probable on a different memory page, just like Deighton said. I mean I don't want to repeat that, just "thinking aloud"...
0

LVL 3

Author Comment

ID: 2789165
I will note that the following code segment slowed down at about x = 3000
For x = 1 To 100000
lsExecs(x) = Space(1024)
If x Mod 1000 = 0 Then Me.Caption = x
Next x

and this code got to about x = 7000 to 8000 when it slowed down

For x = 1 To 100000
lsExecs(x) = Space(512)
If x Mod 1000 = 0 Then Me.Caption = x
Next x

surprisingly.. it sped up again a bit at x = about 20000
0

LVL 32

Expert Comment

ID: 2789178
Sounds like memory access speed to me.  Do you start getting intermittent disk accesses after about 3.3Mb of data added (33000 * 100, 3000 * 1024)?  If so, more free memory should speed it up.  If you can remove something from memory, this should tell.

Also, try running the System Monitor (Start/Programs/Accessories/System Tools/System Monitor).  Make sure you are showing graphs for Allocated Memory, Free Memory, Page Ins, and Page Outs (If they aren't already showing, add them by clicking Edit / Add Item, Select Memory Manager, and choose them off the list).  Watch the graphs as your program runs, and see if you start getting paging when it slows down.
0

LVL 3

Author Comment

ID: 2789196
more poo poo...

ok.. the typical item length is 55 characters..

It does not page to disk if that is what you mean..  Im watching the drive light..

When i hit a breakpoint after looping..
and reset it to the beginning of the loop.. it loops througheverything.. all at the same speed.. very fast..
kind of like it is buffered..
0

LVL 32

Expert Comment

ID: 2789207
Let's do the math:

lsExecs(x) = Space(512) is 1024 Bytes
Loop 100000 times = 102,400,000 bytes!

Your application is swapping to virtual memory to store all that data.

My computer's hard drive works like crazy trying to run this code.

You may see a speed improvement if you first defrag your drive.
0

LVL 3

Author Comment

ID: 2789216
more...  if , after the loop.. i set the next statement to the dim statement right before the beginning..  it will do the first 30000 fast like before.. then slow down..

I thought about the integer/long 32767 number too.. but the number move based on the size of the strings in the array..
ie.. if i make each element smaller.. it gets through more items in the array before slowing down..   im not sure what
this would mean paging wise.. i have array of strings..  i gcould nderstand the paging if i had fixed length array.. where each item took up space on a page..   but i thoguht variable length strings were really just pointers to space in memory..  so there shouldnt be a pageing issue
0

LVL 3

Author Comment

ID: 2789237
more...  if , after the loop.. i set the next statement to the dim statement right before the beginning..  it will do the first 30000 fast like before.. then slow down..

I thought about the integer/long 32767 number too.. but the number move based on the size of the strings in the array..
ie.. if i make each element smaller.. it gets through more items in the array before slowing down..   im not sure what
this would mean paging wise.. i have array of strings..  i gcould nderstand the paging if i had fixed length array.. where each item took up space on a page..   but i thoguht variable length strings were really just pointers to space in memory..  so there shouldnt be a pageing issue
0

LVL 32

Expert Comment

ID: 2789246
I also found a little difference between running the code from the IDE amd running the compiled EXE.  Do you see the same effect when running the EXE?
0

LVL 3

Author Comment

ID: 2789255
lsExecs(x) = Space(512) is 1024 Bytes
Loop 100000 times = 102,400,000 bytes

= 97 mb..  which on my machine is not enough to make it page to disk.
0

LVL 3

Author Comment

ID: 2789268
The issue is still there in an EXE..
0

LVL 3

Author Comment

ID: 2789302
The issue is still there in an EXE..
0

LVL 32

Expert Comment

ID: 2789419
Question:  On the test machine, how much RAM is installed?

How much RAM is in use prior to starting the program?

How much Physical RAM is free?
0

LVL 9

Expert Comment

ID: 2789485
If you can, keep this open until tomorrow.  I would like to run some tests on it, but the tools are at home....
0

LVL 9

Expert Comment

ID: 2789509
What kind of computer do you have?  Pentium, Pentium II, Pentium III, etc.

[Why?  Piece of trivia -- nowadays, floating point processing is often faster than integer/long, even for simple addition and looping!  Try it.  You might be surprised!]

How much memory (you indicated at least 97 MB)?

0

LVL 9

Expert Comment

ID: 2789521
Sidebar: memory is cheap again.  I have 320MB here at work and 192MB at home (only 96MB and 64MB on my other two home computers).  Boy, memory space makes a huge difference when developing.
0

LVL 9

Expert Comment

ID: 2789541

If x Mod 1000 = 0 Then Me.Caption = x

Can you use this:

If ((x mod 1000)=0) Then
Me.Caption = x
DoEvents ' this is the important line
End If

This might help clarify any timing issues.  It might not change anything, but it will help isolate gross timing problems.
0

LVL 9

Expert Comment

ID: 2789550
Yet another question (YAQ).  Are you doing this for fun, or is it something you really need to be fast for an end-product?
0

LVL 3

Author Comment

ID: 2789660
The issue is still there in an EXE..
0

LVL 3

Author Comment

ID: 2789718
Im running nt4 workstation.

there is 256 MB on my machine..

the machines on which the end product will run are 512MB

I am running a payroll program, and the items I am spooling up into the
array are check data.

I am spooling the data to the array and at the end of the payroll.. joining the
data into a string..  then passing it
into an ADO connection for saving to the db.

<inserting them one at a time was taking way too long..>

0

LVL 9

Expert Comment

ID: 2789838
So you are pulling the data from "somewhere".  Is this another string? A database?  Can you paste (just) the exact line without disclosing proprietary information?

I know this is slightly off-topic for this thread, but it is the kind of "problem" I like helping with.
0

LVL 15

Expert Comment

ID: 2790017
>joining the data into a string
How do you do this?

Also, how do you redimension your array?

Show your code (if you have courage)
0

LVL 3

Author Comment

ID: 2790051
I am pulling data from multiple tables / databases.

I then go through the employees and calcutate the items on the checks..

as I do this.. I pop them into the array.  Each array item is actually a
call to a stored procedure

There may be 30 - 40 items per person..
<all your earnings, bonuses, etc.. 401k.. taxes>

if there are 1,000 guys in this company.. then that pumps it to 30k to 40k items.  <I will have to account for up to 10000 people.

In the meantime.. here is what I am doing.. and it appears to work ok.. I would just rather stick them in one array for simplicity..

it will add items up to say 30000 items. <the limit i hit before it acts all funky>
when it hits 30000, then it 'Joins' puts them into a group array.
<I know this moves items from one array to another.. but the Join is fast and faster then waiting for the slowness>
each of these is a 'group'.
If i have 120000 items .. then there will be 4 groups/
I then, at the end.. iterate through the 4 groups.. and send them to the database.

ie
for x = 1 to ArrayGuy.GroupCount
Conn.Execute ArrayGuy.Group(x)
next x
0

LVL 3

Author Comment

ID: 2790125
Dim li_NumberOfItems As Long
Dim las_Preserve() As String
Dim ls_EmpKey As String
Dim ls_BigFatSQL As String      ' all the sql in one
'init
ReDim las_Preserve(Text1) As String
li_NumberOfItems = Text1

'main loop
For x = 1 To li_NumberOfItems
ls_EmpKey = "'TestCo" & Format(x, "000000000") & "'"
'add some items to the array.. one for every employee..random earning
las_Preserve(x) = "EXEC sp_AddEarnings 'TestCo'," & ls_EmpKey & ",'001'," & Rnd(1) * 1000
If x Mod 1000 = 0 Then
Me.Caption = x
DoEvents       'some guy said to add this
End If
Next x

ls_BigFatSQL = Join(las_Preserve, " ")
MyConn.Execute ls_BigFatSQL             ' an ado connection

I will note that the sp is just a simple insert.. you can use any table insert for your testing.. it doesnt really matter anyway about that..
the issue is that the looping slows down after about 30000 items

also.. I added 200000 items.. and i did get some paging to disk.. just not at 100000.
0

Expert Comment

ID: 2790200
Best to use a fixed-array.
I did a test to your first question.
'For x = 1 To 100000
'       las_Values(x) = "about 100 characters"
'       If x Mod 1000 = 0 Then Me.Caption = x
' Next x
--
I used the following variables
dim las_Values(200000) as string 'Or Variant
dim x as long 'or double

I change the x = 1 to 200000

I notice that it took me 4 to 5 seconds for the loop to complete.
--
I then change the variables
dim las_Values(2000000) as string * 20 'the actual size on the string in your example in the Loop assignment.

With all other code the same, it took me 1.5 seconds for the loop to complete.

i am using a 600mhz, 256mb ram.  Hardware could be an issue, but how the array is set up is important. Fixed arrays is easier on the system.

For the last comment from you try this.

ReDim las_Preserve(Text1) As String * 55 'or the max num of char that will be stored for the element of the array.

I hope this helps.
0

LVL 3

Author Comment

ID: 2790236
I too thought the fixed array would be good.. unfortunately. you can not join an array of fixed length items.  It only works for the variable length strings
0

LVL 15

Accepted Solution

ameba earned 150 total points
ID: 2790533
>ls_BigFatSQL = Join(las_Preserve, " ")

So, you really don't need array las_Preserve(). Your goal is just to create one big string.

To create one big string, do not allocate array of strings. Use code2 from this sample:

Option Explicit
Dim Values() As String
Dim BigFatSQL As String
Private Declare Function GetTickCount Lib "kernel32" () As Long

Private Sub Form_Click()
Dim startTime As Long
MousePointer = vbHourglass
startTime = GetTickCount() ' start timer

code1

' report time
Caption = Format(Len(BigFatSQL), "0,000") & " chars, time: " & Format\$((GetTickCount - startTime) / 1000, "###0.0##")
MousePointer = vbNormal
' cleanup
Erase Values
BigFatSQL = ""
End Sub

Private Sub code1()
Dim i As Long
ReDim Values(1 To 120000)
For i = 1 To 120000
Values(i) = "this string is about 100 characters"
Next
BigFatSQL = Join(Values, " ")
End Sub

Private Sub code2()
Dim totallen As Long, tmplen As Long, nextpos As Long
Dim i As Long
' prepare big string
' first, calculate its length
For i = 1 To 120000
totallen = totallen + Len("this string is about 100 characters")
totallen = totallen + 1 ' delimiter (" ")
Next
totallen = totallen - 1 ' we don't really need last delimiter

' allocate string
BigFatSQL = Space(totallen)

' now put everything in place inside big string by using *Mid* statement
nextpos = 1
For i = 1 To 120000
tmplen = Len("this string is about 100 characters")
Mid(BigFatSQL, nextpos, tmplen) = "this string is about 100 characters"
nextpos = nextpos + tmplen
nextpos = nextpos + 1
Next
End Sub
0

LVL 9

Expert Comment

ID: 2790996
Well, I didn't get to spend much time on this, but I'll mention what little I have.

I tested under WinNT 4 and Win98.  Quite often, I did not get any hesitation.  However, towards the end, I did get some hesitation -- when my cable modem was active (occasionally get pings from the outside).  Are you getting cable / network traffic?

The code2() that ameba mentioned is also one of the faster approaches I use.  If it is really critical, it goes into C code in a DLL.  I modified your stuff some and improved ameba's results (same idea):

Dim li_NumberOfItems As Long
Dim ls_BigFatSQL As String
Dim S As String
Dim LenS As Long
Dim NextPos As Long
Dim x As Long

li_NumberOfItems = Text1
ls_BigFatSQL = Space(75 * li_NumberOfItems)
NextPos = 1
For x = 1 To li_NumberOfItems
S = "EXEC sp_AddEarnings 'TestCo','TestCo" & Format(x, "000000000") & "','001'," & Rnd(1) * 1000
LenS = Len(S)
Mid(ls_BigFatSQL, NextPos, LenS) = S
NextPos = NextPos + LenS + 1
If (x Mod 1000 = 0) Then
Me.Caption = x
DoEvents
End If
Next x

If you could shorten the Format() function from 9 zeroes to 7 zeroes, that helps as well.

You mentioned the number 97MB.  Keep in mind that VB uses Unicode internally, so the size is doubled (196MB) although it appears to be "normal" (half that).  There are funky conversions in Win98 and WinNT, but you can refer to Appleman's "Visual Basic Programmer's Guide to the Win32 API" for more information.
0

LVL 15

Expert Comment

ID: 2791469
I don't know if 'first pass' can be avoided as GivenRandy said.

For more speed, see also: http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=10189972  (contains code for my 'Replace' function)
0

LVL 3

Author Comment

ID: 2793746
It is possible that there would be an occasional hiccup due to network activity.. Im not filling the data from a DB in my tests.. I get the slowdown just using item(x) = space(100) for example..

It could be the bossed spy program to see if I am playing quake or checking stocks.. heh.. but I doubt the spy program would always take pictures at the same time in my loops.

8)
0

LVL 9

Expert Comment

ID: 2795258
Can you put your call in a loop so that it does it several times without stopping.  Does it still slow down at the same spot?
0

LVL 3

Author Comment

ID: 2864105
0

LVL 15

Expert Comment

ID: 2864197
If you didn't know, I HATE when I get 'B'. Now you know.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no oâ€¦
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone â€¦
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that aâ€¦
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This lâ€¦
###### Suggested Courses
Course of the Month9 days, left to enroll