Question DataGridView: Slow and uses >1GB memory! Can it be optimized?

[JesperSP]

The following snip of simple code runs pretty slow on my computer, and I suspect that it is due to excessive memory usage, since the program reports using >1GB mem according to TaskManager. Am I using the DataGridView in a wrong way?

Instructions: Make a windowsforms application and put 2x button and 1xDataGridView on it. Push button1 and then button2 - sort the data in the DataGridView by a different col and push button2 again. It'll go above 1GB mem usage! Basically I need to display a lot of data to a user and allow the user to sort the data and generate something from them. Can this be done in a more optimal way?

Cheers,
Jesper

Public Class Form1
    Public dt As DataTable


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        dt = New DataTable
        For i As Integer = 1 To 18
            dt.Columns.Add(New DataColumn("col" & i, System.Type.GetType("System.Double")))
        Next
        For i As Integer = 0 To 300000
            Dim dr As DataRow = dt.NewRow()
            For t As Integer = 0 To 17
                dr.Item(t) = Rnd() * 100


            Next
            dt.Rows.Add(dr)
        Next
        DataGridView1.DataSource = dt
    End Sub


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
         For Each dr As DataGridViewRow In DataGridView1.Rows
            If dr.Cells("col1").Value > 50 Then
                'do something
            End If
        Next
    End Sub
End Class

 

[JohnH]

Don't you think 300.001 rows of data on 18 columns is a bit on the excessive side to present to a user in one go?

 

[JesperSP]

I don't decide the amount of data

You have a valid point there. Unfortunately I do not decide the amount of data to be presented to the user of the software. It may be everything from 1000 to 300000 entries, and the user need to be able to have the data displayed and sorted according to 18 different values. The user will probably only need to look at the about 100 first entries, but the program needs to search for the 100 first entries in the sorted grid that meet a certain criteria - and these could be some of the last ones!! A workaround I thought about was to sort the dataTable by the same column as the DataGridView, and then get my data from the DataTable, which is probably a lot faster, but I don't know how to trap sorting events from the DataGridView and have them reflected in the DataTable. I assume that DataGridView is this slow and memory hungry because of its flexibility. I previously used some very large StringGrids in delphi that could handle this amount of data, but I have migrated to vb.net and I'm trying to do the same here..

Cheers,
Jesper

 

[JohnH]

Unfortunately I do not decide the amount of data to be presented to the user of the software.

As the application developer I would say you do, even if there is requirements they must be reasonable according to the development and performance of the application. You should research filtering and paging options. If data is downloaded from a database there is also the decision to make whether to do this at database or application level. Sorting is also something that may be possible to perform before presenting data, and then allow you to only present say a few hundreds of the first matched results.

 

[JesperSP]

Solution found

As the application developer I would say you do, even if there is requirements they must be reasonable according to the development and performance of the application. You should research filtering and paging options. If data is downloaded from a database there is also the decision to make whether to do this at database or application level. Sorting is also something that may be possible to perform before presenting data, and then allow you to only present say a few hundreds of the first matched results.

Thanks for your advise. The data presented comes from text files that are loaded on demand - each entry also corresponds to an image. I found a solution that works - turns out it was the DataTable that was too memory hungry... But sorting is still slow

Imports System.ComponentModel
Imports System.Reflection


Public Class Form1
    Public mylist As New SortableBindingList(Of MyDouble)
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click


        For i As Integer = 0 To 300000
            mylist.Add(New MyDouble(Rnd() * 100, Rnd() * 100))
        Next


        DataGridView1.DataSource = mylist
    End Sub




    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        For Each entry As MyDouble In mylist
            If entry.col1 > 50 Then
                'do something


            End If
        Next
    End Sub
End Class


Public Class MyDouble
    Public Sub New(ByRef col1 As Double, ByVal col2 As Double)
        _col1 = col1
        _col2 = col2
    End Sub
    Private _col1 As Double
    Public Property col1 As Double
        Get
            Return _col1
        End Get
        Set(ByVal value As Double)
            _col1 = value
        End Set
    End Property
    Private _col2 As Double
    Public Property col2 As Double
        Get
            Return _col2
        End Get
        Set(ByVal value As Double)
            _col2 = value
        End Set
    End Property
End Class


Public Class SortableBindingList(Of T)
    Inherits BindingList(Of T)
    Private _isSorted As Boolean
    Private _sortDirection As ListSortDirection
    Private _sortProperty As PropertyDescriptor


    'This override shows the binded object, that our list supports sorting
    Protected Overrides ReadOnly Property SupportsSortingCore() As Boolean
        Get
            Return True
        End Get
    End Property


    'And that it can sort bi-directional
    Protected Overrides ReadOnly Property SortDirectionCore() As ListSortDirection
        Get
            Return _sortDirection
        End Get
    End Property


    'And that it can sort by T typed object's properties
    Protected Overloads Overrides ReadOnly Property SortPropertyCore() As PropertyDescriptor


        Get
            Return _sortProperty
        End Get
    End Property


    'This is the method, what gets called when the sort event occurs in the bound object
    Protected Overloads Overrides Sub ApplySortCore(ByVal prop As PropertyDescriptor, ByVal direction As ListSortDirection)
        Dim items As List(Of T) = TryCast(Me.Items, List(Of T))


        If items IsNot Nothing Then
            Dim pc As New PropertyComparer(Of T)(prop.Name, direction)
            items.Sort(pc)
            _isSorted = True
            _sortDirection = direction
            _sortProperty = prop
        Else
            _isSorted = False
        End If
        OnListChanged(New ListChangedEventArgs(ListChangedType.Reset, -1))
    End Sub


    'This shows if our list is already sorted or not
    Protected Overloads Overrides ReadOnly Property IsSortedCore() As Boolean
        Get
            Return _isSorted
        End Get
    End Property


    'Removing the sort
    Protected Overrides Sub RemoveSortCore()
        _isSorted = False
    End Sub


    ' Sub New(ByVal list As ICollection(Of T))
    '    MyBase.New(list)
    'End Sub
End Class


'The list uses a simple property comparer; the default comparer to compare two properties. It is good for most cases; of course you have to have the correct property type declaration.


Public Class PropertyComparer(Of T)
    Implements IComparer(Of T)
    Private _property As PropertyInfo
    Private _sortDirection As ListSortDirection


    Public Sub New(ByVal sortProperty As String, ByVal sortDirection As ListSortDirection)
        _property = GetType(T).GetProperty(sortProperty)
        Me._sortDirection = sortDirection
    End Sub


    Public Function Compare(ByVal x As T, ByVal y As T) As Integer Implements IComparer(Of T).Compare
        Dim valueX As Object = _property.GetValue(x, Nothing)
        Dim valueY As Object = _property.GetValue(y, Nothing)


        If _sortDirection = ListSortDirection.Ascending Then Return Comparer.[Default].Compare(valueX, valueY)


        Return Comparer.[Default].Compare(valueY, valueX)
    End Function
End Class

 

[JohnH]

Reflection will have to take the blame for that, it is rather slow, and you will notice this especially when it is used for many operations. Sorting that list of 300000 items using that sorting class for example can cause up to 180 billion reflection operations. If you hardcode each property comparison you will see the sorting finish within a second (at least on my old comp). Same ridiculous amount of comparisons and element swapping operations must be done of course, but you save all those reflection lookups. An example, in ApplySortCore cast the Items as List(Of MyDouble), then sort for example ascending by property "col1" (exposed by prop.Name) :

items.Sort(Function(x, y) x.col1.CompareTo(y.col1))