[RESOLVED] Optimise a For loop VB.net

[highfly884]

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

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

[Niya]

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.

[David Anton]

The following statement can be a performance drag if you have numerous iterations:

If UCase(MyArra1(s, 1)) = UCase(MyArra2(f, 1)) Then

It's much faster using:

If string.Equals(MyArra1(s, 1), MyArra2(f, 1), StringComparison.OrdinalIgnoreCase) Then

[jmcilhinney]

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.

[si_the_geek]

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:

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:

                            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

[highfly884]

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

[TysonLPrice]

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.