locked
DataGridView Speed RRS feed

  • Question

  • MS Access 2010 - Table named "2017" contains 334444 rows, 37 Fields

    Query is "SELECT [2017].[First Name], [2017].[Last Name], [2017].[Mailing Address], [2017].[City Name], [2017].[State Name], [2017].[Postal Code], [2017].[Credential] FROM 2017 WHERE (([2017].[Credential] Like '%LCSW%'));"

    Results : Row Count = 4263 - Elapsed Time = 00:00:14.6234074. Of that, 00:00:01.1673430 was used to fill the DataTable

    Code:
            sw.Reset()
            sw.Start()
            Try
                Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDataSource & ";"
                Dim cn As New OleDbConnection(strConn)
                Dim da As OleDbDataAdapter = New OleDbDataAdapter()
                ds = New DataSet
                SQL = SQLQueryString
                cn.Open()
                da.SelectCommand = New OleDb.OleDbCommand(SQL, cn)
                da.Fill(ds)
                cn.Close()
                DSFillTime = sw.Elapsed
                'DGVQueryResults.SuspendLayout() ' Did not help'
                DGVQueryResults.DataSource = ds.Tables(0)
                DGVQueryResults.AutoResizeColumns()
                'DGVQueryResults.ResumeLayout() ' Did not help'
                TabControl1.SelectedIndex = 1
                sw.Stop()
                TxtStatus.Text = "Row Count = " & ds.Tables(0).Rows.Count.ToString & " - Elapsed Time = " & sw.Elapsed.ToString & ". Of that, " & DSFillTime.ToString & " was used to fill the DataTable"
            Catch ex As Exception
                TxtStatus.Text = "An error occured, message is '" & ex.Message & "'"
            End Try

    I tried Suspend/Resume Layout - is there anything else I can to to improve the fill speed ? Computer is a 4 core 3.33 GHz, 8 GB RAM.
    Monday, August 27, 2018 5:37 AM

Answers

  • I'd just about be willing to bet that the majority of that time occurs in the AutoResizeColumns method.  Comment out that line then rerun the test.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by Devon_Nullman Monday, August 27, 2018 2:29 PM
    Monday, August 27, 2018 10:33 AM

All replies

  • Do you have some event handlers for the grid? Try commenting the code in order to analyse the influence.

    Monday, August 27, 2018 7:54 AM
  • I tried Suspend/Resume Layout - is there anything else I can to to improve the fill speed ?

    You can use a datareader, a custom type, a\and a List(of T) and bind collection to the control.

    The link shows you how to use that datareader.

    https://social.msdn.microsoft.com/Forums/en-US/dcdcc692-04ed-4c91-b97d-f8e3ef661c2e/using-datareader-with-access-database?forum=adodotnetdataproviders

    http://lauteikkehn.blogspot.com/2012/03/datatable-vs-list.html

    https://dzone.com/articles/reasons-move-datatables

    https://www.codeguru.com/vb/gen/vb_misc/oop/article.php/c7063/Data-Transfer-Object-Pattern-Goes-VBNET.htm

    Try it what do you have to loose?

    dim dtos = List(of DTO)
    
    'other code
    
    while oReader.Read()
    
        dim dto = new DTO
    
        dto.FirstName = oReader('FirstName')
    
        dtos.Add(dto)
    end do
    
    control.Datasource = dtos
    
    ----------------------------------------
    Typical DTO 
    
    Public Class DtoProject
    
        Public Property ProjectId As Int32
        Public Property ClientName As String
        Public Property ProjectName As String
        Public Property Technology As String
        Public Property ProjectType As String
        Public Property UserId As String
        Public Property StartDate  As DateTime
        Public Property EndDate As DateTime? 
        Public Property Cost As Decimal
       
    End Class
    


    • Edited by DA924x Monday, August 27, 2018 8:25 AM
    Monday, August 27, 2018 8:22 AM
  • I'd just about be willing to bet that the majority of that time occurs in the AutoResizeColumns method.  Comment out that line then rerun the test.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by Devon_Nullman Monday, August 27, 2018 2:29 PM
    Monday, August 27, 2018 10:33 AM
  • It's the DataGridViewColumnHeadersHeightSizeMode that gets ya every time...

    Here is an example of 'filling' a datatable and passing it through background worker. This example creates 20 columns of various data types and then adds 500,000 rows. the entire procedure takes 10 seconds on my machine.

    Public Class MassColumns
        Dim DT As New DataTable
        Dim StpWch As New Stopwatch
        WithEvents BGW As New System.ComponentModel.BackgroundWorker With {.WorkerReportsProgress = True}
    
        Private Sub MassColumns_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            BGW.RunWorkerAsync()
            StpWch.Start()
        End Sub
    
        Private Sub BGW_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles BGW.DoWork
            'add random columns/datatypes
            For i As Integer = 0 To 20
                If i.ToString.Contains("2") Then
                    DT.Columns.Add("Col" & i, GetType(System.DateTime))
                ElseIf i.ToString.Contains("3") Then
                    DT.Columns.Add("Col" & i, GetType(System.Int32))
                ElseIf i.ToString.Contains("4") Then
                    DT.Columns.Add("Col" & i, GetType(System.Int64))
                Else
                    DT.Columns.Add("Col" & i, GetType(System.String))
                End If
            Next
            'Add 500,000 rows
            For i As Integer = 0 To 500000
                Dim newrec As DataRow = DT.NewRow
                For Each col As DataColumn In DT.Columns
                    Select Case col.DataType
                        Case GetType(System.DateTime)
                            newrec(col.ColumnName) = Now
                        Case GetType(System.Int32)
                            newrec(col.ColumnName) = 100 * Rnd()
                        Case GetType(System.Int64)
                            newrec(col.ColumnName) = 10000 * Rnd()
                        Case GetType(System.String)
                            newrec(col.ColumnName) = 10000 * Rnd()
                    End Select
                    '  
                Next
                DT.Rows.Add(newrec)
            Next
            Dim DGV As New DataGridView
            DGV.DataSource = DT
            BGW.ReportProgress(100, DT)
        End Sub
    
        Private Sub BGW_ProgressChanged(sender As Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles BGW.ProgressChanged
            DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing
            DataGridView1.DataSource = DirectCast(e.UserState, DataTable)
            MsgBox(StpWch.ElapsedMilliseconds)
        End Sub
    End Class


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Monday, August 27, 2018 12:40 PM
  • I'd just about be willing to bet that the majority of that time occurs in the AutoResizeColumns method.  Comment out that line then rerun the test.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    I commented out the AutoResizeColumns Method, and ran the same query on the same table:

    Row Count = 4263 - Elapsed Time = 00:00:13.0511716. Of that, 00:00:01.1694275 was used to fill the DataTable 
    I then Set AutoSizeColumnsMode and AutoSizeRowsMode both to "None" and added this:

    DGVQueryResults.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
    DGVQueryResults.AutoResizeColumns()
    


    Row Count = 4263 - Elapsed Time = 00:00:04.7839251. Of that, 00:00:01.1790099 was used to fill the DataTable

    On another note - My view of the forum today is what I'd call "Anonymous" mode. The only reason I know this cam from you is your name after the comment. Below that, all I see is "Moderator" and when it was posted. Every other comment I see nothing, except for DA924x because he edited his comment.

    Monday, August 27, 2018 2:29 PM
  • Do you have some event handlers for the grid? Try commenting the code in order to analyse the influence.

    Nope, the DataGridView is just for display. It can be sorted but basically nothing else.
    Monday, August 27, 2018 4:28 PM