Optimized Heirarchical Table Printing in ASP

A common task of ASP pages is to display heirarchical reports or tables. This section generalizes one aspect of the problem and then provides techniques for increasing performance. Finally, test results of different methods and Providers is shown, and an optimal technique presented.

Outline

• Introduction to Heirarchical Tables
• Avoiding JOIN and SHAPE to Produce Heirarchical Tables
• Creating a New Recordset on Each Iteration
• Using a Prepared Connection and Recordset Object
• Using a Prepared Command Object to Obtain a Recordset
• Comparision of Recordset Opening Methods in ODBC, Jet, and SQL Server
• Provider-Specific Results of Recordset Opening Tests
• General Results of Recordset Opening Tests
• Using a Parameterized Command Object Without a Recordset
• Using the SQL UNION Operator
• Summary of Heirarchical Table Techniques

Introduction to Heirarchical Tables

A heirarchical table is defined here as a table that requires at least one sub-query to be performed for each record of a primary query. For example, suppose we have a table of public companies and a table of the officers of the public companies. If we wanted to display a table of public companies and their officers, we would have to display it in a heirarchical report. An example of such a table is below.

The algorithm for printing this table involves querying the list of public companies, and then querying the list of officers for each public company. The companies may be thought of as the primary records, and the officers the secondary records. Thus, the table has a primary-secondary heirarchy, or a set of nested queries. In practice, there may be more than two levels, and each level could have multiple queries.

Avoiding JOIN and SHAPE to Produce Heirarchical Tables

Technically, it is possible to use a single query to retrieve all the necessary information for both the companies and officers. This can be accomplished simply by joining the two tables together. The drawback of this method is that the company information is repeated on each row of the record set. If the company information is substantial, or the number of officers is large, this can have a significant performance hit. The perfomance hit will become prohibitive if there are multiple sub queries to be performed, such as pulling a list of the company's board members as well as officers.

Another method to avoid is the Microsoft SQL SHAPE clause. The SHAPE clause allows nesting sub-queries into a single query that returns a multi-dimensional Recordset. This method is to be avoided because it can make the initial query huge, awkward, and unmaintainable. Secondly, the SHAPE clause is not part of the SQL99 standard and is not widely supported by database engines. Finally, the SHAPE syntax is relatively advanced and obscure to beginners.

Therefore, it is most flexible and easiest logically to perform the main query, and then perform sub queries for each row of the main query. As before, the methods of doing this are described from slowest to fastest. These techniques build upon the previous techniques for simple tables. In the following examples, rather than printing out the multiple records of a sub-query, the sub-query will be a dummy query that returns a single result. This is because the simple table techniques have already shown ways to optimize printing the records. The intent is to optimize the method of issuing the sub-query from ADO, not to optimize the sub-query itself.

Creating a New Recordset on Each Iteration

This first example shows a straight-forward method of performing the sub-query. The example builds on the method of using GetRows in a loop, but reverts back to the ODBC driver. A new recordset is created and initialized, used to perform the sub-query, and then destroyed. All these steps are performed right from within the loop.

<%@ Language=VBScript %>
<% Option Explicit %>
<%
Dim StartTime, EndTime

StartTime = Timer

Dim objCN ' ADO Connection object
Dim objRS, objRS2 ' ADO Recordset objects
Dim strsql ' SQL query string
Dim RecordsArray, i

' 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 using GetRows in a loop
Response.write "<pre>"
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
        
        ' Issue a dummy query and write out the result
        Set objRS2 = Server.CreateObject("ADODB.RecordSet")
        strsql = "SELECT COUNT(*) FROM tblData WHERE Field1="&RecordsArray(0, i)
        objRS2.Open strsql, "DSN=datasource", adOpenForwardOnly, adLockReadOnly
        Response.write "Dummy query result="
        Response.write objRS2(0)
        objRS2.Close
        Set objRS2 = Nothing
        Response.write vbCrLf
    Next
Loop
Response.write "</pre>"

objRS.Close
objCN.Close
Set objCN = Nothing
Set objRS = Nothing

EndTime = Timer
Response.write "<p>processing took "&(EndTime-StartTime)&" seconds<p>&nbsp;"
%>

Simply adding a single query in the loop causes the time to jump from 0.08 seconds to over 13 seconds. One might think that the sub-query is a very slow query, but this is not the case. There are two non-obvious problems with this code, which are discussed in the following sections.

Using a Prepared Connection and Recordset Object

A minor problem is that a new Recordset object is being created on each iteration. This can be fixed easily by initializing the Recordset once before the loop, and destroying it after the loop

The first major problem is that a new database connection is being implicitly created and opened on each iteration. This automatic creation is provided by ADO as a programming convenience, but it sacrifices performance when used inappropriately. Once this problem is realized, the solution is obvious. As the following example shows, a Connection object is explicitly created and initialized before the loop. The Recordset performing the sub-query is then set to use this already opened Connection object. In fact, both the primary and secondary recordsets are set to use the same Connection object. The code follows.

<%@ 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 objRS2 ' Another ADO Recordset object
Dim RecordsArray, i

' Create a connection object
Set objCN = Server.CreateObject("ADODB.Connection")

' Connect to the data source
objCN.ConnectionString = "DSN=datasource"
objCN.Open

' Create the a recordset object, and initialize it
Set objRS = Server.CreateObject("ADODB.RecordSet")
With objRS
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .CursorLocation = adUseServer
    Set .ActiveConnection = objCN
End With
' Create the second recordset object, and initialize it
Set objRS2 = Server.CreateObject("ADODB.RecordSet")
With objRS2
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .CursorLocation = adUseServer
    Set .ActiveConnection = objCN
End With

' Execute the SQL query
objRS.Open "SELECT Field1,Field2,Field3,Field4 FROM tblData"

' Write out the results using GetRows in a loop
Response.write "<pre>"
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
        
        ' Use the pre-prepared Recordset object to issue the dummy query
        strsql = "SELECT COUNT(*) FROM tblData WHERE Field1="&RecordsArray(0, i)
        objRS2.Open strsql
        Response.write "Dummy query result="
        Response.write objRS2(0)
        objRS2.Close
        Response.write vbCrLf
    Next
Loop
Response.write "</pre>"

objRS.Close
objCN.Close
Set objCN = Nothing
Set objRS = Nothing
Set objRS2 = Nothing

EndTime = Timer
Response.write "<p>processing took "&(EndTime-StartTime)&" seconds<p>&nbsp;"
%>

This simple change reduces the processing time down to about four seconds, for a three-fold improvement. Along with using an already opened Connection object, the Recordset properties are set for highest performance, i.e. using a forward-only cursor, read-only lock, and a server-side cursor. (A further discussion of cursors is outside the scope of this article.)

Using a Prepared Command Object to Obtain a Recordset

The second major problem is that the "objRS2.Open" call is implicitly creating a Command object on each iteration. This may be eliminated by setting up a parameterized Command object in advance. To create a parameterized query, a question mark '?' is used in place of each parameter in the SQL statement. Then, the Command object's CreateParameter and Append functions set up the parameters. By setting the Command object's prepared property to True, the query's execution path only needs to be calculated once by the database engine. The dummy query in this test has a single input parameter in its WHERE clause, as shown in the next example.

<%@ 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 objRS2 ' Another ADO Recordset object
Dim objCmd ' ADO Command object
Dim RecordsArray, i

' Create a connection object
Set objCN = Server.CreateObject("ADODB.Connection")

' Connect to the data source
objCN.ConnectionString = "DSN=datasource"
objCN.Open

' Create the a recordset object, and initialize it
Set objRS = Server.CreateObject("ADODB.RecordSet")
With objRS
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .ActiveConnection = objCN
    .CursorLocation = adUseServer
    .Source = "SELECT Field1,Field2,Field3,Field4 FROM tblData"
End With
' Create the second recordset object, and initialize it
Set objRS2 = Server.CreateObject("ADODB.RecordSet")
With objRS2
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .CursorLocation = adUseServer
End With
' Create command object
Set objCmd = Server.CreateObject("ADODB.Command")
With objCmd
    .ActiveConnection = objCN
    .CommandType = adCmdText
    .Prepared = True
    .CommandText = "SELECT COUNT(*) FROM tblData WHERE Field1=?"
End With
' Create unnamed Parameter and append it to Parameters collection
objCmd.Parameters.Append _
    objCmd.CreateParameter(,adInteger,adParamInput,4)

' Execute the SQL query
objRS.Open


' Write out the results using GetRows in a loop
Response.write "<pre>"
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
        
        ' Use prepared Command and Recordset to issue dummy query
        ' Set the parameter for this iteration
        objCmd(0) = RecordsArray(0, i)
        ' Run the prepared query
        objRS2.Open objCmd
        Response.write "Dummy query result="
        Response.write objRS2(0)
        objRS2.Close
        Response.write vbCrLf
    Next
Loop
Response.write "</pre>"

objRS.Close
objCN.Close
Set objCN = Nothing
Set objRS = Nothing
Set objRS2 = Nothing
Set objCmd = Nothing

EndTime = Timer
Response.write "<p>processing took "&(EndTime-StartTime)&" seconds<p>&nbsp;"
%>

Being able to set the Prepared property of the Command object is the step that makes the nearly four-fold time difference. If set to False in the above code, the time jumps back to over four seconds.

Comparision of Recordset Opening Methods in ODBC, Jet, and SQL Server

The above example opens the Recordset using a prepared Command object. In reality, ADO offers a multitude of methods for opening the Recordset. The purpose of these tests is to discover the optimal method of performing the database query from ASP. What follows is a comparison of seven such methods, tested in the context of the previous example. These methods use various combinations of the ADO Recordset, Connection, and Command objects, and stored versus inline queries. Each method is tested on three different OLE DB Providers: Jet, ODBC, and SQL Server.

Each test is further run with either one connection or two connections. The one connection test uses the same Connection for both the primary query and the sub-query. The two connection test uses a different Connection object for the sub-query. The source code for the tests is listed next, followed by tables of the test case code snippets and execution times.

<%@ Language=VBScript %>
<% Option Explicit %>
<%
Dim Method, DataSource, ConnectionString, NumberOfConnections

' Set up test case using the DataSource, Method, and NumberOfConnections
' Uncomment one at a time
'DataSource = "ODBC"
'DataSource = "Jet"
DataSource = "SQL Server"

'NumberOfConnections = 1
NumberOfConnections = 2

'Method = 1
'Method = 2
'Method = 3
'Method = 4
'Method = 5
'Method = 6
Method = 7

Dim StartTime, EndTime
Dim objCN ' ADO Connection object
Dim objRS ' ADO Recordset object
Dim strsql ' SQL query string
Dim objRS2 ' Another ADO Recordset object
Dim objCmd ' ADO Command object
Dim RecordsArray, i
Dim objCN2

' Start timer
StartTime = Timer

' Create a connection object
Set objCN = Server.CreateObject("ADODB.Connection")
Set objCN2 = Server.CreateObject("ADODB.Connection")

' Connect to the selected data source
If DataSource = "ODBC" Then
    ConnectionString = "DSN=datasource"
End If
If DataSource = "Jet" Then
    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"&_
        "Data Source=C:\Inetpub\wwwroot\data\test.mdb;"&_
        "Persist Security Info=False"
End If
If DataSource = "SQL Server" Then
    ConnectionString = "Provider=SQLOLEDB.1;"&_
        "User ID=sa;Data Source=localhost;"&_
        "Initial Catalog=sqltest;Password=;"
End If

' Initialize and open Connections
objCN.ConnectionString = ConnectionString
objCN.Open

If NumberOfConnections = 2 Then
    objCN2.ConnectionString = ConnectionString
    objCN2.Open
End If
If NumberOfConnections = 1 Then
    Set objCN2 = objCN
End If

' Create the a recordset object, and initialize it
Set objRS = Server.CreateObject("ADODB.RecordSet")
With objRS
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .ActiveConnection = objCN
    .CursorLocation = adUseServer
    .Source = "SELECT Field1,Field2,Field3,Field4 FROM tblData"
End With
' Create the second recordset object, and initialize it
Set objRS2 = Server.CreateObject("ADODB.RecordSet")
With objRS2
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .ActiveConnection = objCN2
    .CursorLocation = adUseServer
End With
' Create command object
Set objCmd = Server.CreateObject("ADODB.Command")
With objCmd
    .ActiveConnection = objCN2
    .Prepared = True
End With
If Method = 6 Then
    objCmd.CommandType = adCmdText
    objCmd.CommandText = "SELECT COUNT(*) FROM tblData WHERE Field1=?"
End If
If Method = 7 Then
    objCmd.CommandType = adCmdStoredProc
    objCmd.CommandText = "dummyquery"
End If

' Create Parameter object
objCmd.Parameters.Append _
    objCmd.CreateParameter(,adInteger,adParamInput,4)

' Execute the SQL query
objRS.Open

' Write out the results using GetRows in a loop
Response.write "<pre>"
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

        Response.write "Dummy query result="
        If Method = 1 Then
            strsql = "SELECT COUNT(*) FROM tblData WHERE Field1="&RecordsArray(0, i)
            objRS2.Open strsql
        End If
        If Method = 2 Then
            strsql = "SELECT COUNT(*) FROM tblData WHERE Field1="&RecordsArray(0, i)
            Set objRS2 = objCN2.Execute(strsql)
        End If
        If Method = 3 Then
            strsql = "EXECUTE dummyquery "&RecordsArray(0, i)
            Set objRS2 = objCN.Execute(strsql)
        End If
        If Method = 4 Then
            strsql = "{call dummyquery('"&RecordsArray(0, i)&"')}"
            Set objRS2 = objCN2.Execute(strsql)
        End If
        If Method = 5 Then
            objCN.dummyquery RecordsArray(0, i), objRS2
        End If
        If Method = 6 Or method = 7 Then
            objCmd(0) = RecordsArray(0, i)
            objRS2.Open objCmd
        End If
        Response.write objRS2(0)
        objRS2.Close
        Response.write vbCrLf
        
    Next
Loop
Response.write "</pre>"

objRS.Close
Set objRS = Nothing
Set objRS2 = Nothing
Set objCmd = Nothing
objCN.Close
Set objCN = Nothing
If NumberOfConnections = 2 Then
    objCN2.Close
    Set objCN2 = Nothing
End If

EndTime = Timer
Response.write "<p>processing took "&(EndTime-StartTime)&" seconds<p>&nbsp;"
%>

While ...
	strsql = "SELECT COUNT(*) FROM tblData WHERE Field1="&_
		RecordsArray(0, i)
	objRS2.Open strsql
Loop

While ...
	strsql = "SELECT COUNT(*) FROM tblData WHERE Field1="&_
		RecordsArray(0, i)
	Set objRS2 = objCN2.Execute(strsql)
Loop

While ...
	strsql = "EXECUTE dummyquery " & RecordsArray(0, i)
	Set objRS2 = objCN.Execute(strsql)
Loop

While ...
	strsql = "{call dummyquery('"&RecordsArray(0, i)&"')}"
	Set objRS2 = objCN2.Execute(strsql)
Loop

While ...
	objCN.dummyquery RecordsArray(0, i), objRS2
Loop

objCmd.Prepared = True
objCmd.CommandType = adCmdText
objCmd.CommandText = "SELECT COUNT(*) FROM tblData WHERE Field1=?"
While ...
	objCmd(0) = RecordsArray(0, i)
	objRS2.Open objCmd
Loop

objCmd.Prepared = True
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "dummyquery"
While ...
	objCmd(0) = RecordsArray(0, i)
	objRS2.Open objCmd
Loop

Provider-Specific Results of Recordset Opening Tests

With the ODBC Provider using one connection or two connections made no discernable difference in execution time. The first five methods had execution times of 4.3 to 5.7 seconds, with the first method of using a simple Recordset.Open being the fastest. The last two methods, which use a prepared Command object, both had an execution time of 1.1 seconds. This was the best result out of all the tests performed.

The Jet Provider was able to handle all the methods except the ODBC syntax stored procedure call. In 4 out of 6 working methods, the Provider performed about 10% slower when using two connections versus using one connection, and on the other two, there was no difference. In the first five methods, Jet had an execution time of 3.2 to 3.7 seconds. When using the Prepared Command object, the execution time fell to 1.7 to 2.1 seconds.

The SQL Server Provider executed in 2.1 seconds for the simple Recordset.Open method, but only after running the query numerous times. This behavior may reflect SQL Server's self-tuning mechanisms. For all the other methods, SQL Server performance was between 5.2 and 5.6 seconds, with a few interesting exceptions. In methods Two, Four, Six and Seven, using a different connection to perform the sub-query reduced the execution time down to 1.2 to 1.4 seconds.

General Results of Recordset Opening Tests

From these test results alone, no general statement comparing the performance of the three Providers can be made, because the results were mixed. Using two connections made no difference for ODBC, was slightly slower for Jet, and was remarkably faster for SQL Server.

There was no significant difference between using a stored query versus using an inline query defined in ASP on any of the tests. However, these tests measured single-page execution time of a simple query on an otherwise idle server. In other words, both the query compilation cost and the number of compilations being performed are miniscule. Performance undoubtedly would be different in a high load environment or with more complex queries.

The general conclusion from these tests is that using a prepared Command object is the fastest way to perform a sub-query in a heirarchical report. This method is approximately four times faster than any of the other methods. To gain this benefit with SQL Server, a second connection object must be used to perform the sub-query. The next section covers a SQL Server-specific optimization for even greater performance.

Using a Parameterized Command Object Without a Recordset

The dummy sub-query only returns a single count, meaning that a full Recordset is not really needed. In fact, it is common for secondary queries in heirarchical tables to either not return any data (such as an update, insert, or delete), or to only return a few pieces of information (such as a count, average, or single row.) In these circumstances, it is possible to optimize the speed even further by eliminating the Recordset.

The Command object supports both input and output parameters. Output parameters are not supported by ODBC or Jet, but will work under SQL Server and other major DBMS. The dummy query will have one input parameter for the criteria of the WHERE clause, and one output parameter to return the count. The parameterized query is then executed with the special adExecuteNoRecords option, because no Recordset is needed. If no output parameters are needed, the adExecuteNoRecords option can also be used to realize performance benefits with ODBC or Jet.

In the example, the SQL Server query string is used in lieu of explicit Connection objects for both the main query and the sub-query. This is so that two Connections are implicitly opened for each object. As shown in the previous section, SQL Server performance suffers when the primary query and sub-query are executed on the same Connection.

<%@ Language=VBScript %>
<% Option Explicit %>
<%
Dim StartTime, EndTime

StartTime = Timer

Dim objRS ' ADO Recordset object
Dim objCmd ' ADO Command object
Dim RecordsArray, i
Dim strConnectionString

strConnectionString = "Provider=SQLOLEDB.1;"&_
        "User ID=sa;Password=;"&_
        "Initial Catalog=sqltest;Data Source=localhost;"

' Create the a recordset object, and initialize it
Set objRS = Server.CreateObject("ADODB.RecordSet")
With objRS
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .ActiveConnection = strConnectionString
    .CursorLocation = adUseServer
    .Source = "SELECT Field1,Field2,Field3,Field4 FROM tblData"
End With
' Create command object
Set objCmd = Server.CreateObject("ADODB.Command")
With objCmd
    .ActiveConnection = strConnectionString
    .Prepared = True
    .CommandType = adCmdText
    .CommandText = "SELECT ? = COUNT(*) FROM tblData WHERE Field1=?"
End With
' Append the parameter to the Command object's parameters collection
objCmd.Parameters.Append _
    objCmd.CreateParameter(,adInteger,adParamOutput,4)
objCmd.Parameters.Append _
    objCmd.CreateParameter(,adInteger,adParamInput,4)

' Execute the SQL query
objRS.Open

' Write out the results using GetRows in a loop
Response.write "<pre>"
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

        ' Use pre-prepared Command object without Recordset
        objCmd(1) = RecordsArray(0, i)
        objCmd.Execute ,,adExecuteNoRecords
        Response.write "Dummy query result="
        Response.write objCmd(0)
        Response.write vbCrLf

    Next
Loop
Response.write "</pre>"

objRS.Close
Set objRS = Nothing
Set objCmd = Nothing

Response.write "</table>"


EndTime = Timer
Response.write "<p>processing took "&(EndTime-StartTime)&" seconds<p>&nbsp;"
%>

By parameterizing the query and using the adExecuteNoRecords option, the speed of this test query roughly doubles, and it runs in an impressive 0.65 seconds. If the same Connection object is used for both queries, the running time jumps to over five seconds.

Using the SQL UNION Operator

The SQL UNION operator combines the results of two or more queries into a single result set. In the final result set, duplicate rows are returned only once. If the "UNION ALL" operator is used, then duplicate rows appear as many times as they occur in the original queries. In the Venn diagrams below, the shaded areas indicate that both "UNION" and "UNION ALL" return all rows from both sets. The darker middle section in "UNION ALL" indicates that rows appearing in both sets are returned as two rows.

(1,2,3,4)   UNION   (3,4,5,6) = (1,2,3,4,5,6)
(1,2,3,4) UNION ALL (3,4,5,6) = (1,2,3,4,3,4,5,6)

When using the UNION operator, the number and order of columns must be identical in all the queries and the data types of each column must be compatible. For example, the results of three queries could be combined if each returned four columns with data types int, varchar, datetime, and bit, in that order. The names, data types, and order of the columns are defined by the first query of the union.

With the UNION operator, it is possible to request all the data needed for the heirarchical table in a single query. Because the number of columns is constant, the overhead of a cross-product producing JOIN is avoided. The first query requests all the primary records, and each subsequent query returns secondary records. A unique user-defined number can be included in the results of each query to distinguish between the records.

In order to be more realistic, the example uses the free Northwind Sample database from Microsoft. The example lists Orders and Order Details, which are analagous to Companies and Officers. The example compares the method of using a prepared Command object with the method of using a UNION ALL query. The prepared Command object method is straightforward and was described earlier.

In the UNION ALL query, the first query selects Order ID, Order Date, and Company Name from the Orders table. Also, the number "1" is included with each row. The second query selects Order ID, Product Name, and Unit Price from the Order Details table. The number "2" is included to indicate that this is a secondary record. The order and type of the columns is identical, and this is enforced by explicitly converting Order Date and Unit Price to strings. UNION ALL is used instead of UNION because we want to include all duplicates of records.

The Orders table contains 830 records and the Order Details table contains 2,155 records. Every order is printed by the example. Since each order and detail is printed on a separate line, the final report contains 2,985 lines. The script runs both methods and prints the execution times. The source code and test results are below.

<%@ Language=VBScript %>
<% Option Explicit %>
<%

Dim objCN
Dim strsql, objRS, objRS2
Dim objCmd
Dim RecordsArray1, i
Dim RecordsArray2, j
Dim S, E

' Connect to the database
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objCN = Server.CreateObject("AdoDB.Connection")
objCN.ConnectionString =  "Provider=Microsoft.Jet.OLEDB.4.0;"&_
    "Data Source=C:\Inetpub\wwwroot\data\Northwind.mdb;"&_
    "Persist Security Info=False"
objCN.Open

' Create the second recordset object, and initialize it
Set objRS2 = Server.CreateObject("ADODB.RecordSet")
With objRS2
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .CursorLocation = adUseServer
End With

' Create a parameterized command object
Set objCmd = Server.CreateObject("ADODB.Command")
With objCmd
    .ActiveConnection = objCN
    .CommandType = adCmdText
    .Prepared = True
    .CommandText = _
    " SELECT ProductName, [Order Details].UnitPrice "&_
    " FROM [Order Details] "&_
    " INNER JOIN Products ON [Order Details].ProductID=Products.ProductID"&_
    " WHERE [Order Details].OrderID=?"
End With
' Create unnamed Parameter and append it to Parameters collection
objCmd.Parameters.Append _
    objCmd.CreateParameter(,adInteger,adParamInput,4)


S = Timer

' Perform ordinary select query.  Use the parameterized command
' object to perform the nested sub-query.
strsql = "SELECT OrderID, CompanyName, OrderDate"&_
    " FROM Orders "&_
    " INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID "&_
    " ORDER BY OrderID"
objRS.Open strsql,objCN,adOpenForwardOnly,adLockReadOnly,adCmdText

response.write "<pre>"
Do While Not objRS.EOF
    RecordsArray1 = objRS.GetRows(30)
    
    For i = 0 To UBound(RecordsArray1, 2)
        ' Print order information
        Response.write "<b>Order #"
        Response.write RecordsArray1(0, i)
        Response.write " from "
        Response.write RecordsArray1(1, i)
        Response.write " on "
        Response.write RecordsArray1(2, i)
        Response.write "</b>"
        Response.write vbCrLf

        ' Print order details
        objCmd(0) = RecordsArray1(0, i)
        objRS2.Open objCmd
        Do While Not objRS2.EOF
            RecordsArray2 = objRS2.GetRows(30)
            For j = 0 To UBound(RecordsArray2, 2)
                ' Print product name and price
                Response.write vbTab
                Response.write RecordsArray2(0,j)
                Response.write " "
                Response.write FormatCurrency(RecordsArray2(1,j))
                Response.write vbCrLf
            Next
        Loop
        objRS2.Close
    Next
    
Loop
objRS.Close
response.write "</pre>"
E = Timer

Response.write "<p>time: "&(E - S)


S = Timer

' Perform UNION ALL Query
strsql = _
"SELECT 1 AS RecordType, OrderID, CompanyName, CStr(OrderDate) "&_
    " FROM Orders "&_
    " INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID "&_
" UNION ALL "&_
" SELECT 2 AS RecordType, [Order Details].OrderID, "&_
        " ProductName, CStr([Order Details].UnitPrice) "&_
    " FROM ([Order Details] "&_
    " INNER JOIN [Orders] ON [Order Details].OrderID=[Orders].OrderID)"&_
    " INNER JOIN Products ON [Order Details].ProductID=Products.ProductID"&_
    " ORDER BY OrderID, RecordType"
objRS.Open strsql,objCN,adOpenForwardOnly,adLockReadOnly,adCmdText

response.write "<pre>"
Do While Not objRS.EOF
    RecordsArray1 = objRS.GetRows(30)
    
    For i = 0 To UBound(RecordsArray1, 2)
        ' Print differently depending on row type
        Select Case RecordsArray1(0, i)
            Case 1
            ' Print order information
            Response.write "<b>Order #"
            Response.write RecordsArray1(1, i)
            Response.write " from "
            Response.write RecordsArray1(2, i)
            Response.write " on "
            Response.write RecordsArray1(3, i)
            Response.write "</b>"
            Response.write vbCrLf
            Case 2
            ' Print details; product name and price
            Response.write vbTab
            Response.write RecordsArray1(2,i)
            Response.write " "
            Response.write FormatCurrency(RecordsArray1(3,i))
            Response.write vbCrLf
        End Select
    Next
Loop
objRS.Close
response.write "</pre>"
E = Timer

Response.write "<p>time: "&(E - S)

objCN.Close
Set objRS = Nothing
Set objCN = Nothing


%>

The first key technique is the addition of a unique identifier for each record, held in the Record Type field. If Record Type is "1", then an order information line is printed. If Record Type is "2", then a product detail line is printed. The second key technique is ordering the results by OrderID. This groups the related records of a particular order together. When referring to columns in the ORDER BY clause, the column names from the first query of the union must be used.

The report based on the union query runs over ten times faster than the report that performs sub-queries. This is despite the fact that the sub-query performs its lookup on an indexed, primary key column, and it is parameterized and prepared. Despite these optimizations, the overhead of repeatedly instantiating the second Recordset and performing a sub-query is still relatively high.

There are some drawbacks to this technique. First of all, the union query requires that the same number of columns be returned by each sub-query. Sub-queries that do not require as many columns as the largest sub-query must return Nulls or empty strings in the unused columns. Generally, this overhead should be minimal, but if there is a great discrepancy between the number of columns, then the benefit of the union query might be undone. In SQL Server, use the CAST function on a Null value to create a place holder column of the right data type, for example "CAST(NULL AS NVARCHAR)".

The second drawback is the complexity and size of the resultant query. It becomes comparable in complexity to the SHAPE query described earlier as a method to avoid. The complexity makes debugging much more difficult, as any query error could be the result of any one of the sub-queries. Query parsers generally do not indicate which sub-query contains the error, so the programmer must use process of elimination. Worse, a query error could be the result of the interaction between any combination of the sub-queries. The number of query combinations grows factorially as the number of queries increases.

This latter burden can be eased by strictly specifying the data types and columns of the result set. Interaction problems generally stem from data type mismatches, so explicit casting or conversion will be good practice. Another thing to be avoided in union queries are pointer-based data types, such as MEMO, NTEXT, BLOB, etc, as the query parser may not be able to handle these.

Despite these drawbacks, the union query technique is very powerful. Complex relationships and orderings can be expressed between different record types using user-defined record identifiers. Database engines are specially designed to produce efficiently such result sets, and so the task is properly given to the database server rather than done in ASP.

Summary of Heirarchical Table Techniques

Heirarchical tables are ASP reports where a sub-query must be performed for each record of a primary query. The nesting of the queries is what makes the report heirarchical. To optimize such a report, the JOIN and SHAPE clauses are to be avoided because they have performance or maintainability drawbacks.

The time required to perform the sub-query can be reduced by adequate preparation. The first step is to use a single Connection object rather than creating one explicitly or implicitly each time. The second step is to use a parameterized and prepared Command object. These steps were shown to provide a 15-fold perfomance improvement in the example.

For heirarchical reports on low-load servers, there is no performance advantage to using stored procedures versus defining the queries inline in ASP. The drawback is that the queries are separated from the ASP code, increasing the code maintenance burden. Under SQL Server, using a separate Connection object for the sub-query is the only way to realize the benefits of the prepared Command object. For some queries, further performance may be obtained by foregoing the Recordset and instead using a Command object with output parameters.

For even greater performance, a union query is a viable option. This technique involves issuing a single query, and allowing the database to combine all the needed rows into a single result set. The ASP code to implement this technique is not much different than the other techniques, but the additional complexity of the query may increase the difficulty of debugging. This difficulty can be mitigated by properly specifying the data types and order of the columns. The example using realistic data showed the union query to be over ten times faster than using a prepared sub-query.

ASP Speed Tricks Site Map

1. ASP Speed Tricks
2. Optimized Table Printing in ASP
3. Optimized Heirarchical Table Printing in ASP ← You Are Here
4. Print Javascript Array from ASP
5. Print Select Options from ASP
6. Javascript Autocomplete Combobox - find as you type
7. ASP Speed Tricks Appendix
8. ASP Speed Tricks PDF Format