Faster way to load data on datagridview?

Question

I want to load the data much faster because it takes almost 2 minutes to make all this process. What I want to happen is to load it much faster, is there a way to do it?

I have almost 1k data per datagridview and the database process took about 1-2 secs.

Below is my code

Try
            Dim cycle As Integer = txt_Cycle.Text
            Dim cyclelast, cycle2last, cycle3last As Integer
            Dim rt = txt_Route.Text


            cyclelast = cycle - 1
            cycle2last = cyclelast - 1
            cycle3last = cycle2last - 1

            '2 month
            Using command As New SqlCommand()
                command.Connection = conn

                Dim parameterNames As New List(Of String)(dt_data.RowCount - 2)
                For i As Integer = 0 To dt_data.RowCount - 3
                    Dim parameterName As String = "@meter_num_" & i
                    Dim meter_number As String = dt_data.Rows(i).Cells(3).Value
                    command.Parameters.AddWithValue(parameterName, meter_number)
                    parameterNames.Add(parameterName)
                Next

                command.CommandText = String.Format("SELECT * FROM customer WHERE cycle = @cycle2last and meter_num IN (SELECT meter_num FROM customer WHERE cycle = @cyclelast and rt = @rt and meter_num IN ({0})) 
                AND meter_num IN (SELECT meter_num FROM customer WHERE cycle = @cycle AND rt = @rt AND meter_num IN ({0})) ORDER BY Client_Name ASC,meter_num ASC, MtrType ASC", String.Join(",", parameterNames))
                command.Parameters.AddWithValue("@cycle2last", cycle2last)
                command.Parameters.AddWithValue("@cyclelast", cyclelast)
                command.Parameters.AddWithValue("@cycle", cycle)
                command.Parameters.AddWithValue("@rt", rt)

                Dim da As New SqlDataAdapter(command)
                Dim ds As New DataSet
                da.Fill(ds, "customer")
                Compare_Reading.dt_last2month.DataSource = ds.Tables(0)
            End Using

            'last month
            Using command As New SqlCommand()
                command.Connection = conn

                Dim parameterNames As New List(Of String)(dt_data.RowCount - 2)
                For i As Integer = 0 To dt_data.RowCount - 3
                    Dim parameterName As String = "@meter_num_" & i
                    Dim meter_number As String = dt_data.Rows(i).Cells(3).Value
                    command.Parameters.AddWithValue(parameterName, meter_number)
                    parameterNames.Add(parameterName)
                Next

                command.CommandText = String.Format("SELECT * FROM customer WHERE cycle = @cyclelast and meter_num IN (SELECT meter_num FROM customer WHERE cycle = @cycle2last and rt = @rt and meter_num IN ({0})) 
                AND meter_num IN (SELECT meter_num FROM customer WHERE cycle = @cycle AND rt = @rt AND meter_num IN ({0})) ORDER BY Client_Name ASC,meter_num ASC, MtrType ASC", String.Join(",", parameterNames))
                command.Parameters.AddWithValue("@cyclelast", cyclelast)
                command.Parameters.AddWithValue("@cycle2last", cycle2last)
                command.Parameters.AddWithValue("@cycle", cycle)
                command.Parameters.AddWithValue("@rt", rt)

                Dim da As New SqlDataAdapter(command)
                Dim ds As New DataSet
                da.Fill(ds, "customer")
                Compare_Reading.dt_lastmonth.DataSource = ds.Tables(0)
            End Using
            'curmonth
            Using command As New SqlCommand()
                command.Connection = conn

                Dim parameterNames As New List(Of String)(dt_data.RowCount - 2)
                For i As Integer = 0 To dt_data.RowCount - 3
                    Dim parameterName As String = "@meter_num_" & i
                    Dim meter_number As String = dt_data.Rows(i).Cells(3).Value
                    command.Parameters.AddWithValue(parameterName, meter_number)
                    parameterNames.Add(parameterName)
                Next

                command.CommandText = String.Format("SELECT * FROM customer WHERE cycle = @cycle AND meter_num IN (SELECT meter_num FROM customer WHERE cycle = @cyclelast AND rt = @rt AND meter_num IN ({0}))
                                    AND meter_num IN (SELECT meter_num FROM customer WHERE cycle = @cycle2last AND rt = @rt AND meter_num IN ({0})) ORDER BY Client_Name ASC,meter_num ASC, MtrType ASC", String.Join(",", parameterNames))
                command.Parameters.AddWithValue("@cycle", cycle)
                command.Parameters.AddWithValue("@cyclelast", cyclelast)
                command.Parameters.AddWithValue("@cycle2last", cycle2last)
                command.Parameters.AddWithValue("@rt", rt)

                Dim da As New SqlDataAdapter(command)
                Dim ds As New DataSet
                da.Fill(ds, "customer")
                Compare_Reading.dt_curmonth.DataSource = ds.Tables(0)
            End Using
            Me.Hide()



            Compare_Reading.computation()
            Compare_Reading.txt_Route.Text = txt_Route.Text
            Compare_Reading.txt_billday.Text = txt_BillDay.Text
            Compare_Reading.txt_itn.Text = txt_itn.Text
            Compare_Reading.nup_cycle.Value = txt_Cycle.Text
            'header name
            Compare_Reading.headername()
            Compare_Reading.Show()
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Critical, "SQL Error")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
            'End Try
            'Catch ex As Exception
            'MessageBox.Show(String.Format("Error: {0}", ex.Message), "Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Error)
        End Try

and this is for the computation class

Public Sub computation()
        'Showmonth GridView
        For i As Integer = 0 To dt_curmonth.RowCount - 1
            dt_showmonth.Rows.Add(dt_curmonth.Rows(i).Cells(0).Value)



            dt_showmonth.Rows(i).Cells(1).Value = dt_curmonth.Rows(i).Cells(4).Value 'itinerary
            dt_showmonth.Rows(i).Cells(2).Value = dt_curmonth.Rows(i).Cells(11).Value 'mtr num
            dt_showmonth.Rows(i).Cells(3).Value = dt_last2month.Rows(i).Cells(14).Value ' dec
            dt_showmonth.Rows(i).Cells(4).Value = dt_lastmonth.Rows(i).Cells(14).Value ' jan
            dt_showmonth.Rows(i).Cells(5).Value = dt_curmonth.Rows(i).Cells(14).Value ' feb
            'dt_showmonth.Rows(i).Cells(10).Value = dt_curmonth.Rows(i).Cells(11).Value 'mtr num
            dt_showmonth.Rows(i).Cells(10).Value = dt_curmonth.Rows(i).Cells(12).Value 'mtr type
            dt_showmonth.Rows(i).Cells(11).Value = dt_last2month.Rows(i).Cells(18).Value 'mtr ff
            dt_showmonth.Rows(i).Cells(12).Value = dt_lastmonth.Rows(i).Cells(18).Value 'mtr ff
            dt_showmonth.Rows(i).Cells(13).Value = dt_curmonth.Rows(i).Cells(18).Value 'mtr ff

            If dt_last2month.Rows(i).Cells(14).Value = 0 Then 'last2
                dt_showmonth.Rows(i).Cells(3).Value = dt_lastmonth.Rows(i).Cells(13).Value
            End If
            If dt_lastmonth.Rows(i).Cells(14).Value = 0 Then 'last
                dt_showmonth.Rows(i).Cells(4).Value = dt_curmonth.Rows(i).Cells(13).Value
            End If

            Dim month2nd As Integer = dt_showmonth.Rows(i).Cells(3).Value
            Dim month1st As Integer = dt_showmonth.Rows(i).Cells(4).Value
            Dim curmonth As Integer = dt_showmonth.Rows(i).Cells(5).Value
            Dim high20 As Double
            Dim diff As Integer
            Dim diff1 As Integer
            Dim per20 As Double
            Dim less20 As Double

            diff = month1st - month2nd
            diff1 = curmonth - month1st
            per20 = diff * 0.3
            high20 = Math.Round((diff + per20), 2)
            less20 = Math.Round((diff - per20), 2)

            dt_showmonth.Rows(i).Cells(6).Value = diff
            dt_showmonth.Rows(i).Cells(7).Value = diff1
            dt_showmonth.Rows(i).Cells(8).Value = less20
            dt_showmonth.Rows(i).Cells(9).Value = high20


        Next
    End Sub

How many rows are you pulling back? How long does the database retrieval take? How long does the local computations take? How long does it take to transfer the page data to the client? — Dale K, Nov 26 '19 at 03:54
I know, but you have to time each part of it to know which bit needs improvement. Hence we need to know all those individual times. — Dale K, Nov 26 '19 at 03:56
You can make one db call with 3 select statements, and load 3 tables in one dataset with way less code — T.S., Nov 26 '19 at 04:07
http://vb.net-informations.com/dataset/dataset-multiple-tables-sqlserver.htm — sansknwoledge, Nov 26 '19 at 04:10
If your database query is taking 1-2s then its either the computations or the sending to the client which taking the time. — Dale K, Nov 26 '19 at 04:18
1000 rows could certainly be slow to transfer to the client - do you really need that many? — Dale K, Nov 26 '19 at 04:26
you can do all these math-things in query, it will be sure faster.. — MarioWu, Nov 26 '19 at 04:48
Show data with server side paging implementation. No body will be interested in seeing 1000 rows at once on the screen. — Thangadurai, Nov 26 '19 at 04:50
related - don't use [addwithvalue](http://www.dbdelta.com/addwithvalue-is-evil/) — SMor, Nov 26 '19 at 13:21

score 2 · Accepted Answer · answered Nov 26 '19 at 05:22

The slowness is from the datagridview updating the UI during your calculations. You shouldn't be editing .Cells property of DataGridView for a data-bound grid. Apart from anything else, clicking on a column heading to sort by that column will cause havoc.

Do all your calculations in-memory first and set the DataSource at the very end after all the calculations are finished. That way the DataGridView will render the screen only once.

Faster way to load data on datagridview?

1 Answers1