title: combined these 2 sql queries in VB.NET
body: <p>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 &quot;newprice&quot; 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</p>
  <p>Query 1:</p>
  <pre><code>                Dim sqqq as string = &quot;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 &lt;= '&quot; + ad + &quot;' group by producth.pcode,producth.pname,products.psize&quot;
                  Dim cmdd As New SqlCommand(sqqq, con)
                  Dim dddr As SqlDataReader = cmdd.ExecuteReader
                  While dddr.Read
                     
                      DataGridView1.Rows.Add(DataGridView1.Rows.Count + 1, dddr(&quot;pcode&quot;), dddr(&quot;pname&quot;), dddr(&quot;psize&quot;), &quot;0.000&quot;, &quot;NEWPRICE&quot;, &quot;0.000&quot;)
                  End While
                  dddr.Close()   
  </code></pre>
  <p>Query 2</p>
  <pre><code>                For i As Integer = 0 To DataGridView1.RowCount - 1
                      Dim sq3 As String = &quot;select newprice from producth where pp=(SELECT coalesce(MAX(CAST(pp AS int)),'00') AS pp FROm producth WHERE (pcode = '&quot; + DataGridView1.Rows(i).Cells(1).Value + &quot;') AND (details = 'اذن اضافة مشتريات' OR details = 'اذن تسوية جردية مدينة' OR  details = 'إضافة رصيد افتتاحي') AND (pdate = ( SELECT coalesce(MAX(CAST(pdate AS date)),'01/01/1911') AS pp FROm producth WHERE (pcode = '&quot; + DataGridView1.Rows(i).Cells(1).Value + &quot;') AND (details = 'اذن اضافة مشتريات' OR details = 'اذن تسوية جردية مدينة' OR  details = 'إضافة رصيد افتتاحي') AND (pdate &lt;= '&quot; + ad + &quot;')  ))) and pcode='&quot; + DataGridView1.Rows(i).Cells(1).Value + &quot;'&quot;
                      Dim cmd3 As New SqlCommand(sq3, con)
                      Dim dr3 As SqlDataReader = cmd3.ExecuteReader
                      If dr3.Read Then
                          DataGridView1.Rows(i).Cells(5).Value = dr3(&quot;newprice&quot;)
                      End If
                      dr3.Close()
                  Next
  </code></pre>