ASP program performance test report

In what way does ASP dynamically generate content with the highest output efficiency? Which method is best used to extract the database record set? This article has tested nearly 20 common problems in ASP development. The time displayed by the test tool tells us that these problems are not only worth noting, in addition, unexpected secrets are hidden.

I. Purpose

The first part of this article examines some basic issues in ASP development, and provides some performance testing results to help readers understand the impact of Code placed on the page on performance. ADO is a common and easy-to-use database interface developed by Microsoft. It turns out that interaction with databases through ADO is one of the most important ASP applications. In the second part, we will study this issue.

ADO provides a wide range of functions, so the biggest difficulty in preparing this article is how to define the scope of the problem. Considering that extracting a large amount of data may significantly increase the load on the Web server, we decided the main purpose of this part is to find out what is the optimal configuration for operating the ADO record set. However, even if we narrow down the scope of the problem, we still face a lot of difficulties, because ADO can have many different methods to complete the same task. For example, a record set can be extracted not only through the recordset class, but also through the connection and command classes. Even after the record set object is obtained, there are many operation methods that may significantly affect the performance. However, like the first part, we will try to cover the widest range of problems.

Specifically, this part aims to collect enough information and answer the following questions:

L should adovbs. inc be referenced through include?
L should I create a separate connection object when using the record set?
L which method is best used to extract the record set?
L which cursor type and record locking method are the most efficient?
L should I use a local record set?
L which method is best to set the record set attributes?
L which method is used to reference record set fields with the highest efficiency?
L is it a good way to collect output data using temporary strings?

Ii. Test Environment

A total of 21 ASP files are used in this test. These files can be downloaded from the end of this article. Each page is set to run three different queries, with 0, 25, and 250 records returned respectively. This will help us isolate the page initialization, running overhead, and overhead of using the circular access record set.

For ease of test, database connection strings and SQL command strings are saved as Application variables in global. Asa. Because our test database is SQL Server 7.0, the connection string specifies oledb as the connection provider, and the test data comes from the SQL server's northwind database. The SQL SELECT command extracts seven specified fields from the northwind orders table.

<Script language = VBScript runat = Server>
Sub application_onstart
Application ("conn") = "provider = sqloledb ;"&_
"Server = myserver ;"&_
"Uid = sa ;"&_
"Pwd = ;"&_
"Database = northwind"
Application ("SQL") = "selecttop 0 orderid ,"&_
"Customerid ,"&_
"Employeeid ,"&_
"Orderdate ,"&_
"Requireddate ,"&_
"Shippeddate ,"&_
"Freight "&_
"From [orders]"
End sub
</SCRIPT>

'Alternate SQL-25 records
Application ("SQL") = "selecttop 25 orderid ,"&_
"Customerid ,"&_
"Employeeid ,"&_
"Orderdate ,"&_
"Requireddate ,"&_
"Shippeddate ,"&_
"Freight "&_
"From [orders]"

'Alternate SQL-250 records
Application ("SQL") = "selecttop 250 orderid ,"&_
"Customerid ,"&_
"Employeeid ,"&_
"Orderdate ,"&_
"Requireddate ,"&_
"Shippeddate ,"&_
"Freight "&_
"From [orders]"

The test server is configured as follows: 450 MHz Pentium, 512 mb ram, NT Server 4.0 SP5, MDAC 2.1 (data access component), and Microsoft Script Engine version 5.0. SQL Server runs on another machine with similar configurations. Like the first part, we still use the Microsoft Web application stress tool to record the time (TTLB, time to last byte) from the first page request to the time the server receives the last byte ), the time is in milliseconds. The test script calls more than 1300 times per page and runs for about 20 hours. The following shows the average TTLB of the session. Remember, like the first part, we only care about code efficiency, not its scalability or server performance.

Note that the server buffer is enabled. In addition, in order to keep all file names of the same length, some file names are embedded with one or more underscores.
Iii. First test

In the first test, we extracted a record set from a typical scenario that can be found in the Microsoft asp ado sample. In this example (ado000001. asp), we first open a connection and create a record set object. Of course, the script here is optimized according to the encoding rules summarized in the first part of this article.

<% Option explicit %>
<! -- # Include file = "adovbs. Inc" -->
<%
Dim objconn
Dim objrs

Response. Write (_
"<HTML> "<Title> ADO test </title> "&_
"</Head> <body> "_
)

Set objconn = server. Createobject ("ADODB. Connection ")
Objconn. open application ("conn ")

Set objrs = server. Createobject ("ADODB. recordset ")
Objrs. activeconnection = objconn
Objrs. cursortype = adopenforwardonly
Objrs. locktype = adlockreadonly
Objrs. open application ("SQL ")

If objrs. EOF then
Response. Write ("no records found ")
Else
'Write headings
Response. Write (_
"<Table border = 1> "&_
"<Tr> "&_
"<TH> orderid </Th> "&_
"<TH> customerid </Th> "&_
"<TH> employeeid </Th> "&_
"<TH> orderdate </Th> "&_
"<TH> requireddate </Th> "&_
"<TH> shippeddate </Th> "&_
"<TH> freight </Th> "&_
"</Tr> "_
)
'Write data
Do while not objrs. EOF
Response. Write (_
"<Tr> "&_
"<TD>" & objrs ("orderid") & "</TD> "&_
"<TD>" & objrs ("customerid") & "</TD> "&_
"<TD>" & objrs ("employeeid") & "</TD> "&_
"<TD>" & objrs ("orderdate") & "</TD> "&_
"<TD>" & objrs ("requireddate") & "</TD> "&_
"<TD>" & objrs ("shippeddate") & "</TD> "&_
"<TD>" & objrs ("freight") & "</TD> "&_
"</Tr> "_
)
Objrs. movenext
Loop
Response. Write ("</table> ")
End if

Objrs. Close
Objconn. Close
Set objrs = nothing
Set objconn = nothing

Response. Write ("</body> %>

The test result is as follows:

Let's take a look at the meanings of the numbers in each column:
0 returns the TTLB (MS) required for the page with 0 records ). In all tests, this value is regarded as the time overhead for generating the page itself (including creating objects) and does not include the time when the record set data is accessed cyclically.
Extract and display the TTLB of 25 records in milliseconds
TTLB In the tot time/25 "25" column is divided by 25, which indicates the total average time overhead of each record.
In the disp time/25 "25" column, TTLB is subtracted from the TTLB in the "0" column, and then divided by 25. This value reflects the time required to display a single record in the cyclic record set.
250 extract and display the TTLB of 250 records.
TTLB In the tot time/250 "250" column is divided by 25. This value indicates the total average time overhead of a single record.
TTLB In The DISP time/250 "250" column minus TTLB in the "0" column and then divided by 250. This value reflects the time required to display a single record in the cyclic record set.

The above test results will be compared with the next test results.
4. Should adovbs. inc be referenced through include?

The adovbs. INC provided by Microsoft contains 270 lines of code that defines most ADO attribute constants. In this example, only two constants are referenced from adovbs. Inc. Therefore, in this test (ado000002. asp), we deleted the reference containing files and used the corresponding values directly when setting the attributes.

Objrs. cursortype = 0? 'Adopenforwardonly
Objrs. locktype = 1 'adlockreadonly

 

The page overhead is reduced by 23%. This value does not affect the extraction and display time of a single record, because the changes here do not affect the record set operations in the loop. There are multiple ways to resolve adovbs. inc references. We recommend that you use the adovbs. inc file as a reference and use annotations to describe the settings. Remember, as pointed out in the first part, using annotations moderately has little impact on code efficiency. Another method is to copy the constants that need to be used from the adovbs. inc file to the page.

There is also a good way to solve this problem. This is to link the ADO database to make all ADO constants available directly. Add the following code to the Global. Asa file to directly access all ADO constants:

<! -- Metadata type = "typelib"
File = "C: Program filescommon filessystemadomsado15.dll"
Name = "ADODB Type Library" -->

Or:

<! -- Metadata type = "typelib"
UUID = "00000205-0000-0010-8000-00aa006d2ea4"
Name = "ADODB Type Library" -->

Therefore, our first rule is:

L avoid including the adovbs. inc file, and access and use ADO constants in other ways.
5. Should I create a separate connection object when using the record set?

To answer this question correctly, we must analyze the tests under two different conditions: first, there is only one database transaction on the page; second, there are multiple database transactions on the page.

In connector, we create a separate connection object and assign it to the activeconnection attribute of recordset. However, as shown in ado000003. asp, we can also directly assign the connection string to the activeconnection attribute, saving the additional steps of initializing and configuring the connection object in the script.

Objrs. activeconnection = Application ("conn ")

Although the recordset object still needs to create a connection, It is created under highly optimized conditions. Therefore, compared with the previous test, the page overhead is reduced by 23%, and as expected, the display time of a single record does not change substantially.

Therefore, our second rule is as follows:

L if only one record set is used, the connection string is directly assigned to the activeconnection attribute.

Next, check whether the above rules are still valid when multiple record sets are used on the page. To test this situation, we introduce a for loop to repeat the iterator 10 times. In this test, we will study three changes:

First, as shown in ado000004. asp, create and remove a connection object in each loop:

Dim I
For I = 1 to 10

Set objconn = server. Createobject ("ADODB. Connection ")
Objconn. open application ("conn ")

Set objrs = server. Createobject ("ADODB. recordset ")
Objrs. activeconnection = objconn
Objrs. cursortype = 0 'adopenforwardonly
Objrs. locktype = 1 'adlockreadonly
Objrs. open application ("SQL ")
If objrs. EOF then
Response. Write ("no records found ")
Else
'Write headings
...
'Write data
...
End if

Objrs. Close
Set objrs = nothing
Objconn. Close
Set objconn = nothing

Next

Second, as shown in ado20.05. asp, create a connection object outside the loop. All Record Sets share this object:

Set objconn = server. Createobject ("ADODB. Connection ")
Objconn. open application ("conn ")

Dim I
For I = 1 to 10
Set objrs = server. Createobject ("ADODB. recordset ")
Objrs. activeconnection = objconn
Objrs. cursortype = 0 'adopenforwardonly
Objrs. locktype = 1 'adlockreadonly
Objrs. open application ("SQL ")

If objrs. EOF then
Response. Write ("no records found ")
Else
'Write headings
...
'Write data
...
End if

Objrs. Close
Set objrs = nothing

Next
Objconn. Close
Set objconn = nothing

Third, as shown in ado20.06. asp, the connection string is assigned to the activeconnection attribute in each loop:

Dim I
For I = 1 to 10

Set objrs = server. Createobject ("ADODB. recordset ")
Objrs. activeconnection = Application ("conn ")
Objrs. cursortype = 0 'adopenforwardonly
Objrs. locktype = 1 'adlockreadonly
Objrs. open application ("SQL ")

If objrs. EOF then
Response. Write ("no records found ")
Else
'Write headings
...
'Write data
...
End if

Objrs. Close
Set objrs = nothing
Next

 

As we can guess, creating and removing a connection object in a loop is the most efficient method. However, it is surprising that assigning a connection string directly to the activeconnection attribute in a loop is only a little slower than sharing a single connection object.

However, the third rule should be:

L when multiple record sets are used on the same page, create a single connection object and share it through the activeconnection attribute.
6. Which cursor type and record locking method are the most efficient?

In all tests so far, we only use a forward cursor to access the record set. ADO provides three types of cursors for record sets: static and scrollable cursors, dynamic and scrollable cursors, and keyset cursors. Each type of cursor provides different functions, such as accessing the previous record and the next record, and checking whether other programs can modify the data. However, the function of each type of cursor is beyond the scope of this article. The following table is a comparative analysis of various types of cursors.

Compared with forward-only cursors, all other cursors require additional costs, and these cursors are generally slower in a loop. Therefore, we would like to share with you the following warning: Never think like this -- "well, sometimes I will use dynamic cursors, so I will always use such cursors ."

The same view applies to the selection of record locking methods. The previous test only used the read-only locking method, but there are three other methods: batch, open, and open batch processing. Like the cursor type, these locking methods provide different functions and control capabilities for processing record set data.

We come up with the following rules:

L use the simplest cursor type and record locking method suitable for processing tasks.
7. which method is best used to extract the record set?

So far, we have been extracting record sets by creating recordset objects, but ADO also provides indirect record set extraction methods. The following two methods are compared: ado1_03. asp and conn_01.asp:

Set objconn = server. Createobject ("ADODB. Connection ")
Objconn. open application ("conn ")

Set objrs = objconn. Execute (Application ("SQL "))

The page overhead is slightly increased, and the display time of a single record is not changed.

Next, let's take a look at how to directly create a record set object (optional values 02. asp) from the command object ):

Set objcmd = server. Createobject ("ADODB. Command ")
Objcmd. activeconnection = Application ("conn ")
Objcmd. commandtext = Application ("SQL ")

Set objrs = objcmd. Execute

Similarly, the page overhead also increases slightly, while the display time of a single record does not change substantially. The performance difference between the two methods is very small, but we still have an important issue to consider.

When using the recordset class to create a record set, we can control the processing method of the record set with maximum flexibility. Since the last two methods fail to provide overwhelming performance, we mainly consider the default returned cursor type and record lock method. In some cases, the default value is not necessarily the most ideal.

Therefore, unless the following two methods need to be selected for special reasons, we recommend that you consider the following rules:

L instantiate the record set through the ADODB. recordset class to obtain the best performance and flexibility.

8. Should I use a local record set?

Ado allows you to use a local (client) record set. In this case, all data in the record set is extracted. After the query is complete, the connection can be closed immediately. Later, you can use a local cursor to access the data, this makes it easy to release the connection. Using a local record set is very important for accessing remote data services that require offline data use. Is it also helpful for common applications?

Next we add the cursorlocation attribute and close the connection (client1.asp) after opening the record set ):

Set objrs = server. Createobject ("ADODB. recordset ")
Objrs. cursorlocation = 2 'aduseclient
Objrs. activeconnection = Application ("conn ")
Objrs. locktype = 1? 'Adlockreadonly
Objrs. open application ("SQL ")
Objrs. activeconnection = nothing

Theoretically, this method will benefit efficiency for the following two reasons: first, it avoids repeated requests for data when moving between records; second, because it can easily release connections, it reduces resource requirements. However, from the table above, it seems that using a local record set does not significantly help improve efficiency. This may be because when a local record set is used, no matter what the program sets, the cursor is always changed to a static type.

6th rules are as follows:

L avoid using the record set unless it is indeed required to be localized.
10. which method is used to reference the highest efficiency of record set Field Values?

10.1 Test

So far, we have been referencing the Field Values in the record set by name. Because this method requires that the corresponding field be searched every time, the efficiency is not high. To prove this, in the following test, we reference the value of a field in the SET index (ado20.08. asp ):

'Write data
Do while not objrs. EOF
Response. Write (_
"<Tr> "&_
"<TD>" & objrs (0) & "</TD> "&_
"<TD>" & objrs (1) & "</TD> "&_
"<TD>" & objrs (2) & "</TD> "&_
"<TD>" & objrs (3) & "</TD> "&_
"<TD>" & objrs (4) & "</TD> "&_
"<TD>" & objrs (5) & "</TD> "&_
"<TD>" & objrs (6) & "</TD> "&_
"</Tr> "_
)
Objrs. movenext
Loop

 

As expected, the page overhead also changes slightly (perhaps because the code is slightly reduced ). However, the improvement in display time is quite obvious.

In the next test, we bind all the fields to the variables (ado20.09. asp) respectively ):

If objrs. EOF then
Response. Write ("no records found ")
Else
'Write headings
...
Dim fld0
Dim fld1
Dim fld2
Dim fld3
Dim fld4
Dim fld5
Dim fld6
Set fld0 = objrs (0)
Set fld1 = objrs (1)
Set fld2 = objrs (2)
Set fld3 = objrs (3)
Set fld4 = objrs (4)
Set fld5 = objrs (5)
Set fld6 = objrs (6)
'Write data
Do while not objrs. EOF
Response. Write (_
"<Tr> "&_
"<TD>" & fld0 & "</TD> "&_
"<TD>" & fld1 & "</TD> "&_
"<TD>" & fld2 & "</TD> "&_
"<TD>" & fld3 & "</TD> "&_
"<TD>" & fld4 & "</TD> "&_
"<TD>" & fld5 & "</TD> "&_
"<TD>" & fld6 & "</TD> "&_
"</Tr> "_
)
Objrs. movenext
Loop
Set fld0 = nothing
Set fld1 = nothing
Set fld2 = nothing
Set fld3 = nothing
Set fld4 = nothing
Set fld5 = nothing
Set fld6 = nothing
Response. Write ("</table> ")
End if

This is the best record so far. Note that the display time of a single record has been reduced to less than 0.45 milliseconds.

All the above scripts require an understanding of the structure of the result record set. For example, we directly use the field name in the column title and reference each field value separately. In the following test, not only the field data is obtained by traversing the field set, but also the field title is obtained in the same way. This is a more dynamic solution (ado20.10. asp ).

If objrs. EOF then
Response. Write ("no records found ")
Else
'Write headings response. Write ("<Table border = 1> <tr> ")
For each objrecords in objrs. Fields
Response. Write ("<TH>" & objworkflow. Name & "</Th> ")
Next
Response. Write ("</tr> ")
'Write data
Do while not objrs. EOF
Response. Write ("<tr> ")
For each objrecords in objrs. Fields
? Response. Write ("<TD>" & obj1_. Value & "</TD> ")
Next
Response. Write ("</tr> ")
Objrs. movenext
Loop
Response. Write ("</table> ")
End if

As you can see, the code performance has declined, but it is still faster than ado1_07. asp.

The next test example shows the compromise between the first two methods. We will continue to maintain dynamic features and improve performance by saving field references in the dynamically allocated array:

If objrs. EOF then
Response. Write ("no records found ")
Else
Dim fldcount
Fldcount = objrs. Fields. Count
Dim Merge ()
Redim Merge (fldcount)
Dim I
For I = 0 to fldCount-1
Set Topology (I) = objrs (I)
Next

'Write headings
Response. Write ("<Table border = 1> <tr>") for I = 0 to fldCount-1
Response. Write ("<TH>" & Records (I). Name & "</Th> ")
Next
Response. Write ("</tr> ")
'Write data
Do while not objrs. EOF
Response. Write ("<tr> ")
For I = 0 to fldCount-1
Response. Write ("<TD>" & Response (I) & "</TD> ")
Next
Response. Write ("</tr> ")
Objrs. movenext
Loop
For I = 0 to fldCount-1
SET transaction (I) = nothing
Next
Response. Write ("</table> ")
End if

 

Although it cannot exceed the previous best score, it is faster than the first few examples, and it has the advantage of dynamically processing any record set.

Compared with the previous test code, the following test code has undergone fundamental changes. It uses the getrows method of the record set object to fill the array for circular access to data, rather than directly accessing the record set itself. Note that the recordset is set to nothing immediately after getrows is called, that is, the system resources are released as soon as possible. In addition, note that the first dimension of the array represents the field, and the second dimension represents the row (ado1_12. asp ).

If objrs. EOF then
Response. Write ("no records found ")
Objrs. Close
Set objrs = nothing
Else
'Write headings
...

'Set Array
Dim arrrs
Arrrs = objrs. getrows
'Close recordset early
Objrs. Close
Set objrs = nothing

'Write data
Dim numrows
Dim numflds
Dim row
Dim labels
Numflds = ubound (arrrs, 1)
Numrows = ubound (arrrs, 2)
For ROW = 0 to numrows
Response. Write ("<tr> ")
For tables = 0 to numflds
Response. Write ("<TD>" & arrrs (rows, row) & "</TD> ")
Next
Response. Write ("</tr> ")
Next

Response. Write ("</table> ")
End if

When the getrows method is used, the entire record set is extracted to an array. Although resource problems may occur when the record set is extremely large, circular data access is indeed faster, because movenext and function calls such as checking eof are canceled.

The speed is costly, and the metadata of the record set is now lost. To solve this problem, we can extract the title information from the record set object before calling getrows. In addition, the data type and other information can be extracted in advance. In addition, it should be noted that the advantage of neutral performance testing only appears when the record set is large.

In the last test of this group, we used the getstring method of the record set. The getstring method extracts the entire record set into a large string and allows the specified separator (ado1_13. asp ):

If objrs. EOF then
Response. Write ("no records found ")
Objrs. Close
Set objrs = nothing
Else
'Write headings
...
'Set Array
Dim strtable
Strtable = objrs. getstring (2, "</TD> <TD>", "</TD> </tr> <TD> ")
'Close recordset early
Objrs. Close
Set objrs = nothing
Response. Write (strtable & "</TD> </tr> </table> ")
End if

Although this method has obvious advantages in speed, it is only applicable to the simplest operations and cannot adapt to slightly complex data operation requirements.