Optimized Table Printing in ASP
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.
Outline
- Concatenating Database Output Into a String
- Eliminating Concatenation From the Loop
- Better Performance with Recordset Field References
- Improving Browser Rendering Speed Using Fixed Size Tables
- Using Minimal Formatting with the PRE tag
- Using the GetString Recordset Function
- Using the GetRows Recordset Function
- Using a Native OLE DB Provider Instead of ODBC
- Summary of Simple Table Techniques
Concatenating Database Output Into a String
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. The table being printed contains two integer columns and two short text columns, and is described fully in ASP Speed Tricks Appendix.
| simpletable1.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 "
| " & objRS("field1") & " | "" & objRS("field2") & " | "" & objRS("field3") & " | "" & objRS("field4") & " |
Set objCN = Nothing
Set objRS = Nothing
EndTime = Timer
Response.write "
processing took "&(EndTime-StartTime)&" seconds
"
%>
| 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 Loop
Concatenation 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. |
' Using multiple Response.write calls takes 1.62 seconds. |
The following example eliminates accumulative concatenation from the loop and replaces it with direct calls to Response.write.
| simpletable2.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
' 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 directly without using concatenation operator
Response.write "
| "
Response.write objRS("field1") Response.write " | "
Response.write objRS("field2") Response.write " | "
Response.write objRS("field3") Response.write " | "
Response.write objRS("field4") Response.write " |
objRS.Close
objCN.Close
Set objCN = Nothing
Set objRS = Nothing
EndTime = Timer
Response.write "
processing took "&(EndTime-StartTime)&" seconds
"
%>
| Test Results | |
|---|---|
| Records | Seconds |
| 1000 | 0.145 |
| 2000 | 0.260 |
| 10000 | 0.980 |
| 20000 | 1.950 |
For 1000 records, this method runs an incredible 23 times faster. For more records, the difference is even greater. The processing time is now roughly proportional to the number of records being printed. This property is essential for large record sets.
Better Performance with Recordset Field References
In the previous examples, the value of the field was retrieved from the Recordset object by specifying the field name directly. The Recordset's Fields collection supports this useful property to provide easy access to the fields. However, referencing the field value by using the textual field name causes a relatively slow string lookup to be performed in the Fields collection.
To avoid the string lookup, we could instead use a numeric index into the Fields collection, such as objRS(0), objRS(1), objRS(2), etc. But even better performance can be gained by saving a pointer to each field in the Recordset right after it is opened. This way, instead of looking up the field value using a string or number, direct access to the field value is obtained. When VBScript sees the pointer in the string context of the Response.write, it can quickly obtain the data from the field and convert it to a string. The next example shows how this can be done.
| simpletable3.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 objField1, objField2, objField3, objField4
' 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 Field1,Field2,Field3,Field4 FROM tblData"
' Execute the SQL query and set the implicitly created recordset
Set objRS = objCN.Execute(strsql)
' Set up field references after opening recordset
Set objField1 = objRS("Field1")
Set objField2 = objRS("Field2")
Set objField3 = objRS("Field3")
Set objField4 = objRS("Field4")
' Write out the results using the field references
Response.write "
| "
Response.write objField1 Response.write " | "
Response.write objField2 Response.write " | "
Response.write objField3 Response.write " | "
Response.write objField4 Response.write " |
objRS.Close
objCN.Close
Set objCN = Nothing
Set objRS = Nothing
EndTime = Timer
Response.write "
processing took "&(EndTime-StartTime)&" seconds
"
%>
| Test Results | |
|---|---|
| Records | Seconds |
| 1000 | 0.105 |
| 2000 | 0.190 |
| 10000 | 0.665 |
| 20000 | 1.350 |
This technique requires declaring each field as a variable, and then setting the pointers after the Recordset is opened. The need for these additional lines of code is a drawback to this technique. The benefit is a 30-40% speedup on this test. Note that the references are set using the actual names of the fields, and the references are named with "obj" prefixed to the field name. This convention is to improve maintainability and readibility.
Improving Browser Rendering Speed Using Fixed Size Tables
While running the above example on Internet Explorer for 20,000 records, one may notice that the server finishes processing within two seconds, but the page does not display until much later. On the test system, the page displayed 12 seconds after the refresh button was pressed. For those 12 seconds, the browser window remained blank.
This delay is not due to the server, but it is due to the browser rendering speed on the client machine. When a web browser receives this huge data set, it must figure out a way to display it on the user's screen. This means calculating the width of the table columns and height of its rows. In fact, the browser has to receive and process all the data before it can finalize the layout of the table. On low bandwidth connections, the problem is compounded because the user must additionally wait for the data to be transferred.
Every browser takes a different amount of time to render a given page, but there are techniques that can be used to help the browser along. The goal is to give the browser as few calculations as possible. One minor technique is to print a new line (vbCrLf) after every 256 characters or so. This seems to help older browsers in particular.
The major technique is to make the table's columns fixed width, thereby eliminating the need for the browser to calculate the table's column widths. In fact, by making the table fixed width, the browser can begin displaying the table before all the data has been received. First, set the table style to "table-layout: fixed;". Second, right after the
| simpletable4.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 objField1, objField2, objField3, objField4
' 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 Field1,Field2,Field3,Field4 FROM tblData"
' Execute the SQL query and set the implicitly created recordset
Set objRS = objCN.Execute(strsql)
' Set up field references after opening recordset
Set objField1 = objRS("Field1")
Set objField2 = objRS("Field2")
Set objField3 = objRS("Field3")
Set objField4 = objRS("Field4")
' Write out the results in a fixed size table
Response.write "
| "
Response.write objField1 Response.write " | "
Response.write objField2 Response.write " | "
Response.write objField3 Response.write " | "
Response.write objField4 Response.write " |
objRS.Close
objCN.Close
Set objCN = Nothing
Set objRS = Nothing
EndTime = Timer
Response.write "
processing took "&(EndTime-StartTime)&" seconds
"
%>
When this version of the code is refreshed, the top part of the table displays in about 3 seconds. The page is not scrollable, and the browser is still receiving and rendering the remaining data, but at least the user can begin reading the first part of table. The page completes loading in the same amount of time as before, about 12 seconds. While the actual speed is not faster, the perceived speed is significantly faster.
Note that different browsers render tables in different ways. For example, Mozilla actually renders the top part of the table before receiving the entire table, even if the table is not fixed in size. The Mozilla Layout Engine, on which Netscape 6+ is based, then resizes the columns again on the fly once all the data has been received. Rendering time is highly dependent upon browser choice, processor speed, memory speed and amount, and graphics card speed, which are aspects of the client's system. A web page programmer generally has no direct control over the client's system, but can generate HTML which requires less power to render.
Using Minimal Formatting with the PRE tag
Rendering time can be drastically improved by eliminating the table and using pre-formatted text. Along the same lines as before, the trade-off is some loss in formatting flexibility for a gain in raw speed. The
tag can be used for text that displays in a fixed-width font using the formatting of the source code, like all the code examples in this document. Because there is no text wrapping, column sizing, or variable character sizes, the browser has minimal amounts of calculations to make for rendering.To implement this, simply replace the table tags with the pre-formatted text tag, and then replace the table cell bounding tags with tabs and new lines. An additional benefit of this technique is that the raw amount of data is less because tabs and new lines are shorter than table cell bounding tags like and . (It would be possible to do better formatting by using the VBScript Space and Len functions to place text in padded, fixed character width columns, but this is beyond the scope of this article.) With 20,000 records, the code example below rendered entirely within three seconds, which is at least four times faster than the fixed-width table formatted data.
| simpletable5.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 objField1, objField2, objField3, objField4
' 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 Field1,Field2,Field3,Field4 FROM tblData"
' Execute the SQL query and set the implicitly created recordset
Set objRS = objCN.Execute(strsql)
' Set up field references after opening recordset
Set objField1 = objRS("Field1")
Set objField2 = objRS("Field2")
Set objField3 = objRS("Field3")
Set objField4 = objRS("Field4")
' Write out the results as pre-formatted text
Response.write """
Do While Not objRS.EOF
Response.write objField1
Response.write vbTab
Response.write objField2
Response.write vbTab
Response.write objField3
Response.write vbTab
Response.write objField4
Response.write vbTab
Response.write vbCrLf
objRS.MoveNext
Loop
Response.write "
objRS.Close
objCN.Close
Set objCN = Nothing
Set objRS = Nothing
EndTime = Timer
Response.write "processing took "&(EndTime-StartTime)&" seconds
"
%>
As an aside, another way to reduce raw data size of the HTML is to simply eliminate the table row and column ending tags, and This technique saves a signficant amount of space in a long query, and is also valid HTML according to the W3C specification. On the other hand, some older browsers choke if these ending tags are excluded. For newer applications in controlled settings, the end tags can be safely excluded. Removing these tags saves bandwidth and transfer time, but otherwise does not affect rendering time versus an ordinary table.
Using the GetString Recordset Function
The Recordset object has two methods that allow retrieving data quickly, without requiring any looping. GetString returns a string from the Recordset, while GetRows returns an array.
Both functions can be used to quickly copy the RecordSet data into the web server's memory and then disconnect from the database server. This can improve performance where database scalability is an issue. GetString is considered first in order to develop a fast technique for using these functions.
The code snippets below show three ways of generating the same information. The first method is the "Optimized Looping" method used in the previous example. "Optimized Looping" is defined simply as looping with field references and eschewing the concatenation operator. The second method, "Full GetString", uses a single call to GetString to return and print the entire Recordset at once. The third method, "Partial GetString" uses a loop with multiple calls to GetString that return and print a small part of the Recordset on each iteration. Shown below are the code snippets and test results. The fastest times are highlighted.
' Optimized Looping (field references, no concatenation) |
' Full GetString |
' Looping with Partial GetString (30 records per iteration) |
| Test Results | ||||
|---|---|---|---|---|
| Records | KBytes | Optimized Looping (s.) | Full GetString (s.) | Partial GetString (s.) |
| 1000 | 25 | 0.10 | 0.08 | 0.08 |
| 2000 | 52 | 0.17 | 0.13 | 0.12 |
| 10000 | 262 | 0.67 | 0.50 | 0.39 |
| 20000 | 536 | 1.25 | 2.13 | 0.74 |
KBytes is the length of the generated HTML file, which is almost entirely the data from the Recordset.
First, the bad news. GetString is a non-intuitive way to print a table of data, requiring remembering the usage and order of the five parameters. The code for GetString is also much more condensed, making it harder to read. And finally, GetString joins every row and column using a fixed format, making customized formatting of the results difficult to impossible. Therefore, the downsides of GetString are that it decreases code maintainability, and it reduces the flexibility of formatting the data in HTML.
The upside, as you might have guessed, is that GetString is about 20-40% faster than "Optimized Looping". Using a single call to GetString is faster than "Optimized Looping" up until 20,000 records, at which point this method takes a significant performance hit. This hit could be explained by the need for the server to allocate additional memory for the lengthy 536KB string. The Pentium III processor that ran these tests has a 256 KB cache, and this is possibly why the performance hit occurs after 262 KB, when the processor may be forced to go to slower system memory.
In any case, this performance hit can be avoided and greater performance achieved by issuing multiple calls to GetString, each returning a small part of the Recordset. The second parameter of GetString limits the number of rows returned. By placing such a function call within a loop, the entire Recordset can be printed. The number of records per call in this test was thirty, and setting it to one hundred increased performance slightly. The optimal setting would vary based on the dataset and system specifications. The code using GetString is shown below.
| simpletable6.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
' 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 Field1,Field2,Field3,Field4 FROM tblData"
' Execute the SQL query and set the implicitly created recordset
Set objRS = objCN.Execute(strsql)
' Write out the results using GetString in a loop
Response.write """
Do While Not objRS.EOF
Response.write objRS.GetString(2,30,vbTab,vbCrLf)
Loop
Response.write "
objRS.Close
objCN.Close
Set objCN = Nothing
Set objRS = Nothing
EndTime = Timer
Response.write "processing took "&(EndTime-StartTime)&" seconds
"
%>
Using the GetRows Recordset Function
While GetString is a very fast way to print data from a Recordset, it suffers from a loss of code maintainability and formatting flexibility. It turns out using GetRows in the same way is nearly as fast, but has the added benefit of allowing ease of code maintainance and unlimited formatting flexibility. The following example replaces GetString with GetRows, and retrieves the array into a temporary variable. The array is then printed out in a simple loop.
| simpletable7.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 RecordsArray ' To hold the Array returned by GetRows
Dim i ' A counter variable
' 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 Field1,Field2,Field3,Field4 FROM tblData"
' Execute the SQL query and set the implicitly created recordset
Set objRS = objCN.Execute(strsql)
' Write out the results using GetRows in a loop
Response.write """
Do While Not objRS.EOF
RecordsArray = objRS.GetRows(30)
' Print out the array
For i = 0 To UBound(RecordsArray, 2)
Response.write RecordsArray(0, i)
Response.write vbTab
Response.write RecordsArray(1, i)
Response.write vbTab
Response.write RecordsArray(2, i)
Response.write vbTab
Response.write RecordsArray(3, i)
Response.write vbTab
Response.write vbCrLf
Next
Loop
Response.write "
objRS.Close
objCN.Close
Set objCN = Nothing
Set objRS = Nothing
EndTime = Timer
Response.write "processing took "&(EndTime-StartTime)&" seconds
"
%>
| Test Results of the Three Major Techniques | ||||
|---|---|---|---|---|
| Records | Optimized Looping (s.) | Partial GetString (s.) | Partial GetRows (s.) | |
| 1000 | 0.10 | 0.08 | 0.08 | |
| 2000 | 0.17 | 0.12 | 0.12 | |
| 10000 | 0.67 | 0.39 | 0.43 | |
| 20000 | 1.25 | 0.74 | 0.82 | |
The test results show that the GetRows technique is only about 10% slower than the fastest technique of using GetString. On the other hand, GetRows has the advantage that the code is easier to read and it allows any kind of additional formatting of the returned data. This benefit is achieved without the additional lines of code required by "Optimized Looping" for declaring and setting field references.
Using a Native OLE DB Provider Instead of ODBC
The examples above use the Microsoft Access Driver for ODBC. The ODBC driver is generally slower than using a native OLE DB driver. It also has some differences in capabilities and SQL syntax, which are described in the documentation. In order to use the OLE DB driver, you need to specify the OLE DB Provider for your database in your connection string. The connection string syntax is different for each provider, making it hard to remember. To create a connection string the easy way, follow these steps:
- Use Notepad and save an empty file to your desktop called "mydsn.udl".
(This file may be called anything but must have a .udl extension.) - Double click on the file and use the panels to configure your data source.
- Use Notepad to open the "mydsn.udl" file, and copy the connection string to ASP.
- Instead of step 3, use "File Name=c:\windows\desktop\mydsn.udl" as your connection string.
Using a native OLE DB Provider is generally faster than going through ODBC.
ADO <--> OLE DB Provider for ODBC <--> ODBC <--> Your Database
ADO <--> Native OLE DB Provider <--> Your Database
The following test uses the previous "Partial GetRows" example and simply replaces "DSN=datasource" with a Native OLE DB connection string for the Jet 4.0 OLE DB Provider. This connects to the identical database file test.mdb. As you can see, the native OLE DB Provider is about 10-20% faster for the simple select query being tested.
| simpletable8.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 RecordsArray ' To hold the Array returned by GetRows
Dim i ' A counter variable
' Create a connection object
Set objCN = Server.CreateObject("ADODB.Connection")
' Connect to the data source using the native OLE DB Provider for Jet
objCN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"&_
"Data Source=C:\Inetpub\wwwroot\data\test.mdb;"&_
"Persist Security Info=False"
objCN.Open
' Prepare a SQL query string
strsql = "SELECT Field1,Field2,Field3,Field4 FROM tblData"
' Execute the SQL query and set the implicitly created recordset
Set objRS = objCN.Execute(strsql)
' Write out the results using GetRows in a loop
Response.write """
Do While Not objRS.EOF
RecordsArray = objRS.GetRows(30)
For i = 0 To UBound(RecordsArray, 2)
Response.write RecordsArray(0, i)
Response.write vbTab
Response.write RecordsArray(1, i)
Response.write vbTab
Response.write RecordsArray(2, i)
Response.write vbTab
Response.write RecordsArray(3, i)
Response.write vbTab
Response.write vbCrLf
Next
Loop
Response.write "
objRS.Close
objCN.Close
Set objCN = Nothing
Set objRS = Nothing
EndTime = Timer
Response.write "processing took "&(EndTime-StartTime)&" seconds
"
%>
| Test Results | ||||
|---|---|---|---|---|
| Records | ODBC (s.) | Jet OLE DB (s.) | ||
| 1000 | 0.08 | 0.07 | ||
| 2000 | 0.12 | 0.10 | ||
| 10000 | 0.43 | 0.35 | ||
| 20000 | 0.82 | 0.65 | ||
Summary of Simple Table Techniques
The seven techniques described have reduced the server processing time of the ASP page displaying 1000 records from 3.5 seconds down to 0.07 seconds. The client-side rendering time was also drastically reduced. Each technique increases performance at the cost of coding time, formatting flexibility, and code maintainability. The last method, "Partial GetRows", may cost the least for the amount of performance benefit it yields, because it is simple to code and maintain and has unlimited formatting flexibility. The seven techniques are listed below.
- Replace the '&' concatenation operator with liberal use of 'Response.write' within the loop.
- Set up pointers or references to the Recordset's fields and use these for printing the record data values.
- Make the table and table columns fixed-width. Break lines with 'vbCrLf' after every 256 characters or so.
- Eliminate the table completely, and instead use pre-formatted text.
- Use GetString in a Loop
- Use GetRows in a Loop
- Use a Native OLE DB provider instead of ODBC
