none
Data loads very slow in datagridview problem RRS feed

  • Question

  • I am developing application for an accounting system. The main problem i am facing, is whenever I try to get data from database table, and do some calculations, it takes very long time to display in the gridview. For example, following is the code to display Profit/Loss statement in the grid. It takes 15 seconds to calculate and load.

    Public Sub fillDataGrid(fb As String)
            Try
    
                dt = New DataTable
                dt.Columns.Add("L. Particulars")
                dt.Columns.Add("L. Amount", System.Type.GetType("System.Double"))
                dt.Columns.Add("A. Particulars")
                dt.Columns.Add("A. Amount", System.Type.GetType("System.Double"))
                Dim str As String
                If fb = "fromB" Then
                    str = "select * from voucher where vouchertype='JOURNAL' or vouchertype='PURCHASE' or vouchertype='SALES'"
                Else
                    str = "select drparty,dramount,crparty,cramount from voucher where (vouchertype='JOURNAL' or vouchertype='PURCHASE' or vouchertype='SALES') and CDate(dt) between CDate('" & Me.MaskedTextBox1.Text & "') and CDate('" & Me.MaskedTextBox2.Text & "')"
                End If
                Dim r As OleDb.OleDbDataReader = obj.s(str)
                Dim amt1 As Double = 0 'purchase account total
                Dim amt2 As Double = 0 'sales account total
                Dim amt3 As Double = 0 'direct expense total
                Dim amt4 As Double = 0 'direct income total
                Dim amt5 As Double = 0 'indirect expense total
                Dim amt6 As Double = 0 'indirect income total
                Dim obj1 As New db
                While r.Read
                    Dim drparty As String() = r.Item("drparty").ToString.Split(",")
                    Dim dramount As String() = r.Item("dramount").ToString.Split(",")
                    Dim crparty As String() = r.Item("crparty").ToString.Split(",")
                    Dim cramount As String() = r.Item("cramount").ToString.Split(",")
                    For i = 0 To drparty.Count - 2
                        Dim str1 As String = "select srno from accounts where srno=" & drparty(i) & " and ledgertype=11"
    
                        Dim r1 As OleDb.OleDbDataReader = obj1.s(str1)
                        If r1.HasRows = True Then
                            amt1 = amt1 + dramount(i)
                        End If
                        r1.Close()
    
                    Next
                    For i = 0 To drparty.Count - 2
                        Dim str1 As String = "select srno from accounts where srno=" & drparty(i) & " and ledgertype=13"
    
                        Dim r1 As OleDb.OleDbDataReader = obj1.s(str1)
                        If r1.HasRows = True Then
                            amt3 = amt3 + dramount(i)
                        End If
                        r1.Close()
    
                    Next
                    For i = 0 To crparty.Count - 2
                        Dim str1 As String = "select srno from accounts where srno=" & crparty(i) & " and ledgertype=10"
    
                        Dim r1 As OleDb.OleDbDataReader = obj1.s(str1)
                        If r1.HasRows = True Then
                            amt2 = amt2 + cramount(i)
                        End If
                        r1.Close()
    
                    Next
                    For i = 0 To crparty.Count - 2
                        Dim str1 As String = "select srno from accounts where srno=" & crparty(i) & " and ledgertype=12"
    
                        Dim r1 As OleDb.OleDbDataReader = obj1.s(str1)
                        If r1.HasRows = True Then
                            amt4 = amt4 + cramount(i)
                        End If
                        r1.Close()
    
                    Next
                    For i = 0 To drparty.Count - 2
                        Dim str1 As String = "select srno from accounts where srno=" & drparty(i) & " and ledgertype=15"
    
                        Dim r1 As OleDb.OleDbDataReader = obj1.s(str1)
                        If r1.HasRows = True Then
                            amt5 = amt5 + dramount(i)
                        End If
                        r1.Close()
    
                    Next
                    For i = 0 To crparty.Count - 2
                        Dim str1 As String = "select srno from accounts where srno=" & crparty(i) & " and ledgertype=14"
    
                        Dim r1 As OleDb.OleDbDataReader = obj1.s(str1)
                        If r1.HasRows = True Then
                            amt6 = amt6 + cramount(i)
                        End If
                        r1.Close()
    
                    Next
    
                End While
                obj1 = Nothing
                r.Close()
    
                Dim dr As DataRow
                dr = dt.NewRow
                dr(0) = "Purchase Accounts"
                dr(1) = amt1.ToString("F2")
                dr(2) = "Sales Accounts"
                dr(3) = amt2.ToString("F2")
                dt.Rows.Add(dr)
                dr = dt.NewRow
                dr(0) = "Direct Expenses"
                dr(1) = amt3.ToString("F2")
                dr(2) = "Direct Incomes"
                dr(3) = amt4.ToString("F2")
                dt.Rows.Add(dr)
                dr = dt.NewRow
                dr(0) = "Indirect Expenses"
                dr(1) = amt5.ToString("F2")
                dr(2) = "Indirect Incomes"
                dr(3) = amt6.ToString("F2")
                dt.Rows.Add(dr)
                Dim inc As Double = 0
                Dim exp As Double = 0
                exp = amt1 + amt3 + amt5
                inc = amt2 + amt4 + amt6
    
                dr = dt.NewRow
                dr(0) = ""
                dr(1) = System.DBNull.Value
                dr(2) = ""
                dr(3) = System.DBNull.Value
                dt.Rows.Add(dr)
    
                dr = dt.NewRow
                dr(0) = ""
                dr(1) = exp.ToString("F2")
                dr(2) = ""
                dr(3) = inc.ToString("F2")
                dt.Rows.Add(dr)
    
    
                If inc - exp >= 0 Then
                    dr = dt.NewRow
                    dr(0) = "Nett Profit"
                    dr(1) = (inc - exp).ToString("F2")
                    dt.Rows.Add(dr)
                Else
                    dr = dt.NewRow
                    dr(0) = "Nett Loss"
                    dr(1) = ((inc - exp) * -1).ToString("F2")
                    dt.Rows.Add(dr)
                End If
                _finalamount = inc - exp
                Me.DataGridView1.DataSource = dt
                Me.DataGridView1.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                Me.DataGridView1.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                'adjustWidthDataGrid()
            Catch ex As Exception
                MsgBox(ex.Message)
    
    
            End Try
        End Sub

    How can i improve this, and just load the data within seconds? Or any other suggestions?


    Hemal Rathod Managing Director(HR Softwares) 213,Krushna Darshan,Parimal Chawk, Waghawadi Road,Bhavnagar-364001 EMail : info@hrsoftwares.in

    Monday, July 30, 2012 1:54 PM

Answers

  • I don't have any, but database normalization is a very important and popular topic.  You can easily find tons of documents describing how to normalize a database.  But in all honesty, the topic is big and I personally recommend that you buy a book on the topic (database design).

    Jose R. MCP
    Code Samples

    • Marked as answer by hemal rathod Monday, July 30, 2012 2:27 PM
    Monday, July 30, 2012 2:19 PM

All replies

  • Your problem is your database design.  You cannot take advantage of the SQL mechanism because you have multiple values stuffed together in a single field.  Your need to normalize this database in order to take advantage of the underlying SQL engine.  All that string splitting and re-querying the database is killer.

    Also note that it is bad practice to concatenate SQL statements.  Use parameterized queries or stored procedures.


    Jose R. MCP
    Code Samples

    Monday, July 30, 2012 2:08 PM
  • Great, Can you provide me some links on how to normalize my database tables from current desing?

    Hemal Rathod Managing Director(HR Softwares) 213,Krushna Darshan,Parimal Chawk, Waghawadi Road,Bhavnagar-364001 EMail : info@hrsoftwares.in

    Monday, July 30, 2012 2:13 PM
  • I don't have any, but database normalization is a very important and popular topic.  You can easily find tons of documents describing how to normalize a database.  But in all honesty, the topic is big and I personally recommend that you buy a book on the topic (database design).

    Jose R. MCP
    Code Samples

    • Marked as answer by hemal rathod Monday, July 30, 2012 2:27 PM
    Monday, July 30, 2012 2:19 PM
  • OK and Thanks

    Hemal Rathod Managing Director(HR Softwares) 213,Krushna Darshan,Parimal Chawk, Waghawadi Road,Bhavnagar-364001 EMail : info@hrsoftwares.in

    Monday, July 30, 2012 2:27 PM