Tutorials to .com

Tutorials to .com » Asp » Optimization » To improve the performance of the best choice for ASP

To improve the performance of the best choice for ASP

Print View , by: iSee ,Total views: 6 ,Word Count: 11490 ,Date: Fri, 17 Apr 2009 Time: 6:34 PM

asp developers to design their projects to obtain better performance and scalability and continuous efforts. Fortunately, there are many books and sites in this area provides a very good suggestion. However, the basis of these recommendations are from the ASP platform, the structure of the work of the conclusions, the actual improvement in performance was not the volume measurements. As a result of these recommendations need to be more complex coding process and reduce the readability of the code, developers can only run in the absence of the effect of the actual circumstances, its own measure in order to improve their performance of ASP applications whether it is worthwhile to pay these costs.

This article is divided into two major parts, I will introduce some performance test results to help the developers to determine whether a particular measure is not only the future of the project is worthwhile, and be able to update the original project. In the first part I will review the basis of a number of ASP development issues. In the second part, will involve some of the most optimized ADO function, and the results of their vb COM object and call the same function of the ASP page ADO to compare. These results are an eye-opener for people, even when it is alarming.

In this paper, we will answer the following questions:

* ASP-generated content will be written into the stream to respond to the most effective way is what?

* Buffer should be open?

* Whether it should consider the ASP code to add a comment?

* Whether the page should be clearly set the default language?

* If not, whether we should close the Session state?

* Should the script logic in subroutine and function area?

* What is the use of include files affected?

* Implement error handling will be imposed on what kind of load?

* Set up a context to deal with whether or not to affect performance?

All tests are using Microsoft's Web application key tool (WAST) to carry out, which is a free tool that can be found here. WAST I created a simple test script repeatedly calls described in the following test ASP page (each more than 70,000 times). Based on the average response time for the total time the last byte (TTLB), which is the time from initial request to the tool from the server to receive the data last time. Our test server is a Pentium 166, memory for the 196MB, the client for the Pentium 450, the memory for 256MB. You might want the performance of these machines is not very high, but do not forget that we are not going to test the capacity of the server, we just want to test the server each time a page dealing with the time spent. During testing these machines do other work. WAST test scripts, test reports and test all of the ASP pages are included in the ZIP file, you can review and test themselves.
ASP-generated content will be written into the stream to respond to the most effective way is what?
The use of an ASP is the main reason for the server to generate dynamic content. Therefore, it is clear that the starting point of our testing is to determine the dynamic content will be sent to the response stream of the most appropriate way. In a wide range of choices, there are two basic types: one is the use of ASP inline tags, and the other is to use Response.Write statements.

To test these options, we have created a simple ASP page, in which the definition of a number of variables, and then insert the value of their table. Although this page is very simple not very practical, but it allows us to isolate and test a number of separate issues.

Intranet use ASP tag

First test, including the use of Intranet ASP tags <% = x%>, in which x is a variable assignment. So far, this method is the most easy to implement, and it makes the HTML part of the page to maintain an easy format to read and maintain.

<% OPTION EXPLICIT

Dim FirstName

Dim LastName

Dim MiddleInitial

Dim Address

Dim City

Dim State

Dim PhoneNumber

Dim FaxNumber

Dim EMail

Dim BirthDate

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 = "john@public.com"

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>

/ app1/response1.asp complete code

The previous best (reaction time) = 8.28 msec / page

Each row in the HTML to use Response.Write statements

Study documents a number of good proposals that avoid the use of previous methods. The main reason is that in the output of imposed pages and pages dealing with the process of reaction time, if the web server had to deal with sending pure HTML and convert between the script can occur is called the context of a conversion problem. I have heard here the majority of programmers, their first reaction is to the HTML source of each line in the Response.Write function packaging.

...

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 fragments

The previous best (reaction time) = 8.28 msec / page

Reaction time = 8.08 msec / page

Difference = -0.20 msec (reduction of 2.4%)

We can see that this method is used in conjunction with the use of the method of marking performance compared to the amount of revenue received is very small, this may be because the page to the server load a lot of small function calls. The greatest disadvantage of this approach is that the HTML is embedded in the script now, so script code has become more lengthy and more difficult to read and maintain.

Function of the use of packaging

When we try to use Response.Write statements this way, the most likely to become discouraged and found the Response.Write function is not at the end of each line put a CRLF. Therefore, when you read from the browser source code, we had a very good layout of HTML, is now not the end of the line. I think you found the next you may be even more terror: Response object not in its sister function Writeln. Therefore, an obvious response Response.Write function is to create a packaging function, in order to attach a line to each CRLF.

...

writeCR ( "<tr> <td> <b> First Name: </ b> </ td> <td>" & FirstName & "</ td> </ tr>")

...

SUB writeCR (str)

Response.Write (str & vbCRLF)

END SUB

/ app1/response4.asp fragments

The previous best (reaction time) = 8.08 msec / page

Reaction time = 10.11 msec / page

Difference = +2.03 msec (increase of 25.1%)

Of course, as a result of this method effectively doubling the number of function calls, and its impact on performance is also evident, therefore, has to avoid at all costs. Ironically, it is CRLF stream to the response line for each increase of 2 bytes, which is a browser that does not require page. Good HTML formatting done is to make it easier for your competitors to read your HTML source code and understand your design.

Response.Write will be a continuous connection to a separate statement

We do not consider the front of the packaging function of the test, the next logical step is to separate from the Response.Write statement to extract all the strings, they are connected to a separate statement, thus reducing the number of function calls greatly enhanced the performance of the page.

...

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 fragments

The previous best (reaction time) = 8.08 msec / page

Reaction time = 7.05 msec / page

Difference = -1.03 msec (reduction of 12.7%)

At present, this is the most optimized configuration.

Response.Write will be a continuous connection to a separate statement, at the end of each line to add a CRLF

Taking into account those who ask them the source code from the browser simply do not have to, I use vbCRLF constant in front of the test at the end of each line to insert a number of carriage returns, and then re-run.

...

Response.Write ( "<html>" & vbCRLF & _

"<Head>" & vbCRLF & _

"<Title> Response Test </ title>" & vbCRLF & _

"</ Head>" & vbCRLF & _

...

/ app1/response5.asp fragments

In front of the best (reaction time) = 7.05 msec / page

Reaction time = 7.63 msec / page

Difference = +0.58 msec (increase of 8.5%)

The results of running a little lower performance, which may be due to additional series and to increase the amount of characters.

Review and observation

Output from the front of the ASP test rules can be drawn:

* Avoid excessive Intranet using ASP.

* Always be connected straight into the Response.Write statements within a single statement.

* Never in the use of packaging around the Response.Write function to additional CRLF.

* If you have to format HTML output directly in the Response.Write statement attached to CRLF.

Whether the buffer should be open?
Proceedings through the script buffer

At the top of the ASP script contains Response.Buffer = True, IIS will cache the contents of the page.

<% OPTION EXPLICIT

Response.Buffer = true

Dim FirstName

...

/ app1/buffer__1.asp fragments

The previous best (reaction time) = 7.05 msec / page

Reaction time = 6.08 msec / page

Difference = -0.97 msec (lower 13.7%)

Performance has been greatly enhanced. But so can a better.

Start buffer through the server configuration

Although IIS 5.0 in the buffer is activated by default, but also in the IIS 4.0 must be to start it manually. To find the site at this time Properties dialog box, where, from the Home Directory tab, select the Configuration button. And then "App options" select "enable buffering". For this test, Response.Buffer statements were removed from the script.

Previous best = 7.05 msec / page

Reaction time = 5.57 msec / page

Difference = -1.48 msec (lower 21.0%)

At present, this is the fastest we get a response than we had the best reaction time in case of 21% would also like to reduce. From now on, we have to test after this reaction time as a reference value.

Review and Observation

Buffer is a good way to improve performance, so the server's buffer set to default values is necessary. If for some reason, the page can not be run correctly to make the buffer only needs to Response.Buffer = False command. The disadvantage is that a buffer in the processing before the entire page, the user from the server can not see anything. Therefore, in dealing with complex pages, the occasional call to update a user Response.Flush be a good idea.

Now in our rules added to a: always open the server settings through the buffer.

Whether it should consider the ASP code to add a comment?
Most HTML developers know HTML Notes contains not a good idea, first of all to increase the size of data, followed by their only other developers to provide information on your organization's information page. However, Notes on the ASP page then? They never leave the server, but it does increase the size of the page, it is necessary to break down with ASP.

In this test, we increased the 20 notes, 80 characters each, a total of 1600 characters.

<% OPTION EXPLICIT

'------------------------------------------------- ------------------------------

... 20 lines ...

'------------------------------------------------- ------------------------------

Dim FirstName

...

/ app2/comment_1.asp fragment

Baseline = 5.57 msec / page

Reaction time = 5.58 msec / page

Difference = +0.01 msec (0.1%)

Test results are staggering. Although the notes document itself is almost twice as much, but they exist and the reaction time did not give a great impact. So that we can follow the following rules:

As long as the use of appropriate, ASP Notes on the properties of very small or no impact.

Whether the page should be clearly set the default language?
To deal with vbscript is the default IIS settings, but I see that in most cases or use <% @ LANGUAGE = VBSCRIPT%> statement will be clear language set to VBScript. Our next test will test the existence of this statement have any effect on performance.

<% @ LANGUAGE = VBSCRIPT%>

<% OPTION EXPLICIT

Dim FirstName

...

/ app2/language1.asp fragment.

Reference value = 5.57 msec / page

Reaction time = 5.64 msec / page

Difference = +0.07 msec (increase of 1.2%)

Can see that the statement contains language to have a minor performance impact. Therefore:

* Set up the server configuration to the default language and the language used on site to match.

* Unless you use non-default language, do not set the language of the statement.

If not, whether we should close the Session state?
Avoid the use of the Session context IIS a number of reasons, those who can become an article in the Independent. We are now trying to answer the question is when the page is no need to close the Session context is helpful to improve the performance. In theory it should be yes, because so patients do not need to use the page that the context of the Session.

With the same buffer, Session state configuration, there are two methods: through the script and the adoption of server settings.

Close Session context through the script

For this test, it is necessary to close the page in the context of the Session, I add a statement of Session state.

<% @ ENABLESESSIONSTATE = FALSE%>

<% OPTION EXPLICIT

Dim FirstName

...

/ app2/session_1.asp fragment.

Reference value = 5.57 msec / page

Reaction time = 5.46 msec / page

Difference = -0.11 msec (lower 2.0%)

Only through such a small effort has been good progress. Now look at the second part.

Close Session through the server configuration context

To close the Session in the context of the server, go to the Properties dialog box site. In the Home Directory tab, select Configuration button. And then "App options" cancel "enable session state" option. We ENABLESESSIONSTATE statement in the absence of running test cases.

Reference value = 5.57 msec / page

Reaction time = 5.14 msec / page

Difference = -0.43 msec (lower 7.7%)

This is another performance improved significantly. Therefore, we should be the rules: the case of the need, always in the page or application level to close the Session state.

Option Explicit use of the substance of the performance would change?
ASP in a top of the page to set the Option Explicit requires that all variables in the use of prior conduct must be a statement on the page. There are two reasons for this. First of all, the application can be dealt with faster access to variables. Secondly, it can prevent us from accidentally using the wrong variable name. In this test, we remove the reference and the Option Explicit statement variables Dim.

Reference value = 5.57 msec / page

Reaction time = 6.12 msec / page

Difference = +0.55 msec (9.8% increase),

Although there are some lines of code removed from the page, response time is still increased. So, although the use of Option explicit time sometimes, but in terms of performance there is very significant results. Therefore, we can add a rule: always use the VBScript in Option explicit.

Should the script logic in subroutine and function area?
Functions and subroutines used to the organization and management of code is a good way, especially when a code page in many areas in use. The disadvantage is that an increase in the system doing the same job an extra function call. Subroutines and functions to another issue is the scope of variables. In theory, a function in the specified variable region more effective. Now we take a look at how these two aspects play a role.

Response.Write statements into the subroutine

This test only Response.Write statements into a subroutine area.

...

CALL writeTable ()

SUB writeTable ()

Response.Write ( "<html>" & _

"<Head>" & _

...

"<Tr> <td> <b> EMail: </ b> </ td> <td>" & EMail & "</ td> </ tr>" & _

"<Tr> <td> <b> Birth Date: </ b> </ td> <td>" & BirthDate & "</ td> </ tr>" & _

"</ Table>" & _

"</ Body>" & _

"</ Html>")

END SUB

/ app2/function1.asp fragment

Reference value = 5.57 msec / page

Reaction time = 6.02 msec / page

Difference = +0.45 msec (8.1% increase)

With the expected, the subroutine call to the page an additional burden.

All the scripts into the subroutine in

In this test, Response.write statement with all variables into a subroutine declaration area.

<% OPTION EXPLICIT

CALL writeTable ()

SUB writeTable ()

Dim FirstName

...

Dim BirthDate

FirstName = "John"

...

BirthDate = "1/1/1950"

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>")

END SUB

/ app2/function2.asp fragment

Reference value = 5.57 msec / page

Reaction time = 5.22 msec / page

Difference = -0.35 msec (6.3% lower)

Very interesting! Although the function of the variable to the scope of the additional function call, but in fact has increased the performance. We can also add the following rules:

* In a page, if the code to be used more than once, it will be enclosed function area code.

* The appropriate time, a statement to the function of the variable range.

What is the use of impact include file?
ASP programming is an important function that contains code from other pages. The adoption of this feature, programmers can be shared across multiple page function, make the code easier to maintain. Shortcomings is that the server must be assembled from multiple sources page. Include the following documents is to use two tests.

The use of inline code Include files

In this test, there is a small section of code was moved to a Include file:

<% OPTION EXPLICIT

Dim FirstName

...

Dim BirthDate

FirstName = "John"

...

BirthDate = "1/1/1950"

%>

<! - # Include file = "inc1.asp" ->

/ app2/include_1.asp fragment

Reference value = 5.57 msec / page

Reaction time = 5.93 msec / page

Difference = +0.36 msec (6.5% increase)

This is not surprising. Include document the use of a load.

Include the use of function areas in the document

Here, the code is packed in a subprogram Include in document. Include reference is carried out in the top of the page in the ASP script subroutine calls the appropriate place.

<% OPTION EXPLICIT

Dim FirstName

...

Dim BirthDate

FirstName = "John"

...

BirthDate = "1/1/1950"

CALL writeTable ()

%>

<! - # Include file = "inc2.asp" ->

/ app2/include_2.asp fragment

Reference value = 5.57 msec / page

Reaction time = 6.08 msec / page

Difference = +0.51 msec (9.2% increase)

This impact on the performance of functions calls than larger. Therefore: only when the code-sharing between the page when using Include files.

Implementation of error handling will be the formation of much of the load?
For all real applications, the error handling is necessary. This test, by calling On Error Resume Next function to call the error handler.

<% OPTION EXPLICIT

On Error Resume Next

Dim FirstName

...

/ app2/error_1.asp fragment

Reference value = 5.57 msec / page

Reaction time = 5.67 msec / page

Difference = 0.10 msec (1.8% increase)

You can see, handle error brings the price. We can propose the following: only occur beyond the test or control the situation outside the error handle when using. A most basic example is the use of access to other resources, such as ADO or COM object of FileSystem object.

Whether to set up a context to deal with an impact on performance?
When an error occurs, the page context to set up a deal to allow the script to reverse the operation. This is done through the use of the page to set up to deal with the statement.

<% @ TRANSACTION = REQUIRED%>

<% OPTION EXPLICIT

Dim FirstName

...

/ app2/transact1.asp fragment

Reference value = 5.57 msec / page

Reaction time = 13.39 msec / page

Difference = +7.82 msec (140.4% increase)

Ah! This is true most dramatic results. Therefore, please note the following rules: only when two or more operations to be implemented as a unit, to deal with the context of use.


Conclusion
The importance of the first part of this article is that the cumulative number of small things. In order to emphasize this issue, I set up a final test, in which we had previously tested but appears to have no adverse impact on all operations. I included a lot of Response.Write statements, closed the buffer, set the default language Option Explicit references removed and an error handler initialization.

<% @ LANGUAGE = VBSCRIPT%>

<%

On Error Resume Next

FirstName = "John"

...

BirthDate = "1/1/1950"

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> Birth Date: </ b> </ td> <td>" & BirthDate & "</ td> </ tr>")

Response.Write ( "</ table>")

Response.Write ( "</ body>")

Response.Write ( "</ html>")

%>

/ app2/final_1.asp fragment

Reference value = 5.57 msec / page

Reaction time = 8.85 msec / page

Difference = +3.28 msec (58.9% increase)

May sound obvious, but more important to understand that we placed the code on the page will affect the performance. Page is sometimes a small change will greatly increase response time.

Rules summary
* Avoid excessive Intranet using ASP.

* Always be connected straight into the Response.Write statements within a single statement.

* Never in the use of packaging around the Response.Write function to additional CRLF.

* If you have to format HTML output directly in the Response.Write statement attached to CRLF.

* Always set to open through the server buffer.

* As long as the use of appropriate, ASP Notes on the properties of very small or no impact.

* Set up the server configuration to the default language and the language used on site to match.

* Unless you use non-default language, do not set the language of the statement.

* The use of VBScript always Option explicit.

* In the case of do not need, always in the page or application level to close the Session state.

* Only when the code-sharing between the page when using Include files.

* In a page, if the code to be used more than once, it will be enclosed function area code.

* The appropriate time, a statement to the function of the variable range.

* Only occur beyond the test or control the situation outside the error handle when using.

* Only when two or more operations to be implemented as a unit, only to deal with the use of context.

Look back now, there are many issues that can be used as the principle of universality:

* To avoid redundancy - do not set the default state of those attributes have been set up.

* Limit the number of function call.

* To reduce the scope of the code.

In the second part of this article, we will explore a number of ADO and COM objects of the issue in depth.


In the first part of this article, I reviewed the development of the ASP to some of the basic issues, introduced a number of performance tests in order to understand our place in the code page may result in operating performance, what kind of impact. In the second part of this series, we will explore demonstrated the most extensive use of ASP, that is, through the ActiveX Data Objects (ADO) interactive content using the database. ADO is Microsoft's database of common and simple interface.

ADO a lot of feature set, so to prepare this article is the biggest challenge when testing restrictions on the scope of the problem. Read large data taking into account the web server for the assembly to exert a lot of load, I have decided to study the limitations in the use of ADO recordset to find the most optimal allocation of the area. However, this restriction is a challenge, because the ADO for the implementation of a function provided with a variety of ways. For example, records can be set to recover from the Recordset category, also available from the Connection and Command class to resume. In addition, once you have set a record, then there are many options will dramatically affect performance. Therefore, with the first part, I will do everything possible to involve a number of specific issues and more.

Purpose
The purpose of my study is to obtain enough information to find answers to the following questions:

* Whether the document should include the use of ADOVBS.inc?

* When using a recordset, should create a separate Connection object?

* Restore a recordset What is the best way?

* Pointer and the type of lock, which is the most effective?

* Should the use of disconnected recordsets?

* Set recordset (Recordset) attributes What is the best way?

* Focus on the domain record to quote the most effective way to value what is?

* Use of temporary string buffer can be used to replace it?

Is how to set up the test?
For the purpose of the study the test, we assembled a total of 21 ASP pages (included in the download content of this article). Each page is configured with three different sets of records to return to run the query, the records focus on the respective records 0,25,250. This can help we will be loading recordset in the record the problems and focus on the performance of the cycle on the issue of separated.

In order to meet these changing conditions, the database connection string and test the SQL string as the application of the variable stored in Global.asa. Because our test database is Microsoft SQL Server 7.0 running on, so we specify OLEDB connection string to connect as a supplier, Northwind sample database (included in the SQL server) as the current database. SQL SELECT statements require the Northwind Orders table 7 specific domain.

<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? 25 records

Application ( "SQL") = "SELECT TOP 25 OrderID," & _

"CustomerID," & _

"EmployeeID," & _

"OrderDate," & _

"RequiredDate," & _

"ShippedDate," & _

"Freight" & _

"FROM [Orders]"

'alternate sql? 250 records

Application ( "SQL") = "SELECT TOP 250 OrderID," & _

"CustomerID," & _

"EmployeeID," & _

"OrderDate," & _

"RequiredDate," & _

"ShippedDate," & _

"Freight" & _

"FROM [Orders]"

Our test server is a dual 450 MHz Pentium, 512MB of RAM, run the NT Server 4.0 SP5, MDAC 2.1 (Data Access Components) and the Microsoft Scripting Engine version 5.0. SQL server the same specifications in a separate machine to run. With the first article, I use Microsoft's Web application tools record the focus from the initial page request to transfer the last byte (TTLB) time, accurate to the millisecond-level server. The test script to run 20 hours, call more than 1,300 of each page. Shows the average session time is TTLB. To remember is that with the first article, we only attempt to address the issue of performance, rather than the issue of scalability and capacity.

Please also note that we started on the server buffer. In addition, I put all the file names are set for the same length, so the file name will have one or more underscore to pad.

Start
In the first test, we use Microsoft ASP ADO typical sample file to restore the typical scenes of a simple set of records. In this example (ADO__01.asp), we first create a Connection object, and then create a Recordset object. Of course, I made some script changes to be reflected in the first part of this series involved in a number of good practices.

<% 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>")

%>

The result is like this:


Now take a look at the first column of each figure represents:

0 on behalf of running back to 0 records of inquiries, TTLB, units of milliseconds. In all our tests, the figure used to mark the page load or load the page to create the object but not the data used in the cycle time.

25 loading and display 25 records TTLB (milliseconds).

tot time/25 TTLB records divided by 25 (milliseconds). Each record on behalf of the total average time.

disp time/25 of TTLB milliseconds minus "0" that column TTLB, and divided by 25 records. Concentrated in the record on behalf of the cycle time for each record displayed.

Loading 250 and display 250 records TTLB (milliseconds).

tot time/250 TTLB divided by the number of 250 record (milliseconds). Each record on behalf of the total average time.

disp time/250 of TTLB milliseconds minus "0" that column TTLB, and divided by 250 records. Concentrated in the record on behalf of the cycle time for each record displayed.

We will use the following test results compared with those values.

Whether the document should include the use of ADOVBS.inc?
This issue I would like to solve quickly. Microsoft provided ADOVBS.inc file contains 270 lines of code, on behalf of attributes can be applied to most of ADO constants. We are only examples cited in this paper two constants. So for this test (ADO__02.asp), I removed references to include files, and list of attributes instead of the actual number of constants.

objRS.CursorType = 0 'adOpenForwardOnly

objRS.LockType = 1 'adLockReadOnly


We can see that loading time has been reduced by 23%. This record shows that each time a different definition, because such a change in the record should not affect the concentration of the cycle. This issue there are several solutions. I recommend the use of paper as a reference ADOVBS.inc, if necessary, to specify the number of the use of the Notes. To remember, as set out in the first part of the same fear of the Notes is not necessary, because as long as the use of appropriate, they will not bring big impact on performance. Another method is the only document you need to copy the constant to the page.

To solve this problem there is a cool way to class by ADO to connect to your application, so that all the ADO constants are available. The following code to your Global.asa file, you can direct the use of all the constants.

<! - METADATA TYPE = "typelib"

FILE = "C: \ Program Files \ Common Files \ SYSTEM \ ADO \ msado15.dll"

NAME = "ADODB Type Library" ->

Or

<! - METADATA TYPE = "typelib"

UUID = "00000205-0000-0010-8000-00AA006D2EA4"

NAME = "ADODB Type Library" ->

So, here is our first rule:

* Avoid ADOVBS.inc contains documents, using other methods to use constants.




When using a recordset, should create a separate Connection object?
To answer this question correctly, the need for two different test cases, test results: The first is the implementation of a database to deal with each situation, and the second is the implementation of multiple databases to deal with each situation.

In the previous example, we have created a separate Connection object and pass it to the recordset property of ActiveConnection. But there may be just the connection string passed to this attribute, thus an additional step can be avoided, that is, in the script (ADO__03.asp) examples and configuration in a separate component:

objRS.ActiveConnection = Application ( "Conn")


Although we are still concentrated in the record creates a connection, but it is in a very optimized to create, so we are just beginning to see the start-up time than before the test reduced by 23%, the same as expected, with each records show that there is little difference in time.

Therefore, our second rule is:

* When using a single recordset, it will be delivered to the connection string properties ActiveConnection.

The following to determine when a page to create more than one record set, setting up of this logic still. To test this, I introduced the FOR cycle will repeat the previous example 10. In this test, we will study three kinds of options:

First, we each cycle Connection object to create and destroy (ADO__04.asp):

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, outside the circle to create a separate Connection object, and with each record it set to share (ADO__05.asp):

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, in each cycle will be delivered to the ActiveConnection connection string attribute (ADO__06.asp):

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


You may have guessed, in each cycle of creation and destruction of Connection object is a low efficiency. But surprisingly, only in passing in each cycle than sharing a single connection string to connect the efficiency of the target only a little bit low.

In spite of this, our first three rules are:

* In a page using a number of records set, create a Connection object, ActiveConnection properties in the re-use it.

Pointer and the type of lock, which is the most effective?
So far, we only spent all the tests only forward (Forward Only) focus on the indicators in the record cycle. However, ADO recordset is also provided for three kinds of types of indicators: Static (Static), Dynamic (dynamic) and the Keyset (Keyboard). Each of which provides additional functionality, such as forward and backward movement as well as the establishment of data when others can see that the revision of the function. However, the discussion of the meaning of these indicators is not the type to discuss the scope of this article. I left you these. The following is a comparative analysis of various types.


Similar to their Forward Only, these additional indicators are clearly caused by the greater load (ADO__03.asp). In addition these guidelines in more slowly during the cycle. I would like to share with you an advice is to avoid this kind of thinking: "I need to look from time to time, Dynamic pointer, so always use it to simply forget."

In essence, the same problem also applies to the type of lock. Previous tests had only been used for Read Only (CD) type of lock. However, there are three types of locks: Lock Pessimistic, Lock Optimistic and Lock Batch Optimistic. With the choice of indicators, these locks also focus on data processing records to provide additional functionality and control. Similarly, I will learn from each lock set up the proper use of the content left to yourself.


So guide us to consider the logic of Rule 4 is very simple: you use the most suitable for the task of the simplest type of indicator and locks.

Access to a Recordset What is the best way?
So far, we have only to restore through the Recordset recordset object. But also provides a number of ADO recordset to obtain the indirect method. The next test will be of value and ADO__03.asp directly from a Connection object to create a recordset object (CONN_01.asp) to compare.

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

objConn.Open Application ( "Conn")

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


We can see that the load there is a slight increase, indicating the time each record has not changed.

Then we look at from a Command object to create a Recordset object directly (CMD__01.asp):

Set objCmd = Server.CreateObject ( "ADODB.Command")

objCmd.ActiveConnection = Application ( "Conn")

objCmd.CommandText = Application ( "SQL")

Set objRS = objCmd.Execute


Once again, we see that there is a slight load increase, the display time for each record there is a difference between nominal. Although the two methods has little effect on performance, there is a big problem to be considered.

Category through the Recordset to create a record set for the control of how to deal with recordsets provide the greatest flexibility. Although other methods did not offer the performance of a landslide, but you will be back to what default pointer type and lock type of confusion, these for your specific needs is not necessarily optimal.

Therefore, unless some special reasons is because you need other methods, please follow the rules of Article 5: The types of cases show ADODB.Recordset recordset to get the best performance and maximum flexibility.

Should be disconnected recordset?
A disconnected ADO recordset to provide a choice of recordset to query in a move to restore all data, close the connection, use a local (or client) for mobile data pointer. It also provides an opportunity for early release of connections. To deal with this situation for long-distance data services is necessary because data on such cases must be disconnected from the database. But for ordinary use, so a whole?

CursorLocation we added the following attributes, open the recordset and close connections (CLIENT1.asp):

Set objRS = Server.CreateObject ( "ADODB.Recordset")

objRS.CursorLocation = 3 'adUseClient

objRS.ActiveConnection = Application ( "Conn")

objRS.LockType = 1 'adLockReadOnly

objRS.Open Application ( "SQL")

objRS.ActiveConnection = Nothing


In theory, this technology should lead to faster performance. There are two reasons: First of all, concentrate on moving in the record to avoid duplication through the connection request; followed through the early cancellation of connection to reduce the resource requirements. However, in the use of client-side pointer, very obviously inefficient. May be due to the customer when using the position indicator, no matter what your settings, CursorType have been amended to Static.

Rule 6 is this: unless it is a disconnected environment required to avoid the use of disconnected recordsets.

Set Recordset What is the best way to attribute?
In front of all the tests were set up through a separate attribute set to directly set the attributes of records. However, function can Recordset.Open we need all the attributes to receive additional parameters. Although each attribute, the separate lines of code easier to read and maintain, they still have to separate the implementation of a single function call to a COM interface to the collection (ADO__07.asp):

Set objRS = Server.CreateObject ( "ADODB.Recordset")

objRS.Open Application ( "SQL"), Application ( "Conn"), 0, 1

'AdForwardOnly, adLockReadOnly


These methods bring in the load on a shockingly small difference, so we have Rule 7: Do not set the record for the single attribute worried


Focus on the domain record to quote the most effective way to value what is?
So far, I have quoted are recorded with the name of the domain focus on value. This is probably a very low efficiency, because each call needs to find the domain. To prove this point, the following test is necessary to focus on the domain through a collection of records of the pointer to invoke the domain (ADO__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 we expected, very small changes in load time (the difference may be due to the code caused by the slight decrease). However, this technology shows in the effective time has brought about a marked reduction.

In the example below, we will designate a separate domain for each variable. This method avoids the cycle in the table for all to find (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 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


Up to now, the result of the formation of such an approach is the best. Each record shows that the decline has become a time .45 ms.

Now, the configuration of all the test script the results requires some understanding of recordset. For example, we have been heading in the column to the domain name encoding, refer to separate the value of these domains. The following example provides a dynamic solution, in the domain of the collection cycle, not only by the data, also the title of the domain (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


Can see that we have in terms of performance, there is a loss, but this method is faster than some ADO__07.asp.

The following test is in the final two tests between the number of compromise. Through a dynamic allocation of array references saved domain, while maintaining the flexibility of dynamic, but also to restore some performance loss.

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


Although it is not faster than the best value, but a few examples than in front of a lot faster, and there is an advantage is the ability to dynamically set any records show.

The next test, the previous program we will make a complete change, use the record set GetRows instructions to create a cycle of the array used, rather than records set in the circle itself. Note that GetRows call immediately after the recordset will be set to Nothing, so we can more quickly release system resources. In addition, attention to the first array dimension on behalf of the domain, and the second dimension representatives (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


GetRows through the use of commands, access to the entire record can be set and loaded into an array. When the resumption of large sets of records, this method may lead to resource problems, but the cycle of data more quickly, because similar to the function call MoveNext and testing EOF can be canceled.

But speed is a cost to upgrade, because the record is no longer set the metadata and data together. Around this problem, I used to call before GetRows records were set to restore the title. In addition, data types can be extracted in advance and other information. Would also like to note that in our tests, the performance advantage only in the use of larger sets of records to see when.

In this part of the final test, we go one step further and use the GetString command recordset. This method will extract the entire recordset to a string of big, allows you to specify your own 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 this approach has been close to the highest level, but it is only suitable for the most simple design, because it simply can not be used for the special circumstances of the data.

Observation
Before we begin this test prior to the implementation of each record time has been about .83 ms shock. Most of this testing method will reduce this figure by half. Although some methods provided significantly faster, but the lower the cost of flexibility.

The following rules are important for the sequence:

* When the records do not need to focus on the value in a special way to treat and can be formatted for a unified format, the use of GetString method to extract the data.
* When you are in the design to be more flexible, but they do not need to use metadata records set to work, use the GetRows method to extract the data in an array.
* When you need the flexibility of the design and meta-data, a data recovery in the cycle before your domain will be bound in the local variables. Avoid domain names quoted.
The use of temporary string buffer can be used to replace it?
This is an article I submitted on a number of comments arising from a small digress. Issues to be discussed is the use of buffers around the temporary string and use as an alternative to collect the output, thus allowing a Response.Write call only. To test, I ADO_11.asp code from the beginning, the results attached to a string, rather than have to call in each cycle of Response.Write, when the operations after the end of the call in the string Response.Write (STR__01 . asp):

Dim strTable

strTable = ""

'write headings

strTable = strTable & "<TABLE BORDER = 1> <TR>"

For i = 0 to fldCount-1

strTable = strTable & "<TH>" & fld (i). name & "</ TH>"

Next

strTable = strTable & "</ TR>"

'write data

Do While Not objRS.EOF

strTable = strTable & "<TR>"

For i = 0 to fldCount-1

strTable = strTable & "<TD>" & fld (i) & "</ TD>"

Next

strTable = strTable & "</ TR>"

objRS.MoveNext

Loop

For i = 0 to fldCount-1

Set fld (i) = Nothing

Next

strTable = strTable & "</ TABLE>"

Response.Write (strTable)


Implementation may not look very good. As many people suggested that perhaps we should use for the Space command string to designate the space, so that it does not need is always in the cycle during the re-allocation of space for themselves (STR__02.asp):

Dim strTable

strTable = Space (10000)


Space may not be as directive as the work of the proposed. Our last rule is: Do not use temporary string to collect the output.

Rules summary
Now we come to sum up these rules:

* Avoid ADOVBS.inc contains documents, using other methods to use constants.

* When using a single recordset, it will be delivered to the connection string properties ActiveConnection.

* In a page using a number of records set, create a Connection object, ActiveConnection properties in the re-use it.

* Use the most suitable for you in the simplest of tasks of the pointer and the type of lock.

* Types of cases through the show ADODB.Recordset recordset to get the best performance and maximum flexibility.

* Unless it is a disconnected environment required to avoid the use of disconnected rec


Asp Optimization Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.