After reading the following article, I found that the author's test is really helpful to friends who use ASP. It should be similar not only for ASP but other languages. Generating dynamic content on the server is one of the main reasons for using ASP, so the first test project we chose was to determine the best method for sending dynamic content to the response stream. There are two basic options (and some variations of them): using inline ASP tags and using the Response.Write statement.
To test these different approaches, we created a simple ASP page that defined some variables and then inserted them into a table. Although this page is simple and of no practical use, it is enough to allow us to isolate and test individual issues.
2.1 Using ASP inline tags
The first test is to use ASP's inline tag <%= x %>, where x is a variable. This is the most convenient method to use, and it makes the HTML portion of the page easier to read and maintain.
Copy the code code as follows:
<% OPTION EXPLICIT
Dim FirstName
DimLastName
Dim MiddleInitial
Dim Address
Dim City
Dim State
DimPhoneNumber
Dim FaxNumber
Dim Email
DimBirthDate
FirstName = John
MiddleInitial = Q
LastName = Public
Address = 100 Main Street
City=New York
State=NY
PhoneNumber = 1-212-555-1234
FaxNumber = 1-212-555-1234
Email = [email protected]
BirthDate = 1/1/1950
%>
<HTML>
<HEAD>
<TITLE>Response Test</TITLE>
</HEAD>
<BODY>
<H1>Response Test</H1>
<TABLE>
<tr><td><b>First Name:</b></td><td><%= FirstName %></td></tr>
<tr><td><b>Middle Initial:</b></td><td><%= MiddleInitial %></td></tr>
<tr><td><b>Last Name:</b></td><td><%= LastName %></td></tr>
<tr><td><b>Address:</b></td><td><%= Address %></td></tr>
<tr><td><b>City:</b></td><td><%= City %></td></tr>
<tr><td><b>State:</b></td><td><%= State %></td></tr>
<tr><td><b>Phone Number:</b></td><td><%= PhoneNumber %></td></tr>
<tr><td><b>Fax Number:</b></td><td><%= FaxNumber %></td></tr>
<tr><td><b>EMail:</b></td><td><%= EMail %></td></tr>
<tr><td><b>Birth Date:</b></td><td><%= BirthDate %></td></tr>
</TABLE>
</BODY>
</HTML>
The complete code of /app1/response1.asp
Best record = 8.28 ms/page
2.2 Use Response.Write to output each line of HTML code
There is a lot of good literature out there that states that the preceding inline markup approach should be avoided because it results in an operation called a context switch. This operation occurs when the type of code processed by the web server changes (from sending pure HTML to script processing, or vice versa), and this switch takes a certain amount of time. After many programmers learn this, their first reaction is to use the Response.Write function to output each line of HTML code:
Copy the code code as follows:
...
Response.Write(<html>)
Response.Write(<head>)
Response.Write( <title>Response Test</title>)
Response.Write(</head>)
Response.Write(<body>)
Response.Write(<h1>Response Test</h1>)
Response.Write(<table>)
Response.Write(<tr><td><b>First Name:</b></td><td> & FirstName & </td></tr>)
Response.Write(<tr><td><b>Middle Initial:</b></td><td> & MiddleInitial & </td></tr>)
...
/app1/response2.asp fragment
Best record = 8.28 ms/page
Response time = 8.08 ms/page
Difference = -0.20 ms (2.4% reduction)
The performance improvement we saw compared to the inline markup version was so small it was simply surprising. This may be because there are many more function calls in the page. However, this method has a bigger disadvantage. Since the HTML code is embedded in the function, the script code becomes very lengthy and inconvenient to read and maintain.
2.3 Use wrapper functions
Response.Write does not add CRLF (Carriage Return - Line Feed, Carriage Return - Line Feed) at the end of the text line, which is the most disappointing aspect of using the above method. Although the HTML code has been well formatted on the server side, what you see in the browser is still only one long line of code. But this problem was not the only disappointment. People soon discovered that there was no Response.WriteLn function that could automatically add CRLF. A natural reaction is to create a wrapper function for Response.Write and add CRLF after each line:
...
writeCR(<tr><td><b>First Name:</b></td><td> & FirstName & </td></tr>)
...
SUB writeCR(str)
Response.Write(str & vbCRLF)
END SUB
/app1/response4.asp fragment
Best record = 8.08 ms/page
Response time = 10.11 ms/page
Difference = +2.03 ms (25.1% increase)
The result is a significant decrease in performance. Of course, this is mainly because this method doubles the number of function calls, and its impact on performance is very obvious. This usage should be avoided at all costs, CRLF results in two extra bytes at the end of each line, and these two bytes are useless for the browser to display the page. In most cases, the beautiful format of the browser-side HTML code just makes it easier for your competitors to read and understand the design of the page.
2.4 Merge multiple Response.Write
Ignoring the last test about encapsulating functions, the next logical step would be to merge all strings from separate Response.Write statements into one statement, thereby reducing the number of function calls and improving the efficiency of the code.
Copy the code code as follows:
Response.Write(<html> & _
<head> & _
<title>Response Test</title> & _
</head> & _
<body> & _
<h1>Response Test</h1> & _
<table> & _
<tr><td><b>First Name:</b></td><td> & FirstName & </td></tr> & _
...
<tr><td><b>Birth Date:</b></td><td> & BirthDate & </td></tr> & _
</table> & _
</body> & _
</html>)
/app1/response3.asp fragment
Best record = 8.08 ms/page
Response time = 7.05 ms/page
Difference = -1.03 ms (12.7% reduction)
This is by far the best method.
2.5 Combine multiple Response.Write and add CRLF at the end of each line
Some people are also very concerned about whether their HTML code looks beautiful on the browser side, so we add a carriage return at the end of each line of HTML code, using the vbCRLF constant. The other test codes are the same as the above example.
...
Response.Write(<html> & vbCRLF & _
<head> & vbCRLF & _
<title>Response Test</title> & vbCRLF & _
</head> & vbCRLF & _
...
/app1/response5.asp fragment
Best record = 7.05 ms/page
Response time = 7.63 ms/page
Difference = +0.58 ms (8.5% increase)
The result is a slight decrease in performance, probably due to the addition of string concatenation operations and the increase in output text.
2.6 Comments
Based on the results of the above ASP output test, we come to the following encoding rules:
Avoid using embedded ASP too much.
Combine as many Response.Write statements as possible into a single statement.
Never encapsulate Response.Write just to add CRLF.
If you want to format HTML output, add CRLF directly after the Response.Write statement.
Outline: What is the most efficient way to output content dynamically generated by ASP? What is the best way to extract a database recordset? This article tests nearly 20 common problems in such ASP development. The time displayed by the test tool tells us: these problems that are usually taken for granted are not only worthy of attention, but also have unexpected secrets hidden inside.
1. Test purpose
The first part of this article examines some basic issues in ASP development and gives some performance test results to help readers understand what impact the code placed on the page has on performance. ADO is a universal, easy-to-use database interface developed by Microsoft. It turns out that interacting with the database through ADO is one of the most important applications of ASP. In the second part, we will study this issue.
The functionality provided by ADO is quite extensive, 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 decided that the main purpose of this section is to find out what is the optimal configuration for operating ADO recordsets. However, even after narrowing the scope of the problem, we still face great difficulties, because ADO can have many different ways to complete the same task. For example, recordsets can be extracted not only through the Recordset class, but also through the Connection and Command classes; even after obtaining the recordset object, there are many operation methods that may dramatically affect performance. However, as in Part One, we will try to cover the broadest possible range of issues.
Specifically, the goal of this section is to gather enough information to answer the following questions:
Should ADOVBS.inc be referenced through include?
lShould I create a separate connection object when using recordsets?
lWhat is the best way to extract a recordset?
lWhich cursor type and record locking method are the most efficient?
lShould I use a local recordset?
lWhat is the best way to set recordset properties?
lWhich method is most efficient for referencing field values in a record set?
lIs it a good way to collect output with temporary string?
2. Test environment
A total of 21 ASP files were used in this test, which can be downloaded from the back of this article. Each page is set up to run three different queries, returning 0, 25, and 250 records respectively. This will help us isolate the initialization and running overhead of the page itself from the overhead of looping through the recordset.
To facilitate testing, the database connection string and SQL command string are saved as Application variables in Global.asa. Since our test database is SQL Server 7.0, the connection string specifies OLEDB as the connection provider, and the test data comes from SQL Server's Northwind database. The SQL SELECT command extracts 7 specified fields from the NorthWind Orders table.
Copy the code code as follows:
<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 250 OrderID, & _
CustomerID, & _
EmployeeID, & _
OrderDate, & _
RequiredDate, & _
ShippedDate, & _
Freight & _
FROM[Orders]
The test server configuration was as follows: 450 Mhz Pentium, 512 MB RAM, NT Server 4.0 SP5, MDAC 2.1 (Data Access Component), and Microsoft Scripting Engine version 5.0. SQL Server is running on another machine with a similar configuration. As in the first part, we still use Microsoft Web Application Stress Tool to record the time from the first page request to the last byte received from the server (TTLB, Time To Last Byte), the time is in milliseconds. The test script called each page more than 1300 times and took about 20 hours to run. The times shown below are the average TTLB for the session. Remember, as in the first part, we are only concerned with the efficiency of the code, not its scalability or server performance.
Also note that we have enabled server buffering. In addition, in order to make all file names the same length, some file names have one or more underscores embedded in them.
3. First test
In the first test, we extracted a recordset simulating a typical scenario found in the Microsoft ASP ADO samples. 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.
Copy the code code as follows:
<% Option Explicit %>
<!-- #Include file=ADOVBS.INC -->
<%
Dim objConn
Dim objRS
Response.Write(_
<HTML><HEAD> & _
<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></HTML>)
%>
Here are the test results:
Let’s take a look at the meaning of the numbers in each column:
0 returns the TTLB (in milliseconds) required for a page of 0 records. In all tests, this value is considered the time overhead of generating the page itself (including creating objects), excluding the time of iterating through the recordset data.
25 TTLB in milliseconds to fetch and display 25 records
The TTLB in the tot time/2525 column is divided by 25, which is the total average time cost per record.
disp time/2525 column TTLB minus 0 column TTLB, then divide by 25. This value reflects the time required to display a single record while looping through the recordset.
250 Extracts and displays TTLB of 250 records.
The TTLB in the tot time/250250 column is divided by 25. This value represents the total average time cost of a single record.
disp time/250 The TTLB in column 250 is subtracted from the TTLB in column 0, and then divided by 250. This value reflects the time required to display a single record while looping through the recordset.
The above test results will be used to compare with the next test results.
4. Should ADOVBS.inc be referenced through inclusion?
ADOVBS.inc provided by Microsoft contains 270 lines of code that define most of the ADO property constants. Our example only references 2 constants from ADOVBS.inc. Therefore, in this test (ADO__02.asp) we deleted the included file reference and directly used the corresponding value when setting the properties.
objRS.CursorType = 0?' adOpenForwardOnly
objRS.LockType = 1' adLockReadOnly
You can see that the page overhead dropped by 23%. This value does not affect the fetch and display time of individual records, since changes here do not affect recordset operations within the loop. There are several ways to resolve ADOVBS.inc reference issues. We recommend using the ADOVBS.inc file as a reference and explaining the settings via comments. Remember, as noted in Part 1, moderate use of comments has minimal impact on the efficiency of your code. Another method is to copy the constants you need from the ADOVBS.inc file into the page.
There is also a good way to solve this problem, which is to make all ADO constants directly available by linking to the ADO type library. 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:
lAvoid including the ADOVBS.inc file and access and use ADO constants through other methods.
5. Should I create a separate connection object when using a recordset?
To answer this question correctly, we must analyze the test under two different conditions: first, the page has only one database transaction; second, the page has multiple database transactions.
In the previous example, 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, eliminating the additional step of initializing and configuring the Connection object in the script.
objRS.ActiveConnection = Application(Conn)
Although the Recordset object still needs to create a connection, the creation at this time is carried out under highly optimized conditions. As a result, the page overhead dropped by another 23% compared to the previous test, and as expected, the display time of a single record did not change substantially.
Therefore, our second rule is as follows:
lIf you only use one recordset, directly assign the connection string to the ActiveConnection property.
Next we check whether the above rules are still valid when the page uses multiple record sets. To test this situation, we introduce a FOR loop to repeat the previous example 10 times. In this test we will look at three variations:
First, as shown in ADO__04.asp, the Connection object is established and destroyed in each loop:
Copy the code code as follows:
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 share this object with all recordsets:
Copy the code code as follows:
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 the connection string to the ActiveConnection property in each loop:
Copy the code code as follows:
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 tearing down connection objects within a loop is the least efficient way. Surprisingly, however, assigning the connection string directly to the ActiveConnection property within the loop is only slightly slower than sharing a single Connection object.
Nonetheless, the third rule should be:
lWhen multiple record sets are used in the same page, create a single connection object and share it through the ActiveConnection property.
6. Which cursor type and record locking method are the most efficient?
In all testing so far we have only used forward-only cursors to access the recordset. There are three types of cursors provided by ADO for record sets: static scrollable cursors, dynamic scrollable cursors, and key set cursors. Each cursor provides different functions, such as accessing the previous record and the next record, whether you can see modifications to data by other programs, etc. However, a detailed discussion of the functions of each cursor type is beyond the scope of this article. The following table is a comparative analysis of various cursor types.
All other cursor types require additional overhead compared to forward-only cursors, and these cursors are generally slower within loops. Therefore, we would like to share with you the following caveat: Never think like this - well, sometimes I will use dynamic cursors, so I will always use this cursor.
The same sentiment applies to the choice of record locking method. The previous test only used the read-only locking method, but there are three other methods: conservative, open, and open batch processing. Like cursor types, these locking methods provide different functionality and control over working with recordset data.
We arrive at the following rules:
lUse the simplest cursor type and record locking method suitable for the task.
7. Which method is best to use to extract a record set?
So far we have been extracting recordsets by creating Recordset objects, but ADO also provides indirect recordset extraction methods. The following test compares ADO__03.asp and creating a recordset directly from the Connection object (CONN_01.asp):
Copy the code code as follows:
Set objConn = Server.CreateObject(ADODB.Connection)
objConn.Open Application(Conn)
Set objRS = objConn.Execute(Application(SQL))
You can see that the page overhead has increased slightly and the display time of a single record has not changed.
Let's take a look at creating a recordset object (CMD__02.asp) directly from the Command object:
Copy the code code as follows:
Set objCmd = Server.CreateObject(ADODB.Command)
objCmd.ActiveConnection = Application(Conn)
objCmd.CommandText = Application(SQL)
Set objRS = objCmd.Execute
Likewise, page overhead increases slightly without substantial change in the display time of a single record. The difference in performance between the latter two methods is small, but we have an important issue to consider.
When creating a recordset through the Recordset class, we can control the processing of the recordset with the greatest flexibility. Since the latter two methods fail to achieve overwhelming performance, we mainly consider the cursor type and record locking method returned by default. For some occasions, the default value is not necessarily the most ideal.
Therefore, unless there are special reasons to choose between the latter two methods, we recommend considering the following rules:
l Instantiate the recordset through the ADODB.Recordset class to obtain the best performance and flexibility.
8. Should I use a local recordset?
ADO allows the use of local (client) record sets. At this time, the query will extract all data in the records set. After the query is completed, the connection can be closed immediately, and local cursors can be used to access data in the future, which brings convenience to releasing the connection. Using local recordsets is important for accessing remote data services that require the data to be used offline, but will it also be helpful for ordinary applications?
Next we add the CursorLocation attribute and close the connection (CLIENT1.asp) after opening the recordset:
Copy the code code as follows:
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
In theory, this approach would benefit efficiency for two reasons: first, it avoids repeatedly requesting data through the connection when moving between records; second, it eases the Resource requirements. However, it seems from the above table that using local recordset will obviously not help improve efficiency. This may be because when using a local recordset, the cursor always becomes a static type no matter what the program settings are.
Rule 6 is as follows:
lThis should be avoided unless localization of the recordset is truly required.
10. Which method is the most efficient to use to reference field values in a record set?
10.1 Testing
So far we have been referring to field values in the recordset by name. Since this method requires finding the corresponding field every time, it is not very efficient. To demonstrate this, in the following test we reference a field's value by its index in the collection (ADO__08.asp):
Copy the code code as follows:
'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, there is also a small change in page overhead (perhaps due to a slight reduction in code). However, the improvement in display time with this approach is quite noticeable.
In the next test, we bind all fields to variables individually (ADO__09.asp):
Copy the code code as follows:
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. Please note that the display time of a single record has been reduced to less than 0.45 milliseconds.
The above scripts all require some understanding of the construction of the result record set. For example, we use field names directly in column headers to reference each field value individually. In the following test, not only the field data is obtained by traversing the field collection, but the field titles are also obtained in the same way. This is a more dynamic solution (ADO__10.asp).
Copy the code code as follows:
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, the code performance has decreased, but it is still faster than ADO__07.asp.
The next test example is a compromise of the previous two methods. We will continue to maintain the dynamic feature while improving performance by saving field references in dynamically allocated arrays:
Copy the code code as follows:
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 doesn't beat the previous best, it's faster than the first few examples, and it has the advantage of dynamically processing any set of records.
Compared with the previous test code, the following test code has been fundamentally changed. It uses the GetRows method of the Recordset object to populate the array for iteration over the data, rather than directly accessing the Recordset itself. Note that the Recordset is set to Nothing immediately after calling GetRows, which means that system resources are released as soon as possible. Also, note that the first dimension of the array represents the fields and the second dimension represents the rows (ADO__12.asp).
Copy the code code as follows:
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
DimnumRows
DimnumFlds
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 using the GetRows method, the entire recordset is extracted into an array. Although resource problems may occur when the record set is extremely large, accessing the data in a loop is indeed faster because function calls such as MoveNext and checking for EOF are cancelled.
Speed comes at a cost, now the recordset's metadata is lost. To solve this problem, we can extract the header information from the recordset object before calling GetRows; in addition, the data type and other information can also be extracted in advance. Also note that the performance advantage in testing only occurs when the record set is larger.
In the last test of this set, we use the GetString method of the recordset. The GetString method extracts the entire recordset into a large string and allows you to specify the delimiter (ADO__13.asp):
Copy the code code as follows:
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