dcsimg
Results 1 to 17 of 17

Thread: [RESOLVED] Speeding Up a Datasource

  1. #1

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,618

    Resolved [RESOLVED] Speeding Up a Datasource

    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.
    My usual boring signature: Nothing

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,823

    Re: Speeding Up a Datasource

    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??

  3. #3

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,618

    Re: Speeding Up a Datasource

    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.
    My usual boring signature: Nothing

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,375

    Re: Speeding Up a Datasource

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,618

    Re: Speeding Up a Datasource

    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.
    My usual boring signature: Nothing

  6. #6
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,366

    Re: Speeding Up a Datasource

    Hi,

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

    Code:
     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..

    Code:
    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
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    665

    Re: Speeding Up a Datasource

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

  8. #8
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    665

    Re: Speeding Up a Datasource

    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

    Code:
    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

  9. #9

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,618

    Re: Speeding Up a Datasource

    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.
    My usual boring signature: Nothing

  10. #10
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    665

    Re: Speeding Up a Datasource

    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.

    Code:
    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

  11. #11
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    665

    Re: Speeding Up a Datasource

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

    Code:
    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

  12. #12

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,618

    Re: Speeding Up a Datasource

    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...
    My usual boring signature: Nothing

  13. #13
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    665

    Re: Speeding Up a Datasource

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

  14. #14

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,618

    Re: Speeding Up a Datasource

    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.
    My usual boring signature: Nothing

  15. #15

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,618

    Re: Speeding Up a Datasource

    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.
    My usual boring signature: Nothing

  16. #16
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,366

    Re: Speeding Up a Datasource

    Quote Originally Posted by Shaggy Hiker View Post

    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
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  17. #17
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,706

    Re: [RESOLVED] Speeding Up a Datasource

    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.
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

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