Optimize database processing in ASP

ASP is a web server development environment. It provides a simple and easy-to-learn script (VBScript or JScript) with many built-in objects, this provides a simple programming path. More importantly, ASP provides the ADO objectProgramUsers can easily operate various databases to generate and run dynamic and interactive web service applications.

Currently, many e-commerce sites in China use ASP technology to interact with databases and provide users with various services.

Most of the information of e-commerce sites is stored in the database. To improve the web response speed and build a high-performance e-commerce site, most of the information depends on the processing performance between ASP and the database, this article will introduce some methods to optimize database processing.

Use connection pool mechanism
In database processing, the biggest resource cost is to establish a database connection, and the user still has a long connection wait time. If a user re-establishes a connection during access, not only does the user have to wait for a long time, but the system may stop responding because of excessive resource consumption. If you can reuse the previously established database connection instead of re-establishing the connection during each access, these problems can be well solved to improve the performance of the entire system. In the IIS + ASP processing system, the connection pool mechanism is used to ensure this.

The principle of connection pool is that a connection buffer pool is maintained in the IIS + ASP system, and the established database connection is closed logically in the ASP program, the actual physical connection is stored in the pool and maintained. In this way, when the next user accesses the database, a database connection is directly obtained from the connection buffer pool without re-connecting to the database. Therefore, the system response speed can be greatly improved.

To correctly use the connection pool mechanism, note the following:

1. In versions earlier than mdac2.0, the connection pool can be used only after the database driver is configured. In later versions (such as mdac2.1), the connection pool mechanism is used by default. For detailed configuration, see the Microsoft Site (http://www.microsoft.com/data ).

By the way, it is best to use the driver provided by Microsoft when using Oracle databases.

2. The connection string parameters of each database must be the same; otherwise, the database will be reconnected as a different connection instead of using the connection in the buffer pool. The best practice is to store the connection string in the application variable. All programs use the value of the application variable when establishing the connection.

3. In order to better use and maintain the connection buffer pool, we recommend that you use the following methods in the program to operate on the database connection, because the buffer pool mechanism cannot be used during implicit database connection:

Explicitly create a connection object: Set conn = server. Createobject ("ADODB. Connection ")
Create a database connection: conn. open application ("connection_string ")
Explicitly close the connection object: conn. Close

Using Direct ole db driver
In ASP, you can use ADO to connect to a database. One is the traditional ODBC method and the other is the ole db method. Since ADO is based on ole db technology, in order to support ODBC, it is necessary to establish a corresponding call conversion from ole db to ODBC (such as MS oledb provider for ODBC ). The direct ole db method (such as MS oledb provider for SQL and Oracle) does not need to be converted to improve the processing speed. At the same time, the new features of OLE DB can also be used.

Cache the ADO object or its content in the memory
Usually, some common information stored in the database is involved in ASP programs, such as the province list and product category. The information is the same for each user. When a user accesses the database, the database is retrieved and displayed to the user. This will not only increase the load on the database server, but will not be able to quickly serve more important transactions, moreover, the web server must constantly create ADO objects, which consumes a large amount of resources, resulting in almost no response when many users exist. If some common information can be stored in the memory in advance, when the user accesses it, it is directly removed from the memory and displayed to the user, the system pressure can be greatly reduced and the response speed can be improved.

For example, we can store the recordset object that has obtained data in the application variable. When the user accesses the object, the recordset object is obtained from the application variable without the need to establish a database connection again; you can also store the data in the recordset object in other ways, such as storing the data in an array, and then storing the array in the application variable to read the data in an array.

Note that to store an object in the application variable, the thread mode must be both. For objects that do not meet this condition, other methods must be used, for example, the method of converting to an array is stored in the application variable, which is also the reason why the content is stored in the array.

Use number sequence
When reading data from A recordset in an ASP program, the following methods are often used for convenience:

Response. Write RS ("fieldnamen ")

The sequence of digits where the database column name is located is rarely used for reading. That is:

Response. Write RS (N)

In fact, in order to get the column value from recordset, ADO must convert the column name to a numerical sequence. Therefore, if you use a numerical sequence directly, the reading speed can be improved. If you feel that the program is not visually readable after using a number sequence, you can use the constant creation method, such as definition:

Const fieldname1 1

This ensures the readability and reading speed of the program.

Database use process
In e-commerce sites, especially those that need to be traded, in order to complete the transaction, a large amount of information may need to be queried multiple times to determine whether the transaction is true or false, and then updated to the database. If you directly perform multiple database operations in a program when writing ASP, not only does IIS need to create a lot of ADO objects, but also consumes a lot of resources and increases the burden on the database server, increases network traffic. If you define multiple database operation procedures as a database process, use the following method:

Connection.exe cute "{call procedurename (..)}"

In this way, the powerful performance of the database can be used to greatly reduce the pressure on the web system, and the management and maintenance become convenient because the page content is separated from the business.

Utilize the features of the database
ADO is a set of common object controls and does not take advantage of any features of the database. However, when writing an ASP program, you can consciously consider the characteristics of the database, which may have good results.

For example, the Oracle database server usually analyzes and optimizes the executed SQL statements and stores them in an SQL Memory Buffer. When the same SQL statement is requested next time, directly extract and execute from the memory buffer, without analysis and optimization, which can greatly improve performance. This requires that the same SQL statement or parameterized SQL statement should be used whenever possible during ASP programming:

Set cmd = server. Createobject ("ADODB. Command ")
Cmd. commandtext = "select * from product where productcode = ?"

Create and release when used
As mentioned above, ADO objects consume a lot of resources. Therefore, we must keep in mind that they are created only when ADO objects are used and released immediately after they are used up. The program is as follows:

Set rs = server. Createobject ("ADODB. recordset ")
....
Rs. Close
Set rs = nothing

Use optimized SQL statements
For e-commerce websites, the most important thing is to ensure that the system responds quickly enough regardless of the number of users they visit. In ASP technology, the resources consumed by ADO objects are very large. If an SQL statement needs to be executed for a long period of time, the whole resource will be occupied, the system does not have enough resources to serve other users. Therefore, try to use optimized SQL statements to reduce execution time. For example, if you do not use a statement that contains a subquery in an in statement, make full use of the index.

As long as the efficiency problem is fully taken into account in the construction of our e-commerce website, and the solution described above will greatly improve the website response speed.