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
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
You can make one db call with 3 select statements, and load 3 tables in one dataset with way less code — T.S.
Show data with server side paging implementation. No body will be interested in seeing 1000 rows at once on the screen. — Thangadurai

SSS SSS · Accepted Answer · 2019-11-26T05:22:51

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 Answers