Highlight DataGridViewRows based on value comparison with other rows

Question

I have a Part class with the fields list in the code below. I have a DataGridView control, which I am filtering with the Advanced DGV (ADGV) DLL from NUGET. I must include the ADGV in my winform. I currently have a DataGridView, a search box on the form, and a button to run the following function. I need to go through all of the visible rows, collect a unique list of part numbers with their most recent revisions, and then color the rows in DataGridView which are out of date by checking the part number and rev on each row against the mostuptodate list. For 45,000 entries displayed in DataGridView, this take ~17 secs. For ~50 entries, it take ~1.2 seconds. This is extremely inefficient, but I can't see a way to cut the time down.

Sub highlightOutdatedParts()
    'Purpose: use the results in the datagridview control, find the most recent revision of each part, and 
    '           highlight all outdated parts relative to their respective most recent revisions
    'SORT BY PART NUMBER AND THEN BY REV
    If resultsGrid.ColumnCount = 0 Or resultsGrid.RowCount = 0 Then Exit Sub
    Dim stopwatch As New Stopwatch
    stopwatch.Start()
    resultsGrid.Sort(resultsGrid.Columns("PartNumber"), ListSortDirection.Ascending)
    Dim iBag As New ConcurrentBag(Of Part)
    Dim sortedList As Generic.List(Of Part)
    For Each row As DataGridViewRow In resultsGrid.Rows
        If row.Visible = True Then
            Dim iPart As New Part()
            Try
                iPart.Row = row.Cells(0).Value
                iPart.Workbook = CStr(row.Cells(1).Value)
                iPart.Worksheet = CStr(row.Cells(2).Value)
                iPart.Product = CStr(row.Cells(3).Value)
                iPart.PartNumber = CStr(row.Cells(4).Value)
                iPart.ItemNo = CStr(row.Cells(5).Value)
                iPart.Rev = CStr(row.Cells(6).Value)
                iPart.Description = CStr(row.Cells(7).Value)
                iPart.Units = CStr(row.Cells(8).Value)
                iPart.Type = CStr(row.Cells(9).Value)
                iPart.PurchCtgy = CStr(row.Cells(10).Value)
                iPart.Qty = CDbl(row.Cells(11).Value)
                iPart.TtlPerProd = CDbl(row.Cells(12).Value)
                iPart.Hierarchy = CStr(row.Cells(13).Value)
                iBag.Add(iPart)
            Catch ice As InvalidCastException
            Catch nre As NullReferenceException
            End Try
        End If
    Next
    sortedList = (From c In iBag Order By c.PartNumber, c.Rev).ToList()  ' sort and convert to list
    Dim mostUTDRevList As New Generic.List(Of Part)     ' list of most up to date parts, by Rev letter
    For sl As Integer = sortedList.Count - 1 To 0 Step -1   'start at end of list and work to beginning
        Dim query = From entry In mostUTDRevList    ' check if part number already exists in most up to date list
                    Where entry.PartNumber = sortedList(sl).PartNumber
                    Select entry
        If query.Count = 0 Then     ' if this part does not already exist in the list, add.
            mostUTDRevList.Add(sortedList(sl))
        End If
    Next
    'HIGHLIGHT DATAGRIDVIEW ROWS WHERE PART NUMBERS ARE OUT OF DATE
    For Each row As DataGridViewRow In resultsGrid.Rows
        ' if that part with that Rev does not exist in the list, it must be out of date
        Try
            Dim rowPN As String = CStr(row.Cells(4).Value).ToUpper  ' get part number
            Dim rowR As String = CStr(row.Cells(6).Value).ToUpper   ' get Rev
            Dim query = From entry In mostUTDRevList    ' check if that part number with that Rev is in the list.
                        Where entry.PartNumber.ToUpper.Equals(rowPN) AndAlso
                        entry.Rev.ToUpper.Equals(rowR)
                        Select entry
            If query.Count = 0 Then     ' if the part is out of date highlight its' row
                row.DefaultCellStyle.BackColor = Color.Chocolate
            End If
        Catch ex As NullReferenceException
        Catch ice As InvalidCastException
        End Try
    Next
    resultsGrid.Select()
    stopwatch.Stop()
    If Not BackgroundWorker1.IsBusy() Then timertextbox.Text = stopwatch.Elapsed.TotalSeconds.ToString & " secs"
    MessageBox.Show("Highlighting completed successfully.")
End Sub

Answer 1

It is almost always faster to work with the data than the control. The control is simply the means to present a view of the data (in a grid) to the users. Working with the data from there requires too much converting to be effieicent. Then, use the DGV events to highlight the rows

Its hard to tell all the details of what you are doing, but it looks like you are comparing the data to itself (as opposed to some concrete table where the lastest revision codes are defined). Nor is it clear why the datasources are collections, ConcurrentBags etc. The key would be to use collections optimized for the job.

To demonstrate, I have a table with 75,000 rows; the product codes are randomly selected from a pool of 25,000 and a revision code is a random integer (1-9). After the DGV datasource is built (a DataTable) a LookUp is created from the ProductCode-Revision pair. This is done once and once only:

' form level declaration
Private PRCodes As ILookup(Of String, Int32)

' go thru table
' group by the product code
' create an anon Name-Value object for each, 
'     storing the code and highest rev number
' convert result to a LookUp
PRCodes = dtSample.AsEnumerable.
    GroupBy(Function(g) g.Item("ProductCode"),
            Function(key, values) New With {.Name = key.ToString(), .Value = values.
                                    Max(Of Int32)(Function(j) j.Field(Of Int32)("RevCode"))
                                                      }).
    ToLookup(Of String, Int32)(Function(k) k.Name, Function(v) v.Value)

Elapsed time via stopwatch: 81 milliseconds to create the collection of 23731 items. The code uses an anonymous type to store a Max Revision code for each product code. A concrete class could also be used. If you're worried about mixed casing, use .ToLowerInvariant() when creating the LookUp (not ToUpper -- see What's Wrong With Turkey?) and again later when looking up the max rev.

Then rather than looping thru the DGV rows use the RowPrePaint event:

If e.RowIndex = -1 Then Return
If dgv1.Rows(e.RowIndex).IsNewRow Then Return

' .ToLowerInvariant() if the casing can vary row to row
Dim pc = dgv1.Rows(e.RowIndex).Cells("ProductCode").Value.ToString()
Dim rv = Convert.ToInt32(dgv1.Rows(e.RowIndex).Cells("RevCode").Value)

Dim item = PRCodes(pc)(0)
If item > rv Then
    dgv1.Rows(e.RowIndex).DefaultCellStyle.BackColor = Color.MistyRose
End If

Notes

• It takes some time to create the DataSource, but 75,000 rows is a lot to throw at a user
• The time to create the LookUp is minimal - barely measurable
• There is no noticeable wait in displaying them because a) the LookUp is made for this sort of thing, b) rows are done as needed when they are displayed. Row # 19,999 may never be processed if the user never scrolls that far.
• This is all geared to just color a row. If you needed to save the Current/NotCurrent state for each row, add a Boolean column to the DataTable and loop on that. The column can be invisible if to hide it from the user.
  • The random data results in 47,000 out of date RevCodes. Processing 75k rows in the DataTable to set the flag takes 591 milliseconds. You would want to do this before you set the DataTable as the DataSource to prevent changes to the data resulting in various events in the control.

In general, the time to harvest the max RevCode flag and even tag the out of date rows is a trivial increment to creating the datasource.

The Result:

The data view is sorted by ProductCode so that the coloring of lower RevCodes is apparent.

We surely cant grok all the details and constraints of the system from a small snippet - even the data types and original datasource are a guess for us. However, this should provide some help with better look-up methods, and the concept of working with the data rather than the user's view.

One thing is the revision code - yours is treating them as a string. If this is alphanumeric, it may well not compare correctly - "9" sorts/compares higher than "834" or "1JW".

See also:
Lookup(Of TKey, TElement) Class
Anonymous Types

Answer 2

The solution was spurred in part by @Plutonix.

Sub highlightOutdatedParts()
    If resultsGrid.ColumnCount = 0 Or resultsGrid.RowCount = 0 Then Exit Sub
    Dim stopwatch As New Stopwatch
    stopwatch.Start()
    resultsGrid.DataSource.DefaultView.Sort = "PartNumber ASC, Rev DESC"
    resultsGrid.Update()
    'HIGHLIGHT DATAGRIDVIEW ROWS WHERE PART NUMBERS ARE OUT OF DATE
    Dim irow As Long = 0
    Do While irow <= resultsGrid.RowCount - 2
        ' if that part with that Rev does not exist in the list, it must be out of date
        Dim utdPN As String = resultsGrid.Rows(irow).Cells(4).Value.ToString().ToUpper()
        Dim utdRev As String = resultsGrid.Rows(irow).Cells(6).Value.ToString().ToUpper()
        Dim iirow As Long = irow + 1
        'If iirow > resultsGrid.RowCount - 1 Then Exit Do
        Dim activePN As String = Nothing
        Dim activeRev As String = Nothing
        Try
            activePN = resultsGrid.Rows(iirow).Cells(4).Value.ToString().ToUpper()
            activeRev = resultsGrid.Rows(iirow).Cells(6).Value.ToString().ToUpper()
        Catch ex As NullReferenceException
        End Try
        Do While activePN = utdPN
            If iirow > resultsGrid.RowCount - 1 Then Exit Do
            If activeRev <> utdRev Then
                resultsGrid.Rows(iirow).DefaultCellStyle.BackColor = Color.Chocolate
            End If
            iirow += 1
            Try
                activePN = resultsGrid.Rows(iirow).Cells(4).Value.ToString().ToUpper()
                activeRev = resultsGrid.Rows(iirow).Cells(6).Value.ToString().ToUpper()
            Catch nre As NullReferenceException
            Catch aoore As ArgumentOutOfRangeException
            End Try
        Loop
        irow = iirow
    Loop
    resultsGrid.Select()
    stopwatch.Stop()
    If Not BackgroundWorker1.IsBusy() Then
        timertextbox.Text = stopwatch.Elapsed.TotalSeconds.ToString & " secs"
        resultcounttextbox.Text = resultsGrid.RowCount - 1 & " results"
    End If
    MessageBox.Show("Highlighting completed successfully.")
End Sub