Is there a faster way to loop through large amounts of Rows in a DataGridView?-VBForums
Results 1 to 24 of 24

Thread: Is there a faster way to loop through large amounts of Rows in a DataGridView?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Is there a faster way to loop through large amounts of Rows in a DataGridView?

    I am looping though about 100,000 records and checking a DataGridViewCheckBox Column, but it is taking way too long. Is there a faster way of doing this? Here is how I am currently doing it:

    Code:
    For Each R0w As DataGridViewRow In DataGridView1.Rows
                Dim TST As String = DataGridView1.Item(3, R0w.Index).Value.ToString
                If TST = "loc_132" Then
                    DataGridView1.CurrentRow.DefaultCellStyle.ForeColor = Color.Red
                    DataGridView1(0, R0w.Index).Value = True
                End If
            Next

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,195

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    Here are a few optimization tips:
    1. For/Next is faster than For/Each
    2. Declare your TST outside of the loop so you only create it once
    3. Declare the value that you're searching outside the loop so you only create it once

    Code:
    Dim TST AS String
    Dim loc_123 As String = "loc_123"
    For counter As Integer = 0 To DataGridView1.Rows.Count - 1
        TST = DataGridView1.Item(3, counter).Value.ToString()
        If TST = loc_123 Then
            DataGridView1.CurrentRow.DefaultCellStyle.ForeColor = Color.Red            
            DataGridView1(0, counter).Value = True
        End If
    Next
    However, if you're looping through that many rows you'll likely need to do this in a separate thread.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    What do you mean by "However, if you're looping through that many rows you'll likely need to do this in a separate thread. "

  4. #4
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,195

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    Multithreading is a concept where you do a task on a separate thread other than the user interface thread. The reason why you would do this is because if you have a long running task then it will take up too much resources to where your UI will appear to freeze.

    Here is an MSDN article on how to multithread within Visual Basic .NET: https://msdn.microsoft.com/en-us/lib...(v=vs.71).aspx

    Here is a sample(free-typed) code for your scenario:
    Code:
    Private thread_UpdateDGVCheckBox As Threading.Thread
    Private Sub ThreadedDGVCheckBoxUpdate()
        Dim TST AS String
        Dim loc_123 As String = "loc_123"
        For counter As Integer = 0 To DataGridView1.Rows.Count - 1
            TST = DataGridView1.Item(3, counter).Value.ToString()
            If TST = loc_123 Then
                Me.Invoke(Sub()
                              DataGridView1.CurrentRow.DefaultCellStyle.ForeColor = Color.Red
                              DataGridView1(0, counter).Value = True
                          End Sub
            End If
        Next
    End Sub
    Then when to use the code, you'd call:
    Code:
    thread_UpdateDGVCheckBox = New Threading.Thread(AddressOf ThreadedDGVCheckBoxUpdate)
    thread_UpdateDGVCheckBox.Start()

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    Thank you for the helpful information!!! I will let you know how it goes once I give it a shot, because yeah after I made the changes of your initial suggestions it was still pretty slow. So, I will trying the multithreading!

  6. #6
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,195

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    No problem. Keep me updated on how you are doing.

    Before I tip my hat for a while I wanted to make a point about this chunk of code:
    Code:
    Me.Invoke(Sub()
        DataGridView1.CurrentRow.DefaultCellStyle.ForeColor = Color.Red
        DataGridView1(0, counter).Value = True
    End Sub
    Using a Sub without a name like this is call an anonymous delegate. This will only work if you are using Visual Studios 2010 or higher; if you're using an older version like 2008 then you will need to use an old school delegate.

    Here is a MSDN article on older style delegates just in case you need it: https://msdn.microsoft.com/en-us/lib...(v=vs.90).aspx

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    Whelp Im stepping through the code and its just immediately skipping this part of the code:

    Code:
      TST = DataGridView1.Item(3, DataGridView1.Rows(counter).Index).Value.ToString()
                If TST = "loc_132" Then
                    Me.Invoke(Sub()
                                 DataGridView1.CurrentRow.DefaultCellStyle.ForeColor = Color.Red
                                  DataGridView1(0, DataGridView1.Rows(counter).Index).Value = True
                              End Sub)
                End If

  8. #8
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,195

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    When you setup the breakpoint on your For/Next loop, what is the value of DataGridView1.Rows.Count - 1?

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    "0" from what I can tell...

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    just did some more digging it might be "-1"

  11. #11
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,195

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    Then that is why it is skipping the code, if there are no rows to loop through then there is no need to execute the code.

    Edit - We cross-posted. If you pull up the watch window and type in:
    Code:
    DataGridView1.Rows.Count
    It will tell you the amount.

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    Just to clarify I am accessing a datagridview on another form, not sure if this would have an affect on doing this? So, I've altered your code to look like this: (this MsgBox returns the value "-1" btw)

    Code:
    Dim StringCnt As Integer
            StringCnt = MyFormName.DataGridView1.RowCount - 1
            MsgBox(StringCnt)

  13. #13
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,195

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    The only way that it would affect the code is if the Form was never launched and so the data was never populated in the DataGridView.

  14. #14

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    That's weird its not seeing any rows, because the DataGridView is definitely populated...

  15. #15

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    Getting this from the watch window "Reference to a non-shared member requires an object reference" when I enter in:

    Code:
    DataGridView1.Rows.Count
    Last edited by Christhemist; Nov 10th, 2016 at 05:30 PM.

  16. #16
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,415

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    If it's not even going into the For Loop then that not what making your app run slow. Where is this code located? What other code is relevant to what your doing.

  17. #17

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    @wes4dbt Its not getting a proper row count because of that error...

    Code:
     Private thread_UpdateDGVCheckBox As Threading.Thread
        Private Sub ThreadedDGVCheckBoxUpdate()
    
            Dim TST As String
            Dim StringCnt As Integer
            StringCnt = DataGridView1.RowCount - 1
            MsgBox(StringCnt)
    
            For counter As Int32 = 0 To DataGridView1.RowCount - 1
                TST = DataGridView1.Item(3, DataGridView1.Rows(counter).Index).Value.ToString()
                If TST = "loc_132" Then
                    Invoke(Sub()
                                            DataGridView1.CurrentRow.DefaultCellStyle.ForeColor = Color.Red
                                           DataGridView1(0, DataGridView1.Rows(counter).Index).Value = True
                                        End Sub)
                End If
            Next
        End Sub

  18. #18
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,415

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    Ok, where uis this "Private Sub ThreadedDGVCheckBoxUpdate()" called from?

    And WHAT error? You haven't mentioned getting an error when you run your code.

  19. #19

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    It's being called from a button on a separate form.

    Not an error but the output in the watch window reads "Reference to a non-shared member requires an object reference" when I input this code:
    Code:
    DataGridView1.Rows.Count

  20. #20
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,415

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    There's no way your call "Private Sub ThreadedDGVCheckBoxUpdate()" from a button on a seperate form. You have declared the Sub as Private, it would not be visible from another form. I think there is some information you have not provided that making solving this issue a lot harder than is should be. Always provide a complete explanation and all relevant code.

  21. #21

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    My bad, it is being called from a button on the same form using this:

    Code:
    thread_UpdateDGVCheckBox = New Threading.Thread(AddressOf ThreadedDGVCheckBoxUpdate)
            thread_UpdateDGVCheckBox.Start()
    Note: It's the DataGridView that is on another form, I have just excluded this from the code. So, its really:

    Code:
    MyFormName.DataGridView1.Rows.Count
    Last edited by Christhemist; Nov 10th, 2016 at 06:04 PM.

  22. #22
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,415

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    thread_UpdateDGVCheckBox = New Threading.Thread(AddressOf ThreadedDGVCheckBoxUpdate)
    thread_UpdateDGVCheckBox.Start()
    Just providing this little snippet isn't enough, we have no idea where this is located.

    The fact that the dgv is located on a different form is extremely important. Like I said before, please provide a complete explanation. Maybe someone else can help you. I'm done for now.

  23. #23

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    This snippet is on the same form in a Button_Click event

  24. #24

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Re: Is there a faster way to loop through large amounts of Rows in a DataGridView?

    This issue is still unresolved if anyone else has anymore insight...

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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.