Database's access is reduced by your system performance than a website without a database. However, in most cases, websites and databases have a unsatisfactory relationship, which is exactly the database to the site provides large capacity, diversity, personalization, etc., and realizes a lot of special features.
1 Don't forget to give the database index. Reasonable index can immediately significantly improve the performance of the entire system of the database. You can refer to the SQL performance debug books, learn to make indexes according to the required query method and improve the query statement according to the index mode.
2 In appropriate, use the storage process as much as possible instead of SQL inquiry, because the former has passed the precompiled, and the running speed is faster. Also let the database returns only those data you need, rather than returning a lot of data and lets the ASP program filter. In short, we must fully and effectively play the powerful function of the database, let it feedback to our most suitable and most refined information according to our requirements.
3 In the case of possible, we should use SQL Server instead of Access. Because Access is just a file-based database, multi-user performance is very poor. Database connections should be used to use OLEDB and non-DSN mode because this connection is better concurrent performance.
4 Avoid using DAO (Data Access Objects) and RDO (Remote Data Objects) data sources, because they primarily apply in a single user's processing system, the ADO (ActiveX Data Objects) is designed for web applications.
5 To establish a record set Rescordset, you should clearly set the data cursor (LockType). Because the ASP in different ways will manipulate the database in different ways, the execution speed is also very different, especially when the amount of data is large. If you only want to traverse data, then the default cursor (advance, read-only) will bring the best performance.
6. When you quote the ADO variable, it will consume more CPU cycles. Therefore, if you retrieve the field variable of the database multiple times in an ASP page, a preferred way is to place the field value first in the local variable, and then you can directly call the local variable to calculate and display data.
7 Cache ADO Connection object is not a good idea. If a connection (Connection) is stored in the Application object and is used by all ASP pages, all pages will be used to use this connection. But if the connection object is stored in the Session object, you have to create a database connection for each user, which reduces the role of the connection pool and increases the pressure of the web server and database server. You can use the ADO page to use ADO to replace cache database connections. Because IIS built a database connection pool, this method is very effective, and the disadvantage is that some creation and release operations need each ASP page.
8ASP's most powerful and main use is to operate on the database, we have to pay attention to in database operation: Do not use the SQL query statement in "Select * ...". You should try to retrieve those fields you need. For example, there are 10 fields in a table, but you only use one of the fields (name), which uses "Select Name from MyTable" instead of "select * from myTable". When the field number is less, the difference between the two may not be obvious, but when there are dozens of fields in a table, the database will save more data that you don't need. In this case, you should not save trouble or fear of finding the trouble of finding the corresponding field name, but should be used to use "SELECT ID, NAME, AGE ... from myTable".
9 Close the open record set object and connection object. Recording a set object and connection object consumption system resource is quite large, so their available quantities are limited. If you open too many recorded objects and connection objects, it doesn't turn off them, you may have a fast running speed when the ASP program is just starting, and more running more times is growing more and more slow, and even leads to the server. Cancer. Please use the following method to close: MyRecordSet.closset myRecordset = Nothing
SET myConnection = Nothing
10 connection database
Still using the ODBC system or file DSN to connect to the database, or to connect using a very fast OLEDB technology. Use the latter, when moving the web file, no longer need to modify the configuration.
OLEDB is located between the application and the ODBC layer. In the ASP page, ADO is a program on OLEDB. When you call ADO, first send it to OLEDB, then send it to the ODBC layer. You can connect directly to the OLEDB layer, which will increase the performance of the server. How to connect directly to OLEDB?
If you use SQLServer 7, use the following code as a connection string:
StrConnString = "DSN = ''; driver = {SQL Server};" & _
"UID = MyUID; PWD = mypwd;" & _
"Database = mydb; server = myserver;"
The most important parameter is the "driver =" section. If you want to bypass ODBC to use OLEDB to access SQL Server, use the following syntax:
StrConnstring = "provider = SQLOLEDB.1; Password = mypassword;" & _
"Persist security info = true; user id = myuid;" & _
"InTIAL CATALOG = MyDBName;" & _
"Data Source = MyServer; connection timeout = 15"
Why is this important?
Now you may be why do you have to learn this new connection method? Why don't you use standard DSN or system DSN methods? Ok, based on the test made in their ADO 2.0 programmer reference book, if you use an OLEDB connection, you can use the DSN or DSN-Less connection, there is the following performance improvement performance:
Performance comparison:
-------------------------------------------------- --------------------
SQL Access
OLEDB DSN OLEDB DSN
Connection time: 18 82 connection time: 62 99
Repeat 1,000 records: 2900 5400 Repeat 1,000 records: 100 950
-------------------------------------------------- --------------------
This conclusion published in Wrox's ADO 2.0 programmer. Time is in milliseconds, repeating 1,000 records of records are calculated in the way of server oil labels.
