Optimizing database processing in ASP

ASP is a Web server-side development environment that provides a simple, easy to learn script (VBScript or JScript) with many built-in objects, providing a straightforward way to program. More importantly, the ASP provides ADO objects that allow programmers to easily manipulate a variety of databases to produce and run dynamic, interactive Web service applications.

At present, many domestic E-commerce sites are using ASP technology to interact with the database, to provide users with various services.

Because most of the information of the E-commerce site is stored in the database, to improve the response speed of the web, the establishment of High-performance E-commerce site, a large part of the ASP and database processing performance, this article will specifically introduce some methods of optimizing database processing.

Using the connection pool mechanism
In database processing, the most expensive resource is to establish a database connection, and the user will have a longer connection wait time. If each user accesses, re-establish the connection, not only the user will wait for a long time, and the system may be due to excessive resource consumption and stop responding. If you can reuse a previously established database connection rather than re-establish the connection every time you access it, you can work around these problems to improve the performance of your entire system. In the iis+asp processing system, the connection pool mechanism is used to ensure this.

Connection Pool principle is: The iis+asp system maintains a connection buffer pool, the establishment of a good database connection in the ASP program disconnect is a logical disconnect, and the actual physical connection is stored in the pool and be maintained. As a result, the next user accesses a database connection directly from the connection buffer pool without reconnecting the database, thus greatly increasing the response speed of the system.

In order to correctly use the connection pool mechanism, the following points must be noted:

1. In previous versions of MDAC2.0, the database driver configuration was required to use connection pool, and in future releases (such as MDAC2.1), the connection pool mechanism was used by default. Details of the configuration can be found at Microsoft's site (http://www.microsoft.com/data/).

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

2. Each database connection string parameter must be the same, or it will be considered a different connection to reconnect to the database instead of using a connection in the buffer pool. The best practice is to store the connection string in the application variable, and 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, it is recommended that you use the following methods in your program to operate the database connection, because the mechanism of the buffer pool is not available when you implicitly use a database connection:

To explicitly create a Connection object: Set conn=server.createobject ("Adodb.connection")
Establishing a database connection: Conn.Open application ("connection_string")
To explicitly close a Connection object: Conn.close

Leverage a direct OLE DB driver
In ASP, there are two ways to connect a database through ADO, one is the traditional ODBC method and the other is OLE DB. Because ADO is based on OLE DB technology, in order to support ODBC, you must establish a corresponding call conversion for OLE DB to ODBC (for example, MS OLE DB Provider for ODBC). Using direct OLE DB methods (such as MS DB Provider for SQL, Oracle), you do not need to convert to improve processing speed, while also leveraging the new features of OLE DB.

Cache ADO objects or their contents in memory
Typically, in an ASP program, there are some common information stored in a database, such as a list of provinces, commodity classifications, and so on, which is the same for every user who accesses the data. If each user accesses the to be taken out of the database, and then displayed to the user, not only will make the database server load, not fast service to more important transactions, and the Web server must continue to create ADO objects, thereby consuming a lot of resources, resulting in a lot of users almost lost the response. If you can put some commonly used information stored in memory in advance, when the user access, directly from the memory, display to the user, you can greatly reduce the system pressure, improve response speed.

For example, we can store the Recordset object that has obtained the data in the application variable, and when the user accesses, get the Recordset object from the application variable without having to establish the database connection again You can also store the data in the Recordset object in other ways, such as in an array, and then store the array in the application variable, which is read as an array when used.

It is important to note that the thread pattern must be both for an object to be stored in the application variable, and that the object that does not meet the condition must be stored in the application variable in other ways, such as converting the array, which is why the content is stored in the array.

Working with numeric sequences
In an ASP program, when reading data from such a recordset, the database column name is often used for convenience:

Response.Write rs ("Fieldnamen")

It is rarely read using the sequence of numbers that contain the database column name:

Response.Write RS (N)

In fact, in order to get the column value from the recordset, ADO must convert the column name to a sequence of numbers, so you can increase the reading speed if you use a numeric sequence directly. If you feel the use of a number sequence, the program readability is not intuitive, you can establish a constant method, such as the definition:

Const FIELDNAME1 1

This ensures that the program is readable and read faster.

Working with Database procedures
In E-commerce sites, especially the site to be traded, in order to complete the transaction, you may need to query a large number of information, used to determine right and wrong, and then update the storage. If you are writing ASP, directly in a program to do a number of database operations, not only IIS to create a lot of ADO objects, consuming a lot of resources, but also increased the burden of the database server, increased network traffic. If you define a multiple database operation process as a database procedure, you invoke it in the following way:

Connection.Execute "{Call procedurename (..)}"

This can take advantage of the powerful performance of the database, greatly reduce the pressure of the web system, and because the page content and business separation, management and maintenance has become more convenient.

Take advantage of the characteristics of the database
ADO is a common set of object controls that do not itself take advantage of any of the features of the database. However, if the ASP program is written, consciously consider the characteristics of the combination of the database, often can have very good results.

For example, an Oracle database server is typically parsed and optimized for executed SQL statements and stored in a SQL memory buffer that, when the next time the same SQL statement is requested, is taken out of the memory buffer and no longer analyzed for optimization, which can greatly improve performance. This requires that the same SQL statements, or parameterized SQL statements, be used when the ASP program is written:

Set cmd=server.createobject ("Adodb.command")
cmd.commandtext= "SELECT * from product where productcode=?"

Use to create run out of release
As mentioned earlier, ADO objects are very resource-consuming, so be sure to keep in mind that you only create them when you use them, and then release them as soon as you are done with them:

Set Rs=server.createobject ("Adodb.recordset")
....
Rs.close
Set rs=nothing

Using optimized SQL statements
For E-commerce sites, the most important thing is to ensure that no matter how much access users, the system should have fast enough response speed. Because in the ASP technology, ADO object consumes the resource is very big, if a SQL statement to carry on for a long time, to the whole resource will also occupy, make the system does not have enough resources to serve other users. Therefore, try to use optimized SQL statements to reduce execution time. For example, do not use statements that contain subqueries in the in statement to make the most of the index.

As long as we fully consider the problem of efficiency in the construction of our E-commerce website, and use the solution mentioned above, it will greatly improve the response speed of the website.
Turn from: Dynamic Network production guide www.knowsky.com