CODEWITHSUNDEEP
sql-servervb.net
BigDaddy
BigDaddy

Reputation: 5

Faster way to load data on datagridview?

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

Upvotes: 0

Views: 323

Answers (1)

SSS
SSS

Reputation: 5393

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.

Upvotes: 2

Related Questions