Program | Performance outline: ASP dynamically generated content in what way to output the highest efficiency? What is the best way to extract a database recordset? This article tests common problems with nearly 20 such ASP developments, and the time the test tools show tells us that the questions that are often taken for granted are not only noteworthy, but also unexpected secrets hidden.
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 the effect of the code put into the page on the 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, in the second part, we study this problem.
ADO provides a wide range of functionality, 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 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 operating an ADO recordset. However, even narrowing 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 from the Recordset class, but also through the connection and command classes, and even after the Recordset object is available, there are many operational methods that can dramatically affect performance. However, as in the first part, we will cover the widest possible range of issues.
Specifically, the objective of this section is to gather enough information to answer the following questions:
Should l be adovbs.inc by including references?
• Should I create a separate connection object when I use a recordset?
What is the best way to extract the recordset?
l What type of cursor and record locking methods are most efficient?
Should the local recordset be used?
L set recordset properties which method is best?
L What is the most efficient way to refer to a recordset field value?
• Is it a good way to collect output with a temporary string?
Second, test environment
This test uses a total of 21 ASP files that 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 of the page itself, the overhead of running it, and the overhead of iterating through recordsets.
For ease of testing, both the database connection string and the SQL command string are saved as application variables in Global.asa. Since 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.
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 the 5.0 version of the Microsoft Scripting engine. SQL Server runs on another machine that has a similar configuration. As in the first section, we still use the Microsoft Web application Stress Tool record from the first page to the time when the last byte was received from the server (Ttlb,time to the end byte), in milliseconds. The test script calls each page more than 1300 times and runs for about 20 hours, and the time shown below is the average ttlb of the session. Remember, 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 some file names.
Third, the first Test
In the first test, we simulated a 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 scripts here are optimized according to the coding rules summarized in the first part of this article.
<% Option Explicit% >
<!--#Include file= "Adovbs. INC "->
<%
Dim objconn
Dim objRS
Response.Write (_
"< HTML >< head >" & _
"< TITLE >ado test</title >" & _
")
Set objconn = Server.CreateObject ("ADODB. Connection ")
objConn.Open application ("Conn")
Objrs.close
Objconn.close
Set objRS = Nothing
Set objconn = Nothing
Response.Write ("</body >% >
Here is the test result:
Let's take a look at the meaning of each column number:
0 returns the ttlb (milliseconds) required for a 0-record page. In all tests, this value is considered to be the time cost of generating the page itself, including the creation of an object, and does not contain the time to iterate over the recordset data.
25 ttlb with 25 records extracted and displayed in milliseconds
The tot time/25 "25" column ttlb divided by 25, which is the total average time cost for each record.
Disp TIME/25 The ttlb of the "25" column minus the ttlb of the "0" column, then divided by 25. This value reflects the time it takes to display a single record when the Recordset is being cycled.
250 extract and display the ttlb of 250 records.
The tot time/250 "250" column ttlb divided by 25, which represents the total average time cost for a single record.
Disp time/250 the "250" column of the Ttlb minus the "0" column ttlb, divided by 250. This value reflects the time it takes to display a single record when the Recordset is being cycled.
The test results above will be used to compare with the next test result.
Should adovbs.inc be referenced by including references?
The adovbs.inc provided by Microsoft contains 270 lines of code that define most of the ADO property constants. Our example references only 2 constants from the Adovbs.inc. So in this test (ado__02.asp) We removed the include file references, and set the properties directly using the corresponding values.
You can see that the page overhead is down by 23%. This value does not affect the extraction and display time of a single record, because the changes here do not affect the recordset operation within the loop. There are a number of ways to resolve adovbs.inc reference problems. We recommend that the Adovbs.inc file be used as a reference, and that it be explained by annotations when set. Keep in mind, as the first part points out, that proper use of annotations has minimal impact on the efficiency of the Code. Another approach 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 the problem, which is to make all the 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"
Uuid= "00000205-0000-0010-8000-00AA006D2EA4"
Name= "ADODB Type Library"-->
Therefore, our first rule is:
L avoid including adovbs.inc files, access and use ADO constants in other ways.
Should you create a separate connection object when using recordsets?
To correctly answer this question, we must analyze the tests under two different conditions: first, the page has only one database transaction; second, the page has multiple database transactions.
In the previous precedent, we created a separate connection object and assigned it to the recordset's ActiveConnection property. However, as ado__03.asp shows, 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, the creation at this time is done under highly optimized conditions. As a result, the page overhead has dropped by 23% compared to the previous test, and as expected, the display time for individual records has not changed materially.
Therefore, our second rule is as follows:
L Assign the connection string directly to the ActiveConnection property if only one recordset is used.
We then check whether the above rules are still valid when the page uses more than one recordset. To test this scenario, we introduce a for loop that repeats the precedent 10 times. In this test, we will look at three different changes:
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 the 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, the connection string is assigned to the ActiveConnection property in each loop:
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. Amazingly, however, assigning a connection string directly to the ActiveConnection property within a loop is a little slower than sharing a single Connection object.
Nevertheless, the third rule should be:
L Create a single Connection object and share it with the ActiveConnection property when more than one recordset is used on the same page.
What type of cursor and record locking methods are most efficient?
All of the tests so far have used only "forward only" Grand to access the recordset. ADO provides more than three types of cursors for recordsets: Statically scrollable cursors, dynamically scrollable cursors, keyset cursors. Each cursor provides different capabilities, such as accessing the previous record and the last record, and seeing if other programs are modifying the data. However, it is beyond the scope of this article to specifically discuss the usefulness of each type of cursor, which is a comparative analysis of various cursor types.
All other cursor types require additional overhead compared to "forward only" types of cursors, and these cursors are generally slower in the loop. Therefore, we would like to share with you the following caveat: Never think so--"well, sometimes I use a dynamic cursor, so I always use this cursor." ”
The same view also applies to the selection of record locking methods. The previous test used only the read-only locking method, but there are three other ways: Conservative, open, open batch mode. As with cursor types, these locking methods provide different capabilities and control over the processing of recordset data.
We come to 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 have been extracting recordsets by creating Recordset objects, but ADO also provides an indirect method of Recordset extraction. The following tests compare ado__03.asp and create recordsets (conn_01.asp) directly from the Connection object:
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 individual records.
Now let's take a look at creating a Recordset object directly from the command object (cmd__02.asp):
Similarly, page overhead also increases slightly, while the display time of individual records does not change in nature. The difference in performance between these two methods is small, but we still have one important issue to consider.
When you create recordsets from the Recordset class, we are able to control how recordsets are handled with the greatest flexibility. Since the following two methods do not have overwhelming performance, we mainly consider the default return cursor types and record locking, for some occasions, the default value is not necessarily the most ideal.
Therefore, unless you need to select the following two methods for special reasons, we recommend that you consider this rule:
L Instantiate recordsets through the Adodb.recordset class for the best performance and flexibility.
Should you use a local recordset?
ADO allows the use of a local (client) recordset, at which point the query extracts all the data in the recordset, and the connection can be closed immediately after the query completes, and the data is later accessed using a local cursor, 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 ordinary applications?
Let's add the CursorLocation property and close the connection after the recordset is opened (client1.asp):
In theory, this approach is beneficial for the following two reasons: first, it avoids repeatedly requesting data through the connection while moving between records; second, it eases resource requirements because of the ease with which the connection can be released. However, the use of local recordsets from the table above appears to be less useful for improving efficiency. This may be because when you use a local recordset, the cursor always becomes a static type, regardless of what the program sets.
The 6th rule is as follows:
L Avoid using a recordset unless you do require it to be localized.
What is the most efficient way to refer to a recordset field value?
10.1 Test
So far we've been referencing field values in a recordset by name. Because this method requires every time to find the appropriate field, it is not efficient. To prove this, in the following test we refer to its value (ado__08.asp) by the index of the field in the collection:
As expected, there is a slight change in the cost of the page (perhaps because the code is slightly reduced). 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):
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 as 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 for a single record has been lowered below 0.45 milliseconds.
The above script requires 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 individually. In the following test, not only is the field data obtained by traversing the field collection, but also the field headings are obtained in the same way, which is a more dynamic scheme (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 as 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 fallen, but it's still faster than ado__07.asp.
The next Test example is a compromise of 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 as 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
Although it cannot be more than the previous best, it is faster than the first few examples, and it has the advantage of dynamically processing any recordset.
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 through the data rather than directly accessing the recordset itself. Note that the recordset is set to nothing immediately after the call to GetRows, which frees up the system resources as quickly as possible. Also, note 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 there may be resource problems when the recordset is extremely large, it is faster to iterate through the data because of the cancellation of function calls such as MoveNext and checking for EOF.
Speed is a cost, and now the recordset's metadata has been lost. To solve this problem, we can extract the header information from the Recordset object before calling GetRows, and the data type and other information can also be extracted beforehand. It is also important to note that the performance advantage of the test will only appear 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 the 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
While the speed advantage of this approach is obvious, it is only available for the simplest operation and is simply not adaptable to slightly complex data manipulation requirements.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
