ADO, ASP, and Components

by Susie Adams

In today's fast-paced development environment, one of the most challenging and confusing issues an Active Server Web developer faces is deciding where to code an application's business logic. In fact, the most common inquiry from ASP users is where and how to access ODBC data stores.

After the release of Visual InterDev 1.0 about a year ago, developers began learning how to create ASP applications by reviewing the code that the Visual InterDev design-time controls and wizards created. These productivity tools generated script that created database connections and transversed recordsets inside an ASP page.

Furthermore, as developers began to master these skills, they started to question the performance of the interpreted ASP environment and the script they had written in order to read and write to an ODBC database. For many, the question still remains: Should you really include the ADO script inside the Active Server page or would it be more efficient to move the data access portion of the business logic into a middle-tier COM component?

In this article, we'll investigate several database access alternatives. We'll create three different ASP pages that generate the same simple HTML table, based on an SQL query. The table will display employee time records for employee number 17; the process time for each page will be displayed at the bottom of the page. Hopefully, the final results will lend some valuable insight into the ASP ADO data access dilemma.

ADO business logic, ASP-style

The first example will execute an SQL query and create the HTML table, whose code is shown in Listing A. We'll be using Active Server script exclusively for the query. To begin, create a new Web project by selecting the New… option from Visual InterDev's File menu. From the Project tab, select the Web project option and enter the name of the new Web, aspPerform. Next, create a new page named perf1.asp by selecting the File menu's New… option and selecting the File tab's Active Server Page option. At this point, add the code found in Listing A. The first line of this code creates and opens an ADO connection using the ADO connection object, as follows:

Set conn = CreateObject ("ADODB.Connection")
conn.Open "DSN=tb;UID=sa"

Now, let's create an ADO command object to access and run an SQL query against an SQL Server 6.5 TimeBilling database. You could accomplish this step in several ways; however, for simplicity's sake, I prefer to use the command object. For those of you unfamiliar with the ADO command object, a command object is a definition of a specific command that you intend to execute against a data source. You can create a command object independently of a previously defined connection by setting its ActiveConnection property to a valid connection string. For example, in the line 

Set cmd.ActiveConnection = conn

ADO still creates a connection object, but it doesn't assign that object to an object variable.

Next, I created the SQL query and assigned it to the Command object's CommandText property. To execute the query, I created a recordset using the ADO recordset object and then opened the recordset with the command object. Once the recordset was available, I created the HTML table headers, then looped through the recordset displaying the field objects for each record as columns in the HTML table. In order to determine the amount of time it takes to execute the page, I used the now() function at the top of each page to determine the start time; I compared that value to the now() time computed at the bottom of the ASP page.

To view the page, save the Perf1.asp page and browse it using Internet Explorer, as shown in Figure A. The first time I ran the page, it took approximately 18 seconds to run. The second time I ran it, the IE browser was open and the execution time decreased to 15 seconds—still a fair amount of time to execute and display approximately 100 records in a table.

Note: Your times may vary depending on the speed and location of your server. I executed my demos on a standalone server that was also acting as my client machine. The client was IE 4.0 and the server was IIS 4.0.

Figure A: Use Internet Explorer to browse the Employee Time Table, ASP-style.

Listing A Accessing ADO inside ASP script

<% Option Explicit %>
<html>
lt;title>Performance 1</title>

<body>
<%
Dim start

start = Now()

Dim conn
Dim cmd
Dim rs
Dim row
Dim empid
Dim sqltext

Set conn = CreateObject ("ADODB.Connection")
conn.Open "DSN=tb;UID=sa"

Set cmd = CreateObject ("ADODB.Command")

sqltext = "select emp.emp_id, hide, entry_date, project_code, sub_project_code, last_name, first_name, act_hrs, bill_hrs, bill_amt, act_amt "
sqltext = sqltext + "from emp, emptime "
sqltext = sqltext + "where emp.emp_id = 17 and emp.emp_id = emptime.emp_id "
sqltext = sqltext + "order by last_name, first_name, entry_date, project_code, sub_project_code"

cmd.CommandText = sqltext

Set cmd.ActiveConnection = conn

Set rs = CreateObject ("ADODB.Recordset")
rs.Open cmd

row = 0

%>
<H1>Employee Time</H1>

&;table>
<tr bgcolor="#C0C0C0">
<td>Name</td>
<td>Date</td>
<td>Project</td>
<td>Sub-Project</td>
<td>Actual Hours</td>
<td>Billable Hours</td>
<td>Amount</td>
</tr>
<FONT COLOR="black">
<% Do While Not rs.EOF %>
   <tr bgcolor = 
   <% If row Mod 2 Then %>
      "#C0FFFF"
   <% Else %>
      "#FFFFFF"
   <% End If %>
   >
    <td><%= rs.Fields("last_name") %></td>
    <td><%= rs.Fields("entry_date") %></td>
    <td><%= rs.Fields("project_code") %></td>
    <td><%= rs.Fields("sub_project_code")%></td>
    <td><%= rs.Fields("act_hrs") %></td>
   <td><%= rs.Fields("bill_hrs") %></td>
   <td><%= rs.Fields("bill_amt") %></td>
   </tr>
   <%
   row = row + 1 
   rs.MoveNext 
   %>
<% Loop %>
</table>
<p>
Running Time = <%= DateDiff("s", start, Now()) %> seconds

</body>

</html>

The time delay experienced in this example results from the combination of several factors: the server, the connection speed, and the database, just to mention three. The most significant contributor is the interpretive nature of Active Server script. There are, however, a few tricks that can help you speed up the script execution speed. If you must access ODBC data from inside an Active Server page, you can achieve the most significant, positive effect on performance using the ADO field object.

Using the ADO field object

In this example, we'll once again access the ODBC database from within an ASP page. However, this time we're going to use the ADO field object to retrieve a handle to the result set's field objects. A recordset object has a fields collection made up of field objects. Each field object corresponds to a column in the recordset.

In the first example, we resolved the address of the field object each time we displayed a column.  Here, we'll resolve the handle to the field objects only once per column before we begin looping through the records in the recordset.

To begin, create a new page called perf2.asp. In the new page, add the script shown in Listing B. The first several lines of script are identical to the script in the first example. I created an ADO connection object, created an ADO command object, assigned the SQL query to the command object's CommandText property, set the active connection, created a recordset, and opened the recordset with the command object executing the query—thus creating a resultset. After I created the resultset, I began to process the records it contained. At this point, the similarities in the two listings come to an end.

In the next lines of script, I created several variables as placeholders to recordset field object references. Then, if the record count was greater then 0, I assigned the field pointers for each recordset field object to their corresponding field variable. Next, as I looped through the records, I used the field variables instead of the field object itself to reference and display the values of the current record's columns.

To view the page, first save it and then browse it using IE. When I browsed this page, my execution time dropped two seconds from the first example. Again, the length of time depends on the system setup you're using to develop and run the pages.

Listing B: Perf2.asp – ASP ADO access with the field object

<% Option Explicit %>
<html>
<title>Perf 2</title>

<body>
<%
Dim start

start = Now()

Dim conn
Dim cmd
Dim rs
Dim row
Dim empid
Dim sqltext

Set conn = CreateObject ("ADODB.Connection")
conn.Open "DSN=tb;UID=sa"

Set cmd = CreateObject ("ADODB.Command")

sqltext = "select emp.emp_id, hide, entry_date, project_code, sub_project_code, last_name, first_name, act_hrs, bill_hrs, bill_amt, act_amt "
sqltext = sqltext + "from emp, emptime "
sqltext = sqltext + "where emp.emp_id = 17 and emp.emp_id = emptime.emp_id "
sqltext = sqltext + "order by last_name, first_name, entry_date, project_code, sub_project_code"

cmd.CommandText = sqltext

cmd.ActiveConnection = conn

Set rs = CreateObject ("ADODB.Recordset")
rs.Open cmd

Dim fldName
Dim fldDate
Dim fldProject
Dim fldSubProject
Dim fldActualHours
Dim fldBillableHours
Dim fldAmount

row = 0

%>
<H1>Employee Time</H1>

<table>
<tr bgcolor="#C0C0C0">
<td>Name</td>
<td>Date</td>
<td>Project</td>
<td>Sub-Project</td>
<td>Actual Hours</td>
<td>Billable Hours</td>
<td>Amount</td>
</tr>
<FONT COLOR="black">

<% 
If Not rs.EOF Then
   Set fldName = rs.Fields("last_name")
   Set fldDate = rs.Fields("entry_date")
   Set fldProject = rs.Fields("project_code")
   Set fldSubProject = rs.Fields("sub_project_code")
   Set fldActualHours = rs.Fields("act_hrs")
   Set fldBillableHours = rs.Fields("bill_hrs")
   Set fldAmount = rs.Fields("bill_amt")
End If   
%>
<% Do While Not rs.EOF %>
   <tr bgcolor = 
   <% If row Mod 2 Then %>
      "#C0FFFF"
   <% Else %>
      "#FFFFFF"
   <% End If %>
   >
   <td><%= fldName %></td>
    <td><%= fldDate %></td>
    <td><%= fldProject %></td>
    <td><%= fldSubProject%></td>
    <td><%= fldActualHours %></td>
   <td><%= fldBillableHours %></td>
   <td><%= fldAmount %></td>    
   </tr>
   <%
   row = row + 1 
   rs.MoveNext 
   %>
<% Loop %>
</table>
<p>
Running Time = <%= DateDiff("s", start, Now()) %> seconds

</body>

</html>

Formatting an HTML table using a COM object

In the previous example, we found the most efficient way to retrieve data from an ODBC database: ASP script. Unfortunately, the execution time was significantly greater than the time required for a typical client/server application to retrieve and display the same query in a similar table. In fact, this time problem is representative of the complaints received by most developers as they migrate a Windows applications to the ASP Web environment. So, how do we improve the applications' performance? One way is to use COM components.

COM components are natively compiled, and therefore execute much faster than the alternative, interpretive ASP script. You can use these components to create and return ADO recordsets; and better yet, you can use ADO recordsets to create and render HTML tables. In the example that follows, I created a COM object that accepts one parameter, an ADO recordset. The object then takes the recordset and dynamically creates and displays an HTML table.

To begin, I created a new .asp page, name it perf3.asp, and entered the script contained in Listing C. (The initial script should look very similar to the script in the first two ASP examples.)Next, I created an ADO connection, command, and recordset. Now, however, instead of processing the recordset in the ASP script, I created a reference to a COM object, MyAsp.Format. I called one of its methods, ColorTable, with the recordset as a parameter, with the following lines:

   Set Format = CreateObject("MyAsp.Format")

   Format.ColorTable(rs)

The ColorTable method accepts the recordset parameter, then creates and displays the HTML table. Now we're ready to take a look at the MyASP VB5 COM component. You'll find the code for this component in Listing C.

 

Listing C: Using a VB 5.0 COM object to format an HTML page

<% Option Explicit %>
<html>
<title>Perf 3</title>

<body>
<%
Dim start

start = Now()

Dim conn
Dim cmd
Dim rs
Dim row

Dim empid
Dim sqltext


Set conn = CreateObject ("ADODB.Connection")
conn.Open "DSN=tb;UID=sa"

Set cmd = CreateObject ("ADODB.Command")

sqltext = "select emp.emp_id, hide, entry_date, project_code, sub_project_code, last_name, first_name, act_hrs, bill_hrs, bill_amt, act_amt "
sqltext = sqltext + "from emp, emptime "
sqltext = sqltext + "where emp.emp_id = 17 and emp.emp_id = emptime.emp_id "
sqltext = sqltext + "order by last_name, first_name, entry_date, project_code, sub_project_code"

cmd.CommandText = sqltext

cmd.ActiveConnection = conn

Set rs = CreateObject ("ADODB.Recordset")
rs.Open cmd

%>
<H1><%=empid %> Employee Time</H1>

<table>
<tr bgcolor="#C0C0C0">
<td>Name</td>
<td>Date</td>
<td>Project</td>
<td>Sub-Project</td>
<td>Actual Hours</td>
<td>Billable Hours</td>
<td>Amount</td>
</tr>
<FONT COLOR="black">
<%
   Dim Format

   Set Format = CreateObject("MyAsp.Format")

   Format.ColorTable(rs)
%>
</table>
<p>
Running Time = <%= DateDiff("s", start, Now()) %> seconds

</body>

</html>

We created and compiled the VB5 COM component, shown in Figure B, as an ActiveX DLL file. The MyAsp component contains one class, named Format, that has one method named ColorTable. ColorTable accepts one parameter of type recordset.

To create the component, start a new VB5 ActiveX Dll project named MyASP. Begin by selecting the VB5 new project, ActiveX Dll option. Next, create a new class for the project by selecting the Project menu's Add Class Module option. In the dialog that appears, choose the New Class Module option and rename the class it creates to Format. Once you've created the Format class, add the code found in Listing D to the Class Module code window, creating the ColorTable method.

In order to talk to an Active Server Web page from a COM component, the project must reference two libraries: the Microsoft Active Server Pages Object Library and the Microsoft Transaction Server Type Library. To reference these libraries, select the Project menu's References menu option. In the References dialog that appears, find and select both libraries specified above, as shown in Figure C. The Microsoft Transaction Server Type Library allows you to use the GetObjectContext function to retrieve a reference to the Response object. You can then use the Response object to send output to a client browser. The Write method of the Response object writes a specified string to the current HTTP output. The Active Server Pages Object Library allows you to dimension a variable of type response.

Listing D – The Format Class ColorTable method.

Sub ColorTable(rs As ADODB.Recordset)
    Dim Response As Response
    Dim row As Integer
    
    Dim color As String
    
    Dim fldName As ADODB.Field
    Dim fldDate As ADODB.Field
    Dim fldProject As ADODB.Field
    Dim fldSubProject As ADODB.Field
    Dim fldActualHours As ADODB.Field
    Dim fldBillableHours As ADODB.Field
    Dim fldAmount As ADODB.Field
    
    Set Response = GetObjectContext("Response")
    
    If Not rs.EOF Then
       Set fldName = rs.Fields("last_name")
       Set fldDate = rs.Fields("entry_date")
       Set fldProject = rs.Fields("project_code")
       Set fldSubProject = rs.Fields("sub_project_code")
       Set fldActualHours = rs.Fields("act_hrs")
       Set fldBillableHours = rs.Fields("bill_hrs")
       Set fldAmount = rs.Fields("bill_amt")
    End If
    
    row = 1
    Do While Not rs.EOF
        If row Mod 2 Then
            color = "<tr bgcolor =""#C0FFFF"">"
        Else
            color = "<tr bgcolor =""#FFFFFF"">"
        End If
        
        'This works OK, but it is slower
        'Response.Write (color)
        'Response.Write ("<td>" & fldName & "</td>")
        'Response.Write ("<td>" & fldDate & "</td>")
        'Response.Write ("<td>" & fldProject & "</td>")
        'Response.Write ("<td>" & fldSubProject & "</td>")
        'Response.Write ("<td>" & fldActualHours & "</td>")
        'Response.Write ("<td>" & fldBillibleHours & "</td>")
        'Response.Write ("<td>" & fldAmount & "</td>")
        'Response.Write ("</tr>" & Chr(13) & Chr(10))
        
        Response.Write (color & "<td>" & fldName & "</td><td>" _
        & fldDate & "</td><td>" _
        & fldProject & "</td><td>" _
        & fldSubProject & "</td><td>" _
        & fldActualHours & "</td><td>" _
        & fldBillableHours & "</td><td>" _
        & fldAmount & "</td></tr>" & Chr(13) & Chr(10))
        
        row = row + 1
        rs.MoveNext
    Loop
        
End Sub

In the first line of VB code, I dimensioned a variable of type response to hold a reference to the Active Server Response object. Then, I defined several variables of type field to hold references to the recordset's field objects. You should notice a similarity between the way we're processing the recordsets in both the ASP and VB environments. In each case, it's better to obtain a handle to the field object just once—as opposed to each time you loop through the recordset. The only real coding difference is in the language syntax.

As I begin to loop through the recordset, I use the Response object's write method to marshal the HTML text back to the user's browser. In this particular case, I can choose to execute a Response.write for each column in the table, or I can construct one string value for each row and then issue just one Response.write for each row processed. The second approach requires the data to travel over the wire once per record and is therefore much more efficient than the first method, which requires the transfer of data seven distinct times per record. (The VB5 project is displayed in Figure B.)

Figure B: The VB5 COM component requires data to travel over the wire only once per record.

Figure C: Select both server libraries in the VB5 Project References dialog.

To browse the perf2.asp page, save the MyAsp VB5 component you just created, then compile it using the VB5 File menu's Make MyASP.dll menu option. If you're developing on the IIS server machine, the component will be registered automatically when you compile the project. If you're not using the IIS server machine, you must register the component on the IIS machine using RegSrv32.

Note: Please refer to the appropriate Microsoft documentation for a more detailed explanation of DLL registration information.

Next, save the perf3.asp file and browse it. You should see a remarkable difference in execution time. My demo, shown in Figure D, executed in a mere five seconds!

Figure D: The execution time definitely decreases.

Conclusion

The advantages offered by natively compiled COM components are hard to ignore given the performance results of the three Active Server pages you just created. The first and second examples demonstrated the performance issues associated with runtime interpreted script. The third page displayed a remarkable performance improvement over the first two, thanks to the introduction of the COM component used to format the Employee Time HTML table.

Recently, Microsoft launched a new initiative named Windows DNA (Distributed Network Architecture). This architecture stresses the importance of the migration of almost all application business logic to middle-tier COM components. In fact, the next release of Visual InterDev (2.0), which should now be out in beta, is said to provide developers with an environment that helps facilitate the use of middle-tier COM components.

Susie Adams is a senior consultant with Spectrum Technologies, a client/server and Internet solutions consulting firm in McLean, Virginia. She has more than 11 years of application development experience and currently focuses on the design and development of active content Web applications.

Copyright © 1998, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.