ASP optimization: ASP Program Performance test report

Outline: What is the most efficient way for ASP dynamically generated content to output? What is the best way to extract a database recordset? This article has tested nearly 20 common problems with this type of ASP development, and the time shown by the test tools tells us that the problems that can often be taken for granted are not only noteworthy, but also unexpected secrets hidden inside.

First, the purpose of testing

The first part of this paper examines some basic problems in ASP development, and gives some performance test results to help readers understand how the code placed on the page affects performance. ADO is a common, easy-to-use database interface developed by Microsoft, and it turns out that interaction with the database through ADO is one of the most important applications of ASP, and in the second part, we study this problem.

ADO provides a wide range of features, so the biggest difficulty in preparing this article is how to define the scope of the problem. Considering that extracting large amounts of data can significantly increase the load on the Web server, we decide that the main purpose of this section is to find out what is the optimal configuration for manipulating ADO recordsets. However, even if we narrow down the scope of the problem, we still face great difficulties, because ADO can have many different ways to accomplish the same task. For example, a recordset can be extracted not only through the recordset class, but also through the connection and command classes, and even after the Recordset object is obtained, there are many operational methods that can dramatically affect performance. However, as in the first part, we will cover the widest range of issues as much as possible.

Specifically, this part of the goal is to gather enough information to answer the following questions:

Should l include reference adovbs.inc?

L should I create a separate connection object when I use a recordset?

L What is the best way to extract a recordset?

What is the most efficient type of cursor and record locking method?

Should I use a local recordset?

L What is the best way to set the recordset properties?

Which method is most efficient for referencing recordset field values?

Is it a good way to collect output with a temporary string?

Second, the test environment

This test uses a total of 21 ASP files, which can be downloaded later in this document. Each page is set up to run three different queries, returning 0, 25, and 250 records respectively. This will help us isolate the initialization of the page itself, the running overhead, and the overhead of iterating through the recordset.

For ease of testing, both the database connection string and the SQL command string are saved as application variables in Global.asa. Because our test database is SQL Server 7.0, the connection string specifies OLE DB as the connection provider, and the test data comes from the Northwind database of SQL Server. The SQL Select command extracts 7 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 0OrderID," & _

"CustomerID," & _

"EmployeeID," & _

"OrderDate," & _

"RequiredDate," & _

"ShippedDate," & _

"Freight" & _

"From[orders]"

End Sub

/script >

' Alternate sql-25 Records

Application ("SQL") = "Selecttop 25OrderID," & _

"CustomerID," & _

"EmployeeID," & _

"OrderDate," & _

"RequiredDate," & _

"ShippedDate," & _

"Freight" & _

"From[orders]"

' Alternate sql-250 Records

Application ("SQL") = "Selecttop OrderID," & _

"CustomerID," & _

"EmployeeID," & _

"OrderDate," & _

"RequiredDate," & _

"ShippedDate," & _

"Freight" & _

"From[orders]"

The test server is configured as follows: pentium,512 MB RAM,NT Server 4.0 SP5,MDAC 2.1 (Data access component), and version 5.0 of the Microsoft Scripting engine. SQL Server is running on another machine with a similar configuration. As in the first part, we still use the Microsoft Web application Stress Tool to record the time, in milliseconds, from the first page request to the last byte received from the server (Ttlb,time to final byte). The test script calls more than 1300 times per page, runs for approximately 20 hours, and the time shown below is the average ttlb of the session. Keep in mind that, as with the first part, we only care about the efficiency of the Code, not its scalability or server performance.

Also note that we have enabled server buffering. In addition, in order for all file names to be of the same length, one or more underscores are embedded in the file name.

Third, the first Test

In the first test, we simulated the typical scenario found in the Microsoft ASP ADO sample to extract a Recordset. In this example (ado__01.asp), we first open a connection and then create the Recordset object. Of course, the script here is optimized according to the coding rules summarized in the first part of this article.

% Option Explicit%

The!--#Include file= "Adovbs. INC "--

%

Dim objconn

Dim objRS

Response.Write (_

"HTML >< HEAD" & _

"TITLE >ado test

"/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 >customerid

"TH >employeeid

"TH >orderdate

"TH >requireddate

"TH >shippeddate

"TH >freight

"/tr" _

)

' Write Data

Do and 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 >

% >

Here are the test results:

Let's take a look at the meaning of each column number:

0 returns the required ttlb (milliseconds) for 0 recorded pages. In all tests, this value is considered to be the time overhead of building the page itself, including the creation of objects, and does not include the time to iterate through the recordset's data.

25 extracting and displaying 25 records in milliseconds ttlb

Tot TIME/25 the "25" column of Ttlb divided by 25, which is the total average time overhead for each record.

Disp TIME/25 The ttlb of the "25" bar minus the ttlb of the "0" bar, then divide by 25. This value reflects the time required to display a single record when looping the recordset.

250 extract and display the ttlb of 250 records.

Tot time/250 the "250" column of Ttlb divided by 25, which represents the total average time overhead for a single record.

Disp TIME/250 The ttlb of the "250" column minus the ttlb of the "0" column, divided by 250. This value reflects the time required to display a single record when looping the recordset.

The above test results will be used to compare with the next test result.

Iv. should the inclusion of reference Adovbs.inc be adopted?

The Microsoft-provided adovbs.inc contains 270 lines of code that define most of the ADO property constants. Our example only references 2 constants from Adovbs.inc. So in this test (ado__02.asp) We removed the include file reference and set the property directly using the corresponding value.

Objrs.cursortype = 0? ' adOpenForwardOnly

Objrs.locktype = 1 ' adlockreadonly

You can see that page overhead is down by 23%. This value does not affect the extraction and display time of a single record, because changes here do not affect the recordset operation within the loop. There are several ways to resolve the Adovbs.inc reference problem. We recommend that you use the Adovbs.inc file as a reference and set it up with comments. Keep in mind that, as pointed out in the first section, using annotations moderately has minimal impact on the efficiency of your code. Another way 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, which is to make all ADO constants available directly by linking the ADO type library. By adding the following code to the Global.asa file, you can access all the ADO constants directly:

!--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 adovbs.inc files, and access and use ADO constants by other means.

V. Should a separate connection object be created when using a recordset?

To answer this question correctly, we must analyze the tests under two different conditions: first, the page has only one database transaction, and the second, the page has multiple database transactions.

In the preceding scenario, we created a separate connection object and assigned it to the ActiveConnection property of the recordset. However, as shown in ado__03.asp, we can also assign the connection string directly to the ActiveConnection property, and the additional step of initializing and configuring the Connection object in the script can be omitted.

objrs.activeconnection = Application ("Conn")

Although the Recordset object still creates a connection, it is created at this time under highly optimized conditions. As a result, page overhead has dropped by 23% compared to the previous test, and as expected, the display time for individual records has not changed substantially.

Therefore, our second rule is as follows:

If only one recordset is used, assign the connection string directly to the ActiveConnection property.

Next we examine whether the above rules are still valid when using multiple recordsets for the page. To test this scenario, we introduce a for loop that repeats the preceding 10 times. In this test, we will look at three variations:

First, as shown in ado__04.asp, create and dismantle connection objects in each cycle:

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 ado__05.asp, create a Connection object outside the loop, and all recordsets share the 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 ado__06.asp, assign a connection string 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 connection objects within a loop is the least efficient method. However, it is amazing that assigning a connection string directly to the ActiveConnection property within a loop is only a bit slower than sharing a single Connection object.

Nevertheless, the third rule should be:

When multiple recordsets are used within the same page, create a single Connection object and share it through the ActiveConnection property.

Six, which cursor type and record locking mode is the most efficient?

In all tests so far we have only used "forward only" cursors to access the recordset. There are three types of cursors provided by ADO for recordsets: Static scrollable cursors, dynamically scrollable cursors, keyset cursors. Each cursor provides different functionality, such as accessing the previous record and the next record, seeing if other programs are modifying the data, and so on. However, the function of each cursor type is beyond the scope of this article, and the following table is a comparative analysis of the various cursor types.

All other cursor types require additional overhead compared to cursors of only forward type, and these cursors are generally slower within the loop. Therefore, we would like to share with you the following caveat: Never think so-"well, sometimes I use dynamic cursors, so I've been using this cursor." ”

Shanghai treatment of impotence Hospital; The same view applies to the choice of record locking methods. The previous test only used a read-only lock, but there are three other ways: Conservative, open, open batch processing. As with cursor types, these locking methods provide different functionality and control over the processing of recordset data.

We draw the following rules:

L Use the simplest cursor types and record locking methods that are appropriate for processing tasks.

What is the best way to extract the recordset?

So far we've been extracting recordsets by creating a Recordset object, but ADO also provides an indirect recordset extraction method. The following tests compare ado__03.asp and create a recordset (conn_01.asp) directly from the connection object, either:

Set objconn = Server.CreateObject ("ADODB. Connection ")

objConn.Open application ("Conn")

Set objRS = objConn.Execute (Application ("SQL"))

You can see a slight increase in page overhead and no change in the display time of a single record.

Let's take a look at the Recordset object created directly from the Command object (cmd__02.asp):

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. There is a small difference in performance in the latter two approaches, but we have one more important issue to consider.

When creating recordsets from the Recordset class, we are able to control how recordsets are handled with maximum flexibility. Since the last two methods do not have overwhelming performance, we mainly consider the default return cursor type and record locking method, for some occasions the default value is not necessarily the most ideal.

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

L instantiate a recordset with the Adodb.recordset class for the best performance and flexibility.

Viii. should I use a local recordset?

ADO allows a local (client) recordset to be used, at which point the query extracts all the data in the recordset, the connection can be closed immediately after the query completes, and the data is accessed using local cursors later, which facilitates the release of the connection. Using a local Recordset is important for accessing remote Data services that require data to be used offline, so is it also helpful for normal applications?

Below we join the CursorLocation property and close the connection (client1.asp) after opening the recordset:

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 approach is beneficial for efficiency for the following two reasons: first, it avoids the need to repeatedly request data through a connection when moving between records, and secondly, because it can easily release the connection, it reduces resource requirements. However, it is obviously not helpful to use a local recordset from the previous table to improve efficiency. This may be because when using a local recordset, the cursor always becomes a static type, regardless of what the program is setting.

The 6th rule is as follows:

You should avoid using a recordset unless you do require it to be localized.

What is the most efficient way to refer to recordset field values?

10.1 Testing

So far we've been referencing the field values in the recordset by name. Since this method requires that the corresponding field be found every time, it is not efficient. To prove this, in the following test we reference its value (ado__08.asp) by the index of the field in the collection:

' Write Data

Do and 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

Shanghai Men's Hospital program: As expected, there is a small change in page overhead (this may be due to a slight decrease in the code). However, the improvement in the display time of this method is quite obvious.

In the next test, we bind all the fields to the variable (ado__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 and 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 by far the best record. Note that the display time for a single record has been reduced to less than 0.45 milliseconds.

All of the above scripts require an understanding of the construction of the result recordset. For example, we used the field names directly in the column headings to refer to each field value separately. In this test, not only the field data is obtained by traversing the field collection, but also the field headings are obtained in the same way, which is a more dynamic scenario (ado__10.asp).

If objRS.EOF Then
Response.Write("No Records Found")
Else
‘write headings Response.Write("＜ TABLE BORDER=1 ＞＜ TR ＞")
For Each objFld in objRS.Fields
Response.Write("＜ TH ＞" & objFld.name & "＜ /TH ＞")
Next
Response.Write("＜ /TR ＞")
‘write data
Do While Not objRS.EOF 
Response.Write("＜ TR ＞")
For Each objFld in objRS.Fields
? Response.Write("＜ TD ＞" & objFld.value & "＜ /TD ＞")
Next
Response.Write("＜ /TR ＞")
objRS.MoveNext 
Loop 
Response.Write("＜ /TABLE ＞")
End If

As you can see, code performance has dropped, but it is still faster than ado__07.asp.

The next Test example is a tradeoff between the previous two methods. We will continue to maintain dynamic features while improving performance by saving field references in dynamically allocated arrays:

If objRS.EOF Then
Response.Write("No Records Found")
Else
Dim fldCount
fldCount = objRS.Fields.Count
Dim fld()
ReDim fld(fldCount)
Dim i
For i = 0 to fldCount-1
Set fld(i) = objRS(i)
Next

‘write headings
Response.Write("＜ TABLE BORDER=1 ＞＜ TR ＞") For i = 0 to fldCount-1
Response.Write("＜ TH ＞" & fld(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 ＞" & fld(i) & "＜ /TD ＞")
Next
Response.Write("＜ /TR ＞")
objRS.MoveNext 
Loop 
For i = 0 to fldCount-1
Set fld(i) = Nothing
Next
Response.Write("＜ /TABLE ＞")
End If


While it's not going to be better than the previous best, it's faster than the first few examples, and it has the advantage of being able to handle any recordset on the fly.

The following test code has a fundamental change compared to the previous test code. It populates the array with the GetRows method of the Recordset object for iterating over the data, rather than directly accessing the recordset itself. Note that the recordset is set to nothing immediately after calling GetRows, which frees up system resources as soon as possible. Also, notice that the first dimension of the array represents the field, and the second dimension represents the row (ado__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 fld
numFlds = Ubound(arrRS, 1)
numRows = Ubound(arrRS, 2)
For row= 0 to numRows
Response.Write("＜ TR ＞")
For fld = 0 to numFlds
Response.Write("＜ TD ＞" & arrRS(fld, row) & "＜ /TD ＞")
Next
Response.Write("＜ /TR ＞")
Next

Response.Write("＜ /TABLE ＞")
End If

When you use the GetRows method, the entire recordset is extracted to an array. Although the recordset is extremely large, it can cause resource problems, but it is really faster to iterate through the data, due to cancellation of MoveNext and checking for function calls such as EOF.

The speed is paid for, and now the recordset's metadata has been lost. To solve this problem, we can extract header information from the Recordset object before calling GetRows, and the data type and other information can be pre-extracted. It is also important to note that the performance benefits of testing occur only when the recordset is large.

In the last test of this group we used the GetString method of the recordset. The GetString method extracts the entire recordset into a large string and allows you to specify a delimiter (ado__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＞＜TR＞＜TD＞")
‘close recordset early
objRS.Close
Set objRS = Nothing
Response.Write(strTable & "＜/TD＞＜/TR＞＜/TABLE＞")
End If

Although the speed benefits of this approach are obvious, it is only suitable for the simplest operation and does not fit into the slightly complex data manipulation requirements at all.

ASP optimization: ASP Program Performance test report