For Each loops slow

[BSWhipp]

Can anyone tell me why for each loops are sow slow? I am looping through about 100000 rows and updating information in the datatable base on results of other information. The loop takes a few minutes to run. It should take seconds.

Any insight would be great. Thanks

[PlausiblyDamp]

Could you post the code you are using? When you are asking about performance showing the code is a big help.

[bayat2000]

let us know about your coding.

[BSWhipp]

For Each r In rec3.Rows
            lcvendyear = r.item("fvendno") & r.item("Yearc")
            'Look for how many are late
            foundrow = SuppRcv.Select("fvendno+Yearc = '" & lcvendyear & "' and isLate = '0'")
            r.item("qtyontime") = foundrow.Count

            foundrow = Nothing
            'Find the receive QTY
            foundrow = SuppRcv.Select("fvendno+Yearc = '" & lcvendyear & "'")

            For Each s In foundrow
                lnqtyrecv = lnqtyrecv + s(7)
            Next

            r.item("fqtyrecv") = lnqtyrecv

        Next

[bayat2000]

nested for each can take more time

[Sitten Spynne]

Your for loops are slow because the thing you do on every iteration takes "a long time", and you do it 100,000 times. Even if "a long time" is something reasonably small like 100ms, that's 10,000 seconds. The way to make it faster is to figure out why each iteration takes so long. Every ms you shave off of a 100,000 iteration loop saves you 100 seconds. That's an awful lot of time. I can guarantee there's at least 10ms worth of mistakes in your code, but can't comment on code I can't see.

That said, you can't make a baby in much less than 9 months. If you want it to take, say, 10 seconds to mess with 100,000 records, you have to spend less than 0.1ms per iteration. That is a VERY tight time window if your updates are complex.

Considering it looks like you query a different data set on every iteration, I think "it should take seconds" is completely false. Depending on the size of that data set, it could take hundreds of milliseconds for that Select() call. It's very hard to make suggestions without knowing more about the nature of what you're doing, but in my opinion this is "code that is slow because it does a lot".

[BSWhipp]

It just seems it take much longer in VB than it does in another program. I am converting old programs that are in Foxpro to VB. The same Do while loop in Foxpro takes seconds where the vb code may take a minute or minutes. I will say getting data from SQL is much much faster in VB.

[si_the_geek]

Assuming the data for Foxpro was a local file and the data for your program server based, some slowdown for that kind of loop is to be expected - because you would be repeatedly requesting data from another program (perhaps over a network) and waiting for the response.

There are several differing ways to improve it, arguably the best being Stored Procedures, which would allow you to run code within the database itself (and therefore only need the time to communicate at the start and end of the process, rather than multiple times per loop iteration).

However, that is likely to be hard work unless you already have experience of Stored Procedures, and a far simpler way to boost speed would be to think a little about the code you have got... at the moment you .Select two sets of data, but one is a subset of the other. Adding an extra line of code (and a variable) in the inner loop would allow you to avoid one of the .Select statements, which could almost double the speed.

[Shaggy Hiker]

I'm confused by the timeline of this thread, as the code is there....followed by a series of responses saying they can't see the code. In any case, the code needs to be properly formatted to make it easier to read, which I have done. You can do this by pressing the # button and pasting the code between the resulting [CODE][/CODE] tags, or paste in the code, then highlight it and press either the VB or # key.

There are several things in the code that are going to slow it down. For one thing, it sure looks like you have Option Strict OFF (though I may be mistaken depending on a different point). Turning that ON will speed up the code once you fix all the problems that you find. Normally, this doesn't make all that big a difference, but as Sitten pointed out, once you have hundreds of thousands of iterations, every little bit helps.

1) lcvendyear = r.item("fvendno") & r.item("Yearc"). This appears to be string concatenation. If r is a row from a strongly typed datatable, as it may well be due to the for loop structure, then that might be ok, because r.item() may be returning a string. More likely, it is returning an Object which is being implicitly cast to a string. Two implicit casts plus a concatenation isn't going to be wonderful to begin with. String work is always a bit slow.

2) SuppRcv.Select(). The Select statement is simple and easy to write....it's also slow. I forget the metrics I measured on this, but it was so much slower than just iterating through the rows in a loop that I went back through my code and stripped it out of every place I was using it in an inner loop. Iterating through a loop isn't necessarily the best, either, but whether there would be a better way to construct that SuppRcv such that it could be searched faster is impossible to know based on that code. In any case, this particular line (and the other Select line) is likely to be a significant time sink.

3) You then do a further loop that also performs an implicit conversion.

4) Ultimately, the reason the code is slow is that it has several points of inefficiency. For every iteration of the outer loop you are performing a pair of selects, a series of implicit conversions, and an inner loop. The whole body of the loop could be written with a single loop through SuppRcv. It would look roughly like this:

lcvendyear = r.item("fvendno") & r.item("Yearc")
dim cntr as Integer
dim accum as integer

For each dr in SuppRcv.Rows()
  If dr("fvendno+Yearc").ToString = '" & lcvendyear & "' Then
    accum += s(7)
    If cBool(dr("IsLate")) Then
      cntr+=1
    End If
  End If
Next
  
r.item("qtyontime") = cntr
r.item("fqtyrecv") = accum

I am making assumptions about some types, so this may be a bit off, but it gets rid of the inner loop, both select statements, and some of the implicit conversions. Options Strict ON would help you clean up the rest of the conversions, and fixing any errors due to my assumptions and freehand writing, would deal with the rest.

[Sitten Spynne]

I'm confused by the timeline of this thread, as the code is there....followed by a series of responses saying they can't see the code.

Timestamps! I started typing around 10, post #3 was there when I made my answer. It took me more than 10 minutes to get through my post, and by then the code had been posted.

[Shaggy Hiker]

Timestamps! I started typing around 10, post #3 was there when I made my answer. It took me more than 10 minutes to get through my post, and by then the code had been posted.

That can't be. That's one of the shortest replies you've ever posted. Why, it was so brief it couldn't hardly have taken you more than thirty seconds, tops.

Actually, it certainly did read like that, but the time showed 15 minutes later. Fifteen minutes after a post and I'm on to a different site, so I wasn't sure.

[BSWhipp]

OK, Thanks everyone. I will play around with it some more to see what I can do.