CODEWITHSUNDEEP
sqlvb.net
Osama Gadour
Osama Gadour

Reputation: 187

combined these 2 sql queries in VB.NET

I have these 2 sql queries, the first one is loading data from sql into DGV in vb.net, and then I have to go row by row to execute the second query to get some additional data (newprice) based on some conditions for each record ( the "newprice" is in the same table in the first query ), everything is working fine as I want, but the problem that is taking too long as the code is going through all the rows, with big data it takes a lot of time, is there anyway to optimize this code to work faster by combining the 2 queries? I've tried but couldn't figure out how to do it. thank you

Query 1:

                Dim sqqq as string = "select products.psize,producth.pcode,producth.pname,coalesce(sum(cast(case when producth.details = 'اذن اضافة مشتريات' or producth.details = 'اذن تسوية جردية مدينة' or  producth.details = 'إضافة رصيد افتتاحي' or producth.details ='اذن اضافة مرتجعات مبيعات' then cast(producth.quanitity as money) else 0 end as money)),0) as tadd,coalesce(sum(cast(case when producth.details = 'اذن صرف مبيعات' or producth.details = 'اذن تسوية جردية دائنة' or  producth.details = 'اذن ترجيع مشتريات' then cast(producth.quanitity as money) else 0 end as money)),0) as tremove from producth inner join products on products.parcode=producth.pcode where producth.pdate <= '" + ad + "' group by producth.pcode,producth.pname,products.psize"
                Dim cmdd As New SqlCommand(sqqq, con)
                Dim dddr As SqlDataReader = cmdd.ExecuteReader
                While dddr.Read
                   
                    DataGridView1.Rows.Add(DataGridView1.Rows.Count + 1, dddr("pcode"), dddr("pname"), dddr("psize"), "0.000", "NEWPRICE", "0.000")
                End While
                dddr.Close()

Query 2

                For i As Integer = 0 To DataGridView1.RowCount - 1
                    Dim sq3 As String = "select newprice from producth where pp=(SELECT coalesce(MAX(CAST(pp AS int)),'00') AS pp FROm producth WHERE (pcode = '" + DataGridView1.Rows(i).Cells(1).Value + "') AND (details = 'اذن اضافة مشتريات' OR details = 'اذن تسوية جردية مدينة' OR  details = 'إضافة رصيد افتتاحي') AND (pdate = ( SELECT coalesce(MAX(CAST(pdate AS date)),'01/01/1911') AS pp FROm producth WHERE (pcode = '" + DataGridView1.Rows(i).Cells(1).Value + "') AND (details = 'اذن اضافة مشتريات' OR details = 'اذن تسوية جردية مدينة' OR  details = 'إضافة رصيد افتتاحي') AND (pdate <= '" + ad + "')  ))) and pcode='" + DataGridView1.Rows(i).Cells(1).Value + "'"
                    Dim cmd3 As New SqlCommand(sq3, con)
                    Dim dr3 As SqlDataReader = cmd3.ExecuteReader
                    If dr3.Read Then
                        DataGridView1.Rows(i).Cells(5).Value = dr3("newprice")
                    End If
                    dr3.Close()
                Next

Upvotes: 1

Views: 116

Answers (1)

Tarik
Tarik

Reputation: 11209

To improve performance, you should do the following:

Create a DataTable and fill the data table using a DataAdapter instead of using a DataReader. You can then bind your DataTable with your DataGridView. You then loop through the rows of the DataTable and do whatever update you please to do. While you update the DataTable, you can suspend UI refresh to avoid having the UI updated upon each change in the DataTable.

If you want extra speed up, create a class to contain a single record and use property get and set for each field. Using a DataReader create an instance for each record and add it to a List of type List of YourClass where YourClass is the class you created. Bind the list to the DataGridView.

Extra advices:

  • Use parameterized queries
  • Create constants for strings representing transaction types.
  • Instead of repeating the same string for transaction type, use a transaction type table and have an integer for each type and a short and long description. Use this code everywhere instead of the hardcoded string.
  • Use x in (a, b, c, d) instead of x = a or x = b...

Upvotes: 2

Related Questions