Ten principles of optimizing database in Dynamic Web programming

Database access can degrade your system performance compared to a Web site without a database. But most of the time, the site and the database has a close relationship, it is the database to the site to provide a large capacity, diversity, personalization characteristics, and achieve a lot of special features.

1 do not forget to index the database. A reasonable index can improve the performance of the whole system of the database immediately. You can refer to the SQL Performance debugging books, learn how to make a reasonable index based on the query you want, and improve the query statement by index.

2 when appropriate, use stored procedures as much as possible rather than SQL queries, as the former is precompiled and runs faster. At the same time, let the database simply return the data you need, rather than returning a large amount of data to allow the ASP program to filter. In short, to fully and effectively play the powerful function of the database, let it according to our request feedback to us the most appropriate and most concise information.

3 if possible, we should use SQL Server instead of access. Because access is simply a file-based database, multiuser performance is poor. Database connections use OLE DB and non-DSN methods as much as possible because of the better concurrency performance of this connection.

4 avoids the use of DAO (Data Access Objects) and RDO (Remote data Objects) data sources because they are primarily used in single-user processing systems, and ADO (ActiveX data Objects) is designed for Web applications.

5 set up the recordset Rescordset clearly and reasonably sets the data cursor (cursort) and the Locking method (LockType). Because in different ways the ASP will manipulate the database in different ways, its execution speed is also very different, especially in the large amount of data. If you only want to traverse the data, the default cursor (forward, read-only) will provide the best performance.

6 when you reference an ADO variable, it consumes more CPU cycles. Therefore, if you refer to the database's field variables more than once in an ASP page, a good way to do this is to place the value of the field in the local variable and then call the local variable directly to compute and display the data.

7 Caching ADO Connection objects may not be a good idea. If a connection (Connection) object is stored in the Application object and is used by all ASP pages, then all pages will scramble to use the connection. However, if the connection object is stored in the Session object, a database connection is created for each user, which reduces the role of the connection pool and increases the pressure on the Web server and the database server. You can replace a cached database connection by creating and releasing an ADO object in each ASP page that uses ADO. Because the database connection pool is built into IIS, this approach is very effective, with the disadvantage that each ASP page requires some creation and release operations.

One of the most powerful and major uses of 8asp is to operate the database, and in the database operation we should be aware that "select * ..." should not be used arbitrarily. The SQL query statement in the form. You should try to retrieve the fields that you need. For example, there are 10 fields in a table, but you will only use one of these fields (name), and you should use the "select name from MyTable" instead of "select * FROM MyTable". When the number of fields is small, the difference may not be obvious, but when a table has dozens of fields, the database retrieves more data than you need. In this case you'd better not use "Select Id,name,age ... from mytable" in order to save typing time or to be afraid of finding the name of the corresponding field.

9 closes open Recordset objects and connection (Connection) objects in a timely manner. Recordset objects and connection objects consume a considerable amount of system resources, so their available quantities are limited. If you open too many Recordset objects and connect objects and finally did not close them, it may appear that the ASP program at the beginning of the run quickly, and run several times more and more slow phenomenon, and even cause the server panic. Please use the following method to close:

Myrecordset.closeset myrecordset=nothing
Set myconnection=nothing

10 Connection Database

You still use an ODBC system or file DSN to connect to the database, or you can connect using a quick OLE DB technology. Using the latter, when you move a Web file, you no longer need to modify the configuration.

OLE DB is located between the application and the ODBC layer. In an ASP page, ADO is a program located on top of OLE DB. When ADO is invoked, it is first sent to OLE DB, which is then sent to the ODBC layer. You can connect directly to the OLE DB layer, and when you do this, you will improve server-side performance. How do you connect directly to OLE DB?

If you use SQL Server 7, use the following code as the connection string:

strconnstring = "dsn= ';D river={sql SERVER};" & _
"UID=MYUID; Pwd=mypwd; "& _
"DATABASE=MYDB; Server=myserver; "

The most important parameter is the "driver=" section. If you want to bypass ODBC and use OLE DB to access SQL Server, use the following syntax:

strconnstring = "Provider=SQLOLEDB.1; Password=mypassword; "& _
"Persist security info=true; User Id=myuid; "& _
"Initial catalog=mydbname;" & _
"Data Source=myserver; Connect timeout=15 "

Why is this important?

Now you may wonder why learning this new connection method is critical? Why not use a standard DSN or System DSN method? Well, according to the tests that Wrox in their ADO 2.0 Programmer's Reference books, if you use OLE DB connectivity, you have the following performance improvements than using DSN or Dsn-less connections:

Performance comparisons:

----------------------------------------------------------------------

SQL Access
OLE DB DSN OLE DB DSN
Connection time: 18 82 Connection Time: 62 99
Repeat 1,000 time: 2900 5400 Repeat 1,000 record time: 100 950

----------------------------------------------------------------------

This conclusion is published in Wrox's ADO 2.0 Programmer's Reference. The time is in milliseconds, and the time to repeat the 1,000 records is computed in the form of the server oil standard.