In the first part, we discussed how to optimize the ASP code. In this part, we focus on data access.
In general, the data access time is interpreted than the ASP code, the compilation time is long, do not let the data retrieve become a bottleneck affecting the ASP performance.
First, talk about some older talks. For example: need to declare the variable, use response.write sql_string to debug, you want to use on Error Resume
Next to capture errors. Don't store Connection objects in Application and Session, etc., all many people know and dismissive
knowledge.
Improve data access speed includes two aspects: 1 Connect to the database. 2 Retrieve data.
Establishing an efficient connection is the first step in optimizing database access. You need to understand the concept of connecting pools (see article: Connection Pooling);
Connect the database method. In addition, the test is very important. If you want to test it yourself, you can download the WAST mentioned in the previous part, it can simulate a large number of users at the same time.
Click on the situation. Here, suppose you use the database is MS SQL Server 7.0, if the operating system is NT, then you can monitor in Performance Monitor
SQL7 user connections; if the operating system is 98, you can use SQL7's Profiler.
Below, you start discussing how to connect to the database, as well as the problem of connecting pools.
1. Using OLEDB
There are three ways to establish a connection with the database: DSN, DSN-LESS, OLEDB (see article: Three ways to connect to the database using the ADO). It is recommended to use OLEDB.
2. Release the database object as soon as possible
If you have Page1.asp and Page2.asp, where you click at the same time, the server may work like this:
First person: 1-5 lines of page1.asp,
Second: Page1.asp 1-5 lines,
First person: 6-20 lines of page1.asp,
Second: Page1.asp 1-5 lines,
Third person: Page1.asp 1-5 lines,
Fourth person: 1-5 lines of page1.asp,
Second: Page1.asp 6-20 line,
First person: 1-5 lines of page2.asp,
......
Imagine: There are thousands of people visiting your homepage, then the server will execute thousands of rows, and then return to the page of the first person request after tens of thousands of row statements. therefore, we
A feature should be handled as fast as possible, a task. Look at the following code:
<%
RS1.Open Strsql1, CNN1 'CNN1 IS Connection Object
RS2.Open Strsql2, CNN2
RS3.Open STRSQL3, CNN3
......
'Handling RS1
'Handling RS2
'Handling RS3
......
Rs1.close
RS2.Close
RS3.Close
CNN1.Close
CNN2.Close
CNN3.Close
%>
The method of this processing result set will take up resources than you think, the correct usage should be:
<%
RS1.Open Strsql1, CNN 'CNN IS Connection Object' Handling RS1
Rs1.close
CNN1.Close
......
RS2.Open Strsql2, CNN
'Handling RS2
RS2.Close
CNN2.Close
......
RS3.Open STRSQL3, CNN
'Handling RS3
RS3.Close
CNN3.Close
......
%>
Maybe you will say: In the process of RS2, I also need the value of RS1, so I can't turn off RS1 before processing RS2. So solving this problem better method is to use
GetRows and GetString, will have a detailed description thereof later.
The release object as soon as possible is the premise of ensuring database connection reuse. (See Article: Letting the Database Connection more effective)
3. Create a Connection object and take advantage of the connection pool.
Look at the following code:
'Creating Connection Object, Open Recordset
SET CNN = Server.createObject ("AdoDb.Connection")
CNN.Open StrConnection
SET RS = Server.createObject ("AdoDb.Recordset")
RS.Open Strsql, CNN
'Open Recordset directly
SET RS = Server.createObject ("AdoDb.Recordset")
Rs.Open Strsql, StrConnection
The two methods were tested in Asptoday's Enhancing Performance in ASP - Part 2, and the results showed that directly open the Recordset bit.
Creating the Connection is 23% (fast on page processing, the speed of retrieving the database is the same.) So, this article conclusion is: when working with a
Single Recordset, Pass The Connection String Into The ActiveConAnction property.
I have an objection to this: The fast code is not necessarily good code.
The first thing to explain is that the way to open the Recordset is still to create a connection to the database, but it is complete by the ADO. Second, this
Connection can only be released after the page is processed (regardless of recordset.close or set recordset = Nothing, you can't release it.
Object).
DIM RS
DIM STRCNN
STRCNN = "driver = {SQL Server}; server = .; data = pubs; uid = sa; pwd =; ole db services = -1;"
For i = 1 to 20
SET RS = Server.createObject ("AdoDb.Recordset")
rs.open "Select * from authors", strcnn
Rs.close
SET RS = Nothing
NEXT
Run the above code, in the Performance Monitor, you will find 20 connections to the database!
DIM CNN, RS
Strcn = "driver = {SQL Server}; server = .; database = public;" & _
"UID = SA; PWD =; OLE DB SERVICES = -1;" for i = 1 to 20
SET CNN = Server.createObject ("AdoDb.Connection")
CNN.Open Strcnn
SET RS = Server.createObject ("AdoDb.Recordset")
Rs.Open "Select * from authors", CNN
Rs.close
SET RS = Nothing
CNN.Close
SET CNN = Nothing
NEXT
With the above code, the number of connections only needs 2! (Only for explanation, no speed)
One point to explain: When the server is processed asp, only one is processed once, nor will it handle the entire page, and the connection without a closed will only take advantage of more
H. So, release the Connection as soon as possible, making it back to the connecting pool is a good programming habit. The connection pool is the resource shared by the server, it does not belong to a certain
ASP, nor is there a session, or an Application.
WAST is my common test tool, but the test is a means, which can not replace it. If you are interested in WAST how you are working, you can go to the home page to see.
http://webtool.rte.microsoft.com/
4. Create a Connection object for multiple Recordset
DIM CNN, RS1, RS2, R3
STRCNN = "driver = {SQL Server}; server = .; data = pubs; uid = sa; pwd =; ole db services = -1;"
SET CNN = Server.createObject ("AdoDb.Connection")
CNN.Open Strcnn
SET RS1 = Server.createObject ("AdoDb.Recordset")
Rs1.open "Select * from authors1", CNN
SET RS2 = Server.createObject ("AdoDb.Recordset")
Rs2.open "Select * from authors2", CNN
SET RS3 = Server.createObject ("AdoDb.Recordset")
RS3.Open "Select * from authors3", CNN
.....
The reason is very simple, this approach does not need to create a connection for each Recordset, as long as it is passed, it is enough.
5. Problems with multiple databases.
In most applications, we only operate a database, but sometimes it will also encounter the case with multiple database connections. Below we discuss two ways:
'First: Two Connection, two records.
SET CNN1 = Server.createObject ("AdoDb.Connection")
SET CNN2 = Server.createObject ("AdoDb.Connection")
CNN1.Open "provider = SQLOLEDB; DATA SOURCE = 210.75.56.37; Initial Catalog = PUBS; User
ID = COOLBEL; Password = coolbel.com; "
CNN2.Open "provider = sqloledb; data source = 210.75.56.37; initial catalog = coolbel; user
ID = COOLBEL; Password = coolbel.com; "
SET RS1 = Server.createObject ("AdoDb.Recordset")
SET RS2 = Server.createObject ("AdoDb.Recordset")
Rs1.open "Select * from authors", CNN1
Rs2.open "Select * from myTable", CNN2
......
'Second: a connection, two recordsets.
SET CNN = Server.createObject ("AdoDb.Connection")
CNN.Open "provider = SQLOLEDB; DATA SOURCE = 210.75.56.37; user ID = coolbel; password = coolbel.com;"
SET RS1 = Server.createObject ("AdoDb.Recordset")
SET RS2 = Server.createObject ("AdoDb.Recordset")
RS1.Open "Select * from pub..AUTHHORS", CNN
rs2.open "Select * from coolbel..mytable", CNN
......
The second method is extremely inexpensive, not only low speed, but also waste resources. The root cause of low efficiency is that this connection does not utilize the connection pool, making each request
To recreate the connection to the database.
Click! Test the first code Click! Test the second code (Coolbel is unstable, you need to test multiple times.)
6. For Disconnected Recordset, don't forget to make ACTIVECONNECTION = Nothing
