Results 1 to 7 of 7

Thread: [RESOLVED] Optimise a For loop VB.net

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    Resolved [RESOLVED] Optimise a For loop VB.net

    Hello guys, im currently working with 2 huuuuug excel files the one in ws3 contains more than 13000 lignes and ws2 contains more than 2000 lignes.

    my code is trying to compare the column E with the column J and if it finds two members that are equals,then it verifies if the one in ws3 same ligne column "D" is an output ,if yes then it verifies for the same ligne if the column B of ws3 and M(13) of ws2 are equal if not add them into a datagridview.

    can you help me how to make this loop work faster because now it takes 5 minutes to run tank you so much in advanced
    Code:
    Dim findme3 As Microsoft.Office.Interop.Excel.Range = ws3.Range("E2:E" & range3.Rows.Count)
    Dim findme4 As Microsoft.Office.Interop.Excel.Range = ws2.Range("J5:J" & Range2.Rows.Count)
    Dim MyArra1 As Object(,) = CType(findme3.Value, Object(,))
    Dim MyArra2 As Object(,) = CType(findme4.Value, Object(,))
                    For f = 1 To MyArra2.Length
                        For s = 1 To MyArra1.Length
                            If UCase(MyArra1(s, 1)) = UCase(MyArra2(f, 1)) Then
                                ran1 = ws2.Cells(f + 4, 13)
                                ran2 = ws3.Cells(s + 1, 2)
                                ran3 = ws3.Cells(s + 1, 4)
                                If ran3.Text = "Output" Then
                                    If ran2.Text <> ran1.Text Then
                                        Form2.DataGridView3.Rows.Add(MyArra1(s, 1), ran1.Text, ran2.Text)
                                    End If
                                End If
    
                            End If
                        Next
                    Next
    Last edited by highfly884; Sep 16th, 2021 at 05:52 AM.

  2. #2
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    6,515

    Re: Optimise a For loop VB.net

    Off the top of my head, adding to the DGV inside the inner loop would be a huge performance hog if it is doing that very often.

    Some of that code looks like it could be improved by using hash tables like Dictionary(Of T) but I cannot be certain. I would need to know the performance characteristics of those Excel.Range calls as well as the conversions to Object(,) and the Cells calls inside the inner loop. I haven't worked that much with Excel interop and what work I did with it didn't involve reading, only writing. So I don't really understand the performance implications of their usage.

    I'm thinking that one of those arrays could be dumped into a Dictionary(Of T) with the key being derived from the same logic as you did with UCase(MyArra(f, 1)) If this could be achieved, you should be able to gain a massive performance boost since hash table look ups take something close to O(1) time. That is my instinct but like I said, I don't know enough about Excel interop's performance characteristics to be sure.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  3. #3
    Fanatic Member
    Join Date
    Jan 2006
    Posts
    694

    Re: Optimise a For loop VB.net

    The following statement can be a performance drag if you have numerous iterations:
    Code:
    If UCase(MyArra1(s, 1)) = UCase(MyArra2(f, 1)) Then
    It's much faster using:
    Code:
    If string.Equals(MyArra1(s, 1), MyArra2(f, 1), StringComparison.OrdinalIgnoreCase) Then
    David Anton
    Convert between VB, C#, C++, & Java
    www.tangiblesoftwaresolutions.com

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,648

    Re: Optimise a For loop VB.net

    You should also not be adding the rows to the grid one at a time. Compile all the data first, then add it all to the grid at the end. You might populate a DataTable or a List(Of T) and then bind it to the grid.

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,733

    Re: Optimise a For loop VB.net

    The ideas from Niya and jmcilhinney are definitely worth considering, but you can also get big improvements from your current style by optimising the Ucase calls (or equivalent).

    You are running the If statement 13000 * 2000 times, which is 26000000, and you are doing a Ucase (or equivalent) twice on that line... so that is 52 million Ucase operations that by pre-calculating the values can be reduced to just 13000+2000, which is just 15 thousand

    To do that you can store the values to new arrays, or in this case for one of them simply use a single String variable:
    Code:
    Dim MyArra2 As Object(,) = CType(findme4.Value, Object(,))
    Dim MyArra1Upper as String(MyArra1.Length)
    Dim MyArra2Upper as String
                    For s = 1 To MyArra1.Length
                        MyArra1Upper(s) = UCase(MyArra1(s, 1))
                    Next
                    For f = 1 To MyArra2.Length
                        MyArra2Upper = UCase(MyArra2(f, 1))
                        For s = 1 To MyArra1.Length
                            If MyArra1Upper(s) = MyArra2Upper Then
                                ran1 = ws2.Cells(f + 4, 13)
    You can also improve the speed a little by moving the lines that set the values for ran1 and ran2 to after the next If statement, because their values are irrelevant until you are inside that block:
    Code:
                                ran3 = ws3.Cells(s + 1, 4)
                                If ran3.Text = "Output" Then
                                    ran1 = ws2.Cells(f + 4, 13)
                                    ran2 = ws3.Cells(s + 1, 2)
                                    If ran2.Text <> ran1.Text Then

  6. #6

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    Re: Optimise a For loop VB.net

    Thank you so much guuuuuys i tried all your suggestions and it turns from more than 5 min to 1 min and some seconds thank you so much it's much better now , you're the best

  7. #7
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,597

    Re: Optimise a For loop VB.net

    At the top of the forum on the right is a drop down called "Thread Tools". It has an option to mark this thread resolved. That helps other members know it is not active and has been resolved.
    Please remember next time...elections matter!

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width