DataGridView takes a long time to load

[nkotbox]

I am developing a call logging app which gets its data from an old access database. That database is now tables only. The problem I am having is that the datagridview's take very long to load. (5-7 seconds) this is a tabbed app so everytime I go to a tab with a DGV I have to wait for it to load. Is there a way to speed this up. There is roughly 1000 lines to load for each DGV right now and each grid has 6 columns.

This is my first experience using VB with a database so I am sure the app is full of ineffiencies.

        daCallLog = New OleDb.OleDbDataAdapter(UrgentSql, Login.con)
        daCallLog.Fill(dsCallLog, "UrgentCallsDS")
        daCallLog = New OleDb.OleDbDataAdapter(CustomersSql, Login.con)
        daCallLog.Fill(dsCallLog, "MyCustomersDS")
        daCallLog = New OleDb.OleDbDataAdapter(MyCallsSql, Login.con)
        daCallLog.Fill(dsCallLog, "MyCallsDS")


        DataGridView1.DataSource = dsCallLog.Tables("MyCallsDS")
        DataGridView2.DataSource = dsCallLog.Tables("UrgentCallsDS")
        DataGridView3.DataSource = dsCallLog.Tables("MyCustomersDS")

I was also trying to change the size of the columns when opening the tab containing the DGV. I edited this out and that did not seem to help the load time.

If TabControl1.SelectedIndex = 3 Then
            DataGridView1.Columns(0).Width = 80
            DataGridView1.Columns(1).Width = 50
            ' DataGridView1.Columns(2).Width = 150
            DataGridView1.Columns(3).Width = 65
            DataGridView1.Columns(4).Width = 65
            DataGridView1.Columns(5).Width = 50
        End If
        If TabControl1.SelectedIndex = 4 Then
            DataGridView2.Columns(0).Width = 80
            DataGridView2.Columns(1).Width = 50
            'DataGridView2.Columns(2).Width = 150
            DataGridView2.Columns(3).Width = 65
            DataGridView2.Columns(4).Width = 65
            DataGridView2.Columns(5).Width = 50
        End If
        If TabControl1.SelectedIndex = 5 Then
            DataGridView3.Columns(0).Width = 200
            DataGridView3.Columns(1).Width = 50
            DataGridView3.Columns(2).Width = 150
            DataGridView3.Columns(3).Width = 100
            DataGridView3.Columns(4).Width = 100
            DataGridView3.Columns(5).Width = 100
        End If

Thanks for any help you can provide.

 

[Menthos]

The two areas I'd look at are the queries feeding your dataadapters and the underlying tables.... do you have indexes on those tables optimised for the queries you're running?

 

[nkotbox]

DataGridView

I see that the tables do have a number of indexes added to them. I did not specifically add them. I am unsure exactly what fields should be indexed as one query pulls from 2 tables. See my queries below. If you have suggestions, let me know.

Dim UrgentSql As String = "SELECT [Call Records].dt, [Call Records].SerialNO, [Call Records].Log, [Call Records].Contact, [Call Records].users, [Call Records].status" & _
    " FROM [Call Records] WHERE ([Call Records].users) = " & "'" & username & "'" & " AND ([Call Records].status) = " & "'" & U & "'" & " ORDER BY [Call Records].dt Desc"


        Dim MyCallsSql As String = "SELECT [Call Records].dt, [Call Records].SerialNO, [Call Records].Log, [Call Records].Contact, [Call Records].users, [Call Records].status FROM [Call Records] " & _
        "WHERE [Call Records].users = " & "'" & username & "'" & " ORDER BY [Call Records].dt Desc"


        Dim CustomersSql As String = "SELECT Profile.SiteName, Config.SerialNo, Config.ModelStyle, Config.ShipDate, Profile.City, Profile.State FROM Config RIGHT JOIN Profile ON Config.CustID = Profile.CustID" & " ORDER BY Profile.SiteName Asc"

 

[JohnH]

DataGridView takes a long time to load

If you check the two parts with a Stopwatch I think you will find loading data from database takes the long time, loading data into DataGridView doesn't. There could of course be something strange going on that makes it the other way around, but normally that is the case.

 

[nkotbox]

The DataGrid's are loaded at startup and this loading seems to take place very quick. When I click on the tab that contains the DataGrid, that is where the delay is. That leads me to believe the view takes a long time to draw. Is this correct? Is there a better/different way?

I have loaded a combobox with the same data and it loads very fast. It seems certain that the drawing of the DGV with data is taking a very long time.

 

[JohnH]

Loading a DataGridView with the amount of data you're describing normally takes 5-20 milliseconds. Perhaps as much as 0.5 seconds if all columns have AutoSizeMode set to AllCells, which in terms of graphics operations is the most expensive. Though some event handling for the grid like string formatting could extend that time somewhat. So I'm not convinced you draw the right conclusions. You should measure each operation like I said, then you'll know.

everytime I go to a tab with a DGV I have to wait for it to load

This also needs to be clarified, do you read from database each time you click different tabs?

 

[nkotbox]

I do not read from the database each time I click on a tab. The DGV's are loaded at startup. Only when a new call is added to the database is this data refreshed. So if I open the app and then click on a tab containing a DGV, I have to wait 5-8 seconds for it to open. I can then click to another tab and then immediately click back to the DGV tab and I have to wait another 5-8 seconds.

 

[JohnH]

So the time is clearly not related to database at all, unless.. whatever the below quote means.

Only when a new call is added to the database is this data refreshed.

Still weird, because when I switch tabs containing grids with equal amount of data there is no delay, it happens instantaneously. Do you have any event handlers for the grid?

 

[nkotbox]

Ok, progress, finally...

I had AutosizeColumnsMode set to Fill and AutosizeRowsMode set to All Cells -- this gives me a very long delay

Now I have AutosizeColumnsMode set to Fill and AutosizeRowsMode set to None -- Now the tabs load immediately.

Dock mode set to fill in both instances.

Thanks for all of your help. Strange that the mix of options causes this delay.