Microsoft’s ASP technology enables beginners to write dynamic web pages with little effort.The ADO object model hides the complexity of obtaining data from the database. However, hiding complexity under a simple interface also allows unsuspecting programmers to write wildly inefficient code. Consider the common task of querying the database and displaying the results in an HTML table.
One of the slowest methods is to loop through the recordset, and concatenate each row into a string. Once the loop is complete, the string is written to the response. Many novices may apply this technique due to its logical simplicity, or by following the bad example of others. However, for anything but very small data sets, this technique is highly innefficient. The next code example shows how this technique might be used.
SIMPLETABLEExample.ASP
========================
<%@ Language=VBScript %>
<% Option Explicit %>
Dim StartTime, EndTime
StartTime = Timer
Dim objCN ’ ADO Connection object
Dim objRS ’ ADO Recordset object
Dim strsql ’ SQL query string
Dim strTemp ’ a temporary string
’ Create a connection object
Set objCN = Server.CreateObject("ADODB.Connection")
’ Connect to the data source
objCN.ConnectionString = "DSN=datasource"
objCN.Open
’ Prepare a SQL query string
strsql = "SELECT * FROM tblData"
’ Execute the SQL query and set the implicitly created recordset
Set objRS = objCN.Execute(strsql)
’ Write out the results in a table by concatenating into a string
Response.write "<table>"
Do While Not objRS.EOF
strTemp = strTemp & "<tr><td>" & objRS("field1") & "</td>"
strTemp = strTemp & "<td>" & objRS("field2") & "</td>"
strTemp = strTemp & "<td>" & objRS("field3") & "</td>"
strTemp = strTemp & "<td>" & objRS("field4") & "</td></tr>"
objRS.MoveNext
Loop
Response.write strTemp
Response.write "</table>"
Set objCN = Nothing
Set objRS = Nothing
EndTime = Timer
Response.write "<p>processing took "&(EndTime-StartTime)&" seconds<p> "
Test Results Records Time
============= =============
1000 3.5 seconds
2000 18.4 seconds
10000 7.5 minutes (est.)
20000 30 minutes (est.)
The server processing time to display 1000 records from the table is about 3.5 seconds.Doubling the number of records to 2000 more than quadruples the time to 18.4 seconds. The script times out for the other tests, but some time estimates are given. In the code, the ’&’ concatenation operator is used heavily within the loop.
Concatenation in VBScript requires new memory to be allocated and the entire string to be copied. If the concatenation is accumulating in a single string, then an increasingly long string must be copied on each iteration. This is why the time increases as the square of the number of records. Therefore, the first optimization technique is to avoid accumulating the database results into a string.
Eliminating Concatenation From the LoopConcatenation may be removed easily by using Response.write directly in the loop. (In ASP.Net, the StringBuilder class can be used for creating long strings, but Response.write is fastest.) By eliminating accumulation, the processing time becomes proportional to the number of records being printed, rather than being exponential.
Each use of the concatenation operator results in unnecessary memory copying. With larger recordsets or high-load servers, this time can become significant. Therefore, instead of concatenating, programmers should simply write out the data with liberal use of Response.write. The code snippet below shows that even a few non-accumulative concatenations cause a noticeable time difference when run repeatedly.
’ Using concatenation in a loop takes 1.93 seconds.
For i = 0 To 500000
Response.write vbTab & "foo" & vbCrLf
Next
’ Using multiple Response.write calls takes
1.62 seconds.For i = 0 To 500000
Response.write vbTab
Response.write "foo"
Response.write vbCrLf
Next
