[RESOLVED] Speeding Up a Datasource

[Shaggy Hiker]

I fill a datatable, and set it as the source for a DGV. This is just for display. It was working acceptably the way I had it, but the guy who is using it wanted more columns....substantially more columns, though they don't all have to be visible all the time. The result is that this line:

Me.dgvVarious.DataSource = mGeneralTable

now takes about 10 seconds to execute. The reason for the names is simply that they are correct. The DGV displays whatever is in the table, and the user decides what is in the table based on a few simple choices. In all cases, the number of records is going to be considerably less than 1,000, though it will likely be a few hundred records.

I can't say that I have ever seen setting a table as a datasource take this long, but then again, I'm not sure that I have ever used a table with 62 columns in a DGV before.

Are there any simple things that can be done to speed up that one line? I assume the slowdown has to do with creating columns for all the fields, populating them, and so forth. I don't have any real control over those things...in fact, I don't have much control over this at all, so they may just have to live with the 10 second pause if they want that many columns (it had been about 6), but I figured I'd ask in case there was some simple speed-boosting tip I wasn't aware of.

[wes4dbt]

I've seen the AutoSizeColumn have a major affect on the display speed. I leave it set to None if possible. btw- some of the delay be coming from the "Fill" method??

[Shaggy Hiker]

I tested everything I thought might be a problem, and narrowed it down to that one single line. The rest amounts to a couple tenths of a second total.

That's a good tip, though. Setting AutoSizeColumn to None cut the time in half...or maybe a bit more. I'm down to 4.8 seconds. That's a pretty significant improvement.

[jmcilhinney]

One option might be to make the grid not visible, bind, then make it visible again. If that speeds things up sufficiently then you may not need anything else but, if it looks bad to the user then you could always temporarily replace the grid with a Label that says "Loading.." or a PictureBox with an appropriate image.

[Shaggy Hiker]

Hiding would look bad, in this case, and I've used the other options in other programs. In this case, I think it isn't justified. After all, only two people will use the program. It's not exactly for mass consumption, so a bit of waiting is tolerable. So, after removing the column sizing, I just changed the mouse cursor to the wait cursor while the loading happens. If I hadn't gotten any suggestions, that cursor was ALL I was going to do, cause the two who will use the program will understand that they wanted 10x the number of columns, and that it could have a cost. If there was a wider audience, I might have done it differently.

[ChrisE]

Hi,

I setup the Columns first, never Tested if it is faster, but give it a try.

 Private Sub InitDGV()

        Dim Key As String = Nothing

        'With table.Columns
        With DataGridView1

            .AllowUserToAddRows = False
            .AllowUserToDeleteRows = False
            .AllowUserToOrderColumns = False
            .AllowUserToResizeColumns = False
            .AllowUserToResizeRows = False

            Key = "LfdNr"
            .Columns.Add(Key, Key)
            .Columns(Key).Width = 40
            .Columns(Key).Visible = True
            .Columns(Key).ReadOnly = True
            .Columns(Key).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft

            Key = "Tag"
            .Columns.Add(Key, Key)
            .Columns(Key).Width = 40
            .Columns(Key).ReadOnly = True
            .Columns(Key).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft

            Key = "Datum"
            .Columns.Add(Key, Key)
            .Columns(Key).Width = 80
            .Columns(Key).ReadOnly = True
            .Columns(Key).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft

            Key = "Kw"
            .Columns.Add(Key, Key)
            .Columns(Key).Width = 35
            .Columns(Key).ReadOnly = True
            .Columns(Key).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft

            Key = "kommt vorm."
            .Columns.Add(Key, Key)
            .Columns(Key).Width = 55
            .Columns(Key).ReadOnly = False
            .Columns(Key).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter

            Key = "geht vorm."
            .Columns.Add(Key, Key)
            .Columns(Key).Width = 55
            .Columns(Key).ReadOnly = False
            .Columns(Key).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter

            Key = "min vorm."
            .Columns.Add(Key, Key)
            .Columns(Key).Width = 50
            .Columns(Key).ReadOnly = True
            .Columns(Key).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            Key = "kommt nachm."
            .Columns.Add(Key, Key)
            .Columns(Key).Width = 50
            .Columns(Key).ReadOnly = False
            .Columns(Key).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            Key = "geht nachm."
            .Columns.Add(Key, Key)
            .Columns(Key).Width = 50
            .Columns(Key).ReadOnly = False
            .Columns(Key).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            Key = "min nachm."
            .Columns.Add(Key, Key)
            .Columns(Key).Width = 50
            .Columns(Key).ReadOnly = True
            .Columns(Key).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            Key = "Std"
            .Columns.Add(Key, Key)
            .Columns(Key).Width = 50
            .Columns(Key).ReadOnly = True
            .Columns(Key).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            Key = "Ho"
            .Columns.Add(Key, Key)
            .Columns(Key).Visible = False
            .Columns(Key).Width = 0
            .Columns(Key).ReadOnly = True
            .Columns(Key).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            Key = "GesMin"
            .Columns.Add(Key, Key)
            .Columns(Key).Width = 50
            .Columns(Key).ReadOnly = True
            .Columns(Key).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            Key = "Datum Angelegt"
            .Columns.Add(Key, Key)
            .Columns(Key).Width = 50
            .Columns(Key).ReadOnly = True
            .Columns(Key).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            Key = "Datum Updated"
            .Columns.Add(Key, Key)
            .Columns(Key).Width = 50
            .Columns(Key).ReadOnly = True
            .Columns(Key).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        End With
    End Sub

in my Form Load..

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim Mdb As String = Application.StartupPath & "\..\TestAccess.mdb"

        Me.Cursor = Cursors.WaitCursor
        If Not ConnectionOpen(Cn, Mdb, Nothing) Then
            Me.Close()
            Exit Sub
        End If
        Me.Cursor = Cursors.Default
        InitDGV()
        ActivMonat = If(ActivMonat = 0, Month(Now), ActivMonat)
        ActivJahr = If(ActivJahr = 0, Year(Now), ActivJahr)
        IniCombos()
           
    End Sub

regards
Chris

[kpmc]

maybe set the autogenerate columns to false and ad them as you need them

[kpmc]

Shaggy, Im running some tests over here, and I am not getting the 10 second delay on 100 columns with 10,000 rows. THis entire routine takes 3 seconds, which includes the time it takes me to click a button

Public Class MassColumns
    Dim DT As New DataTable
    Dim StpWch As New Stopwatch
    Private Sub MassColumns_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        StpWch.Start()
        For i As Integer = 0 To 100
            DT.Columns.Add("Col" & i, GetType(System.String))
        Next
        For i As Integer = 0 To 10000
            Dim newrec As DataRow = DT.NewRow
            For Each col As DataColumn In DT.Columns
                newrec(col.ColumnName) = "Value" & i
            Next
            DT.Rows.Add(newrec)
        Next

    End Sub

    Private Sub ButtonBindGrid_Click(sender As Object, e As EventArgs) Handles ButtonBindGrid.Click

        DataGridView1.DataSource = DT
        MsgBox(StpWch.Elapsed.Seconds)
    End Sub
End Class

[Shaggy Hiker]

I can't add columns as I need them, because the actual table bound (and the columns it contains) could change due to selections made by the user. I don't know which columns are needed, and they aren't fixed.

However, that test is pretty interesting. At first I thought it might be the datatype, but you are using strings. No type in my table is any worse than that. I suppose there could be some cost to using integers, dates, and doubles as those all have to be turned into strings for display, but that' doesn't seem like it could be significant.

The more accurate comparison would be to start the stopwatch just before this line:

DataGridView1.DataSource = DT

since that is essentially what I did. So, that, plus what wes4dbt showed, suggests that something about the properties set for the DGV is slowing it down. Autosizing the columns was costing me over 5 seconds, so perhaps there is some other property I have set that is costing more, as well. I'll have to look into that on Monday.

[kpmc]

with a few changes I am still only getting 3 seconds on setting the datasource. I have even extended the column count to 200. Im working on an idea, ill get back to you.

Public Class MassColumns
    Dim DT As New DataTable
    Dim StpWch As New Stopwatch
    Private Sub MassColumns_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        For i As Integer = 0 To 200
            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
        For i As Integer = 0 To 10000
            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

    End Sub

    Private Sub ButtonBindGrid_Click(sender As Object, e As EventArgs) Handles ButtonBindGrid.Click
        StpWch.Start()
        DataGridView1.DataSource = DT
        MsgBox(StpWch.Elapsed.Seconds)
    End Sub
End Class

[kpmc]

Here is another idea, the binding was reduced to 123ms in this example.

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()
    End Sub

    Private Sub BGW_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles BGW.DoWork
        For i As Integer = 0 To 200
            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
        For i As Integer = 0 To 10000
            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, DGV)
    End Sub

    Private Sub BGW_ProgressChanged(sender As Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles BGW.ProgressChanged
        StpWch.Start()
        Dim DGV As DataGridView = DirectCast(e.UserState, DataGridView)
        DGV.Dock = DockStyle.Fill
        Panel1.Controls.Add(DGV)
        MsgBox(StpWch.ElapsedMilliseconds)
    End Sub
End Class

[Shaggy Hiker]

Interesting idea, but I'm not sure the timing is valid in that case. The cost was setting the datasource. In that example, you don't time that, you time the display, effectively. Sure, the binding is actually happening in the background, so what you are timing seems correct, it's just that, in my case, the user would have to sit and wait for the BGW to run as well as the DGV to be displayed. There's nothing else that can meaningfully happen in the foreground while the BGW is running in the background, so there's no real gain from a second thread.

I've also been considering the possibility that the three seconds you are seeing is essentially the same as the 4.8 seconds that I am seeing. That isn't a huge difference, and may be due entirely to the somewhat strange environment my 4.8 second result came from. It's worth a bit of playing around with properties, it just may not be worth MUCH playing around with properties. I was testing in a remote session on a virtual server. It's always been a bit slow. I don't think it should be THAT much slower, but maybe...

[kpmc]

you can start the stopwatch prior to the binding and the result is the same

[Shaggy Hiker]

That would suggest that the cause of that change is due entirely to the DGV being displayed, a point that was discussed earlier. If I hid the DGV during the binding, that...well, that may be possible. I'm not sure it's worth it for such a trivial program. On first use (which is likely to be about 70% of all use cases), it wouldn't be an issue to leave the DGV hidden until it's ready. For subsequent use, the DGV would vanish (replaced by some facade), then re-appear. Actually, I could leave the old one in place, create a new one, then swap them. That might work pretty well.

[Shaggy Hiker]

I screwed up and changed a few properties on the DGV. I screwed up further and did some other things before checking the timing, such that I had forgotten which properties I had changed by the time it dawned on me that the DGV was displaying really fast. A bit of timing showed that it was, indeed, displaying really fast. In fact, the time to display the data had dropped from about 5s down to about 60ms....and I couldn't remember what I had changed.

After a bit of trial and error, and comparing with a different project, I found it: ColumnHeaderHeightSizeMode.

That's a pretty interesting property. When you drop a new DGV on a form, that property is set to AutoSize, but AutoSize is bold, which indicates that AutoSize is not the default value for the property (even though it really IS the default, since it's the one you get by default). A bit of checking showed that EnableResizing is the default. That's the ticket, too. With that property set to AutoSize, I was getting the 5s performance, but changing it to EnableResizing boosts the performance considerably. A performance boost of 80x is pretty good.

To be thorough, I also tested the third option for that property, which is DisableResizing. During this testing, performance improved even more (I went back and forth between the three properties to check). The result is that I was getting 5s with AutoSize, and 20-50ms with either of the other two. This makes a certain amount of sense. Apparently, the AutoSize setting causes the DGV to do a more complicated measure per....something.

Anyways, display time has now been reduced from 10s to sub second, which is good enough for me.

[ChrisE]

After a bit of trial and error, and comparing with a different project, I found it: ColumnHeaderHeightSizeMode.

That's a pretty interesting property. When you drop a new DGV on a form, that property is set to AutoSize, but AutoSize is bold, which indicates that AutoSize is not the default value for the property (even though it really IS the default, since it's the one you get by default). A bit of checking showed that EnableResizing is the default. That's the ticket, too. With that property set to AutoSize, I was getting the 5s performance, but changing it to EnableResizing boosts the performance considerably. A performance boost of 80x is pretty good.

Anyways, display time has now been reduced from 10s to sub second, which is good enough for me.

I never noticed that one(ColumnHeaderHeightSizeMode), good to know

regards
Chris

[Jenner]

It's probably triggering a resize for every row-rendered rather than one for the whole mess of it. There's probably some things to SuspendLayout or BeginUnboundDataLoad or something along those lines to stop the behavior until everything is loaded, but as I never use DGVs I don't know.