S
Skypea
Guest
Hi! Im trying to load data from sql server 2005 to dgv. When I display top 100 data it show quickly, but when I add where in sql by 2 date, and use parameters.addwithvalue it loaded slowly. How to improve the performace?
this is my code:
Sub LoadToDgv()
Call openConn()
Dim LimitData As Integer
Dim sb As StringBuilder
sb = New StringBuilder
sb = getSql()
Dim sqlstring As String
sqlstring = sb.ToString
CMD = New SqlCommand(sqlstring, CONN)
If Limit.Text <> "" Then
LimitData = Val(Limit.Text)
CMD.Parameters.AddWithValue("@LimitData", LimitData)
End If
CMD.Parameters.AddWithValue("@dtfrom", DTFrom.Value)
CMD.Parameters.AddWithValue("@dtto", DTTo.Value)
DA = New SqlDataAdapter(CMD)
Dim dt As New DataTable
DA.Fill(dt)
DGVTransaksi.DataSource = dt
CONN.Close()
End Sub
Function getSql() As StringBuilder
Dim sb As StringBuilder
sb = New StringBuilder
sb.Append("select ")
If Limit.Text <> "" Then
sb.Append("top (@LimitData) ")
End If
sb.Append("trans.id, trans.entry_date, trans.destination, ")
sb.Append("trans.price, trans.module_id, trans.status, trans.status_date, ")
sb.Append("inbox.message from inbox inner join trans on ")
sb.Append("trans.id=inbox.trans_id ")
sb.Append("where (trans.status_date >= @dtfrom and trans.status_date <= @dtto) ")
sb.Append("order by trans.status_date desc ")
Return sb
End Function
Continue reading...
this is my code:
Sub LoadToDgv()
Call openConn()
Dim LimitData As Integer
Dim sb As StringBuilder
sb = New StringBuilder
sb = getSql()
Dim sqlstring As String
sqlstring = sb.ToString
CMD = New SqlCommand(sqlstring, CONN)
If Limit.Text <> "" Then
LimitData = Val(Limit.Text)
CMD.Parameters.AddWithValue("@LimitData", LimitData)
End If
CMD.Parameters.AddWithValue("@dtfrom", DTFrom.Value)
CMD.Parameters.AddWithValue("@dtto", DTTo.Value)
DA = New SqlDataAdapter(CMD)
Dim dt As New DataTable
DA.Fill(dt)
DGVTransaksi.DataSource = dt
CONN.Close()
End Sub
Function getSql() As StringBuilder
Dim sb As StringBuilder
sb = New StringBuilder
sb.Append("select ")
If Limit.Text <> "" Then
sb.Append("top (@LimitData) ")
End If
sb.Append("trans.id, trans.entry_date, trans.destination, ")
sb.Append("trans.price, trans.module_id, trans.status, trans.status_date, ")
sb.Append("inbox.message from inbox inner join trans on ")
sb.Append("trans.id=inbox.trans_id ")
sb.Append("where (trans.status_date >= @dtfrom and trans.status_date <= @dtto) ")
sb.Append("order by trans.status_date desc ")
Return sb
End Function
Continue reading...