Performance in the first part of this article, I reviewed some basic questions about ASP development and introduced some of the results of performance testing to understand how the code we put on the page might affect performance. In the second part of this series, we will explore the broadest use of proven ASP, which is to use database content interactively through ActiveX Data Objects (ADO). ADO is a common and simple database interface for Microsoft.
ADO has many functional settings, so the biggest challenge in preparing this article is to limit the scope of the test problem. Considering that reading large data sets imposes a heavy load on the Web server, I decided to confine my research to the aspect of finding the most optimized configuration for using an ADO recordset. However, this limitation raises a challenge because ADO provides a variety of ways to perform the same function. For example, a recordset can be recovered from the recordset class, or it can be recovered from the connection and command classes. In addition, once you have a recordset, there are a number of options that dramatically affect performance. Therefore, as in the first part, I will address as many specific issues as possible.
Objective
The purpose of my research is to get enough information to find the answers to the following questions:
* Should you use adovbs.inc include files?
* Should you create a separate connection object when using a recordset?
* What is the best way to restore a recordset?
* Which of the types of pointers and locks are most effective?
* Should you use a disconnected recordset?
* What is the best way to set Recordset (Recordset) properties?
* What is the most efficient way to reference a field value in a recordset?
* Can you use temporary strings to better replace buffers?
How is the test set up?
For testing in this study, we have assembled 21 ASP pages (included in this download). Each page is configured to return a recordset run with 3 different queries, with 0, 25, and 250 records in each set. This helps us isolate the problem of loading the recordset and the performance issues on the cycle of the recordset.
To meet these changing conditions, both the database connection string and the test SQL string are stored as application variables in the Global.asa. Because our test database runs on Microsoft SQL Server 7.0, our connection string specifies that OLE DB is the current database as the connection provider, the Northwind sample database (included in the SQL server). The SQL SELECT statement requires 7 specific domains in 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") = "SELECT top 0 OrderID," & _
"CustomerID," & _
"EmployeeID," & _
"OrderDate," & _
"RequiredDate," & _
"ShippedDate," & _
"Freight" & _
"From [Orders]"
End Sub
</script >
' Alternate SQL Records
Application ("SQL") = "SELECT Top OrderID," & _
"CustomerID," & _
"EmployeeID," & _
"OrderDate," & _
"RequiredDate," & _
"ShippedDate," & _
"Freight" & _
"From [Orders]"
' Alternate SQL Records
Application ("SQL") = "SELECT Top OrderID," & _
"CustomerID," & _
"EmployeeID," & _
"OrderDate," & _
"RequiredDate," & _
"ShippedDate," & _
"Freight" & _
"From [Orders]"
Our test server is a dual 450 MHz Pentium, 512MB RAM, running NT Server 4.0 SP5, MDAC 2.1 (Data access component), and version 5.0 of Microsoft Scripting engine. The SQL Server runs on a separate machine of the same specification. Like the first article, I used Microsoft's Web application Focus tool to record the time from the initial page request to the last byte of transmission (TTLB), to the millisecond level on the server. This test script runs for 20 hours and calls more than 1300 times per page. The time displayed is the average ttlb of the session. Remember, like the first article, we're just trying to deal with performance issues, not scalability and capacity issues.
Also note that we have buffers on the server. In addition, I set all the file names to the same length, so there will be one or more underscores in the file name to pad.
Begin
In the first test, we used typical scenarios in the typical Microsoft ASP ADO sample file to recover a simple recordset. In this example (ado__01.asp), we first create a connection object and then create a Recordset object. Of course, I made some changes in the script to reflect some of the good practices involved in the first part of this series.
<% 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")
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 W
