dcsimg
Results 1 to 12 of 12
  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2016
    Posts
    51

    For Each loops slow

    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

  2. #2
    Fanatic Member PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Newport, UK
    Posts
    773

    Re: For Each loops slow

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

  3. #3
    Junior Member
    Join Date
    Aug 2015
    Posts
    30

    Re: For Each loops slow

    let us know about your coding.

  4. #4

    Thread Starter
    Member
    Join Date
    Apr 2016
    Posts
    51

    Re: For Each loops slow

    Code:
    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
    Last edited by Shaggy Hiker; Jan 26th, 2018 at 03:32 PM. Reason: Added CODE tags.

  5. #5
    Junior Member
    Join Date
    Aug 2015
    Posts
    30

    Re: For Each loops slow

    nested for each can take more time

  6. #6
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,578

    Re: For Each loops slow

    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".
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  7. #7

    Thread Starter
    Member
    Join Date
    Apr 2016
    Posts
    51

    Re: For Each loops slow

    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.

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,811

    Re: For Each loops slow

    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.

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,031

    Re: For Each loops slow

    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:

    Code:
    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.
    My usual boring signature: Nothing

  10. #10
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,578

    Re: For Each loops slow

    Quote Originally Posted by Shaggy Hiker View Post
    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.
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  11. #11
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,031

    Re: For Each loops slow

    Quote Originally Posted by Sitten Spynne View Post
    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.
    My usual boring signature: Nothing

  12. #12

    Thread Starter
    Member
    Join Date
    Apr 2016
    Posts
    51

    Re: For Each loops slow

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width


×
By using this site, you agree to the Privacy Policy