dcsimg
Results 1 to 4 of 4

Thread: Make Datagrid Load Faster

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2016
    Posts
    15

    Make Datagrid Load Faster

    Hello,
    I am trying to load a lot of records from a SQL database to VB.net datagrid but it is being too slow.
    I am sure that it has to do with the datagrid, especially because the same data loads quickly when exporting it to Excel(I also added below the code for the Excel part). So I guess it is not a problem with SQL connection and getting the data, also because when I try the same query in SQL Server Management Studio it is fairly quick. Do you know of any code, or code changes I have to make, or anything else I can do to make my load to the datagrid faster?
    Below is the code I have:

    Code:
    SqlWhere = SqlWhere.Remove(SqlWhere.Length - 3, 3)
    
            If cmbSecaucusQueries.Text = "Third Query" Then
                Label5.Text = "THIRD QUERY"
                Label5.ForeColor = Color.Blue
                expsql3 = "SELECT [Source],[Div],[WkEndDt],[Dept],[Vend],[KeyRec],[AllocAmt] FROM [FedPayHistory].[dbo].[Frt_Allocation] " & SqlWhere
            End If
    
            If cmbSecaucusQueries.Text = "Freight Analysis TY Qry 1" Then
                Label5.Text = "FREIGHT ANALYSIS TY QRY 1"
                Label5.ForeColor = Color.Blue
                expsql3 = "SELECT [Div],[Dept],[Vend],[ProNum],[AllocAmt],[WkEndDt] FROM [FedPayHistory].[dbo].[Frt_Allocation] WHERE [RecType] = 'MANUAL' AND [WkEndDt] = '" & txtboxWkEndDtStart.Text.Trim & "'"
            End If
    
            If cmbSecaucusQueries.Text = "809_909 Allocation" Then
                Label5.Text = "809_909 ALLOCATION"
                Label5.ForeColor = Color.Blue
                expsql3 = "SELECT [Source],[Div],[AllocAmt],[WkEndDt] FROM [FedPayHistory].[dbo].[Frt_Allocation] WHERE [RecType] = 'MANUAL' AND [WkEndDt] = '" & txtboxWkEndDtStart.Text.Trim & "'"
            End If
    
    
            myconn.Open()
            da1 = New SqlDataAdapter(expsql3, myconn)
            cmb = New SqlCommandBuilder(da1)
            ds1 = New DataSet()
            da1.Fill(ds1, expsql3)
            DataGridView5.DataSource = ds1.Tables(expsql3)
            myconn.Close()
    
    
    'THIS IS THE CODE TO EXPORT THE SAME DATA I CONNECT TO ABOVE
    Private Sub btnExport_Click(sender As System.Object, e As System.EventArgs) Handles btnExport.Click
                Dim oExcel As Object
                Dim oBook As Object
                Dim oSheet As Object
                Dim oQueryTable As Object
    
                DataGridView5.DataSource = Nothing
                DataGridView5.Refresh()
                DataGridView5.ReadOnly = True
    
                Try
                    oExcel = GetObject(, "Excel.Application")
                Catch ex As Exception
                    oExcel = CreateObject("Excel.Application")
                End Try
                myconn.Open()
                oBook = oExcel.Workbooks.Add
                oSheet = oBook.Worksheets(1)
                oQueryTable = oSheet.QueryTables.Add("OLEDB;driver={SQL Server};server=xxxxxxx; initial catalog=xxxxxxxx;user id=xxxxxxxx;password=xxxxxxxxx;" & ";", oSheet.Range("A1"), expsql3)
                oQueryTable.RefreshStyle = 2
                oQueryTable.Refresh(False)
                MsgBox("Data Exporting. Hit OK", MsgBoxStyle.Information, "Done")
                oExcel.Visible = True
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet)
                oSheet = Nothing
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
                oBook = Nothing
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oQueryTable)
                oQueryTable = Nothing
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
                oExcel = Nothing
                GC.Collect()
                GC.WaitForPendingFinalizers()
                GC.Collect()
                GC.WaitForPendingFinalizers()
                myconn.Close()
            End Sub
    Thank you very much in advance for your reply.

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,920

    Re: Make Datagrid Load Faster

    I'm not nearly as certain as you that the problem is in the datagrid. I can understand your reason for thinking it does, but I've always found that datagrids are effectively instant, even for massive datasets. I think you had best do a little timing before you decide on the problem. Add a Stopwatch object to the project, start it before running the da1.Fill line, stop it afterwards, and look at the elapsed milliseconds. You can do the same thing to measure the cost of showing the datatable. I think you will find that it is the query that is costing you time.

    If it IS the query, then there may be relatively little you can do....unless you don't have appropriate indexes on the table, which would be a quick and easy fix. Still, timing different parts of the code is essential for this type of thing, or else you may end up trying to fix the wrong problem. The Stopwatch object has the precision and accuracy necessary for that kind of timing.
    My usual boring signature: Nothing

  3. #3

    Thread Starter
    New Member
    Join Date
    Jan 2016
    Posts
    15

    Re: Make Datagrid Load Faster

    Hi,
    the query runs four times faster when run directly in SQL Server Management Studio, that is why I do not think it is the query nor the connection to the database since when I export the same data to Excel it takes about 35 sec but when I try to put the same data in the datagrid it takes 3 min. Any other thoughts, suggestions, code to use? Thank you very much for your help.

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,920

    Re: Make Datagrid Load Faster

    I know, but that's what the timing is about. I have a program that runs a SERIOUSLY messed up query to gather a large number of rows to populate a couple of datatables. As lengthy and awkward as the query is, I launch a background thread to start filling the datatables as one of the first steps in starting the application such that the tables might be ready by the time the user reaches the form where they will be used. It can take ten seconds to fill those tables. However, adding them to a DGV using the same line that you are using, takes no measurable time at all. I've done other, similar, things in other programs, and have never seen binding to a datatable take any time.

    That's why I'm saying that you really need to measure this, and the Stopwatch can do that. If you find that the binding really is taking the time, then that would be pretty interesting, but my money's on the query itself.

    Having said that, I'd also say that I generally don't see a difference between time taken by a .NET program and time taken by the same query in SSMS. If you time the pieces, you may find something really unexpected. I had a program that filled one grid SLOWLY. I thought it was the query, which was another monster. When I timed the query, though, I found that it took only a couple milliseconds, which is far too little to see. That got me digging deeper by timing other steps along the way. Eventually, I found that a method in a dll that I hadn't written, was throwing exceptions on every Null field returned. One exception per Null field in a query where most records had several Null fields. Exceptions are painfully slow to handle. Once I fixed that method by checking for Null, the slowdown was simply gone. So, it's not always where you expect, but there's no shortcut to timing.
    My usual boring signature: Nothing

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