Reputation: 5294
I have the code below running in my application and it takes approx 2 - 6 seconds to run, on average it is around 3-4 seconds. I know that the stored procedure executes way under a second so the problem must be with my VB.Net code. Is there anything I can do to this to improve performance at all?
So far I have sped it up slightly by making my stored proc return some of the HTML ready to be inserted into each element rather than having multiple IF statements within the loop, and I changed the loop to a While rather than for each which has helped slightly. I am assuming that some of the performance problems may be down to the server but I would also like to know if there is anything in my code that could be improved.
Thanks
Private Sub dbGetOpenUnassignedTickets()
' Here we need to get the results of the query
Dim Events As New DataTable()
Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("Blueprint").ToString())
Dim cmd As New SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "spGetUnassignedTickets"
cmd.Connection = conn
Using da As New SqlDataAdapter(cmd)
conn.Open()
da.Fill(Events)
conn.Close()
End Using
Dim TicketHTML As String = String.Empty
Dim counter As Integer = 0
While (counter < Events.Rows.Count - 1)
Dim strDragDrop As String
strDragDrop = "onmousedown='return DayPilotCalendar.dragStart(this.parentNode, 60*30, """ & Events.Rows(counter)("TicketID") & """, ""EE"");'"
'IF Current user <> looking at their own schedule AND not allowed to assign tickets
If (Session("UserID") <> ddlUser.SelectedValue And Session("AssignTickets") = 0) Then
strDragDrop = String.Empty
End If
TicketHTML = TicketHTML + Events.Rows(counter)("SeverityHTML")
TicketHTML = TicketHTML + "<table width='100%' " + strDragDrop + ">"
TicketHTML = TicketHTML + Events.Rows(counter)("TypeHTML")
TicketHTML = TicketHTML + Events.Rows(counter)("ProductHTML")
TicketHTML = TicketHTML + "<tr>"
TicketHTML = TicketHTML + "<td colspan='2'><b>Description</b> <br />" + Events.Rows(counter)("DescriptionHTML") + "</td>"
TicketHTML = TicketHTML + "</tr>"
TicketHTML = TicketHTML + "<tr>"
TicketHTML = TicketHTML + "<td>"
TicketHTML = TicketHTML + "<b>Logged Date:</b>"
TicketHTML = TicketHTML + "</td>"
TicketHTML = TicketHTML + "<td align='left'>"
TicketHTML = TicketHTML + Events.Rows(counter)("LogDate")
TicketHTML = TicketHTML + "</td>"
TicketHTML = TicketHTML + "</tr>"
TicketHTML = TicketHTML + "</table>"
TicketHTML = TicketHTML + "</div>"
counter = counter + 1
End While
' And now update the inner HTML of the thing we are putting the tickets into
divTickets.InnerHtml = TicketHTML
End Sub
Upvotes: 1
Views: 730
Reputation: 11912
As others have suggested a StringBuilder will give you a great speed increase (especially if you're using a lot of records).
I'd also loop the rows with For Each dr As DataRow in Events.Rows
and then say dr("TypeHtml")
instead of Events.Rows(counter)("TypeHTML")
etc...
Upvotes: 1
Reputation: 21112
I recommend you use SqlDataReader
for data retrieval and StringBuilder
for building your HTML.
You can also alter or make a new version of spGetUnassignedTickets
that only return the columns needed.
Upvotes: 5