OLE DB method for SQL

In the first section, we discussed how to optimize ASP code. In this section, we focus on data access.

In general, the data access time is more than the ASP code interpretation, the compilation time is long, do not let the data retrieval becomes the bottleneck that affects ASP performance.

First of all, say something corny. For example: Need to declare variable, to use Response.Write sql_string for debugging, to use on Error Resume
Next to catch the error. Do not store Connection objects in application and sessions, and so on, is a lot of people know and dismissive of often
General

Improve data access speed includes two aspects: 1 establish a connection with the database. 2 Retrieving data.

Establishing an efficient connection is the first step in optimizing database access. You need to understand the concept of the connection pool (see article: Connection pool (Connection pooling) introduction);
Method of connecting to the database. In addition, testing is very important, if you want to test yourself, you can download the wast mentioned in the previous section, it can simulate a large number of users at the same time
Click on the situation. Here, suppose you use a database that is MS SQL Server 7.0, and if the operating system is NT, then you can monitor in Performance Monitor
SQL7 Number of user connections (Counter:user connections) and SQL7 Profiler if the operating system is 98.

Here's how to set up a connection to the database, and the problem of connecting to the pool.

1. Using OLE DB

There are three ways to establish a connection to a database: Dsn,dsn-less,oledb (see article: Three ways to connect to a database with ADO). It is recommended that you use OLE DB.

2. Release database objects as soon as possible

Assuming that there are page1.asp and page2.asp, the server may work as many people click at the same time:

First person: 1-5 lines of Page1.asp,
Second person: 1-5 lines of Page1.asp,
First person: 6-20 lines of Page1.asp,
Second person: 1-5 lines of Page1.asp,
Third Person: Page1.asp's 1-5 lines,
Fourth person: 1-5 lines of Page1.asp,
Second person: 6-20 lines of Page1.asp,
First person: 1-5 lines of page2.asp,
......

Imagine: With thousands of people visiting your home page at the same time, the server executes thousands of lines, tens of thousands of lines of statements before returning to the page that the first person requested. Therefore, we
You should deal with a feature, a task, as quickly as possible. 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
%>

This method of processing result sets takes up more resources than you think, and 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

......
%>

You might say: in rs2 processing, I also need rs1 value, so I can't close rs1 before I process rs2. The better way to solve this problem is to use
GetRows and GetString will be explained in detail later.

The release of objects as soon as possible is the prerequisite to ensure the reuse of database connections. (see article: Make a database connection more efficient)

3. Create Connection objects to make the most of the connection pool.

Look at the following code:

' Create Connection Object, open the RecordSet
Set CNN = Server.CreateObject ("ADODB.") Connection ")
Cnn. Open strconnection
Set rs = Server.CreateObject ("ADODB.") RecordSet ")
Rs. Open STRSQL,CNN

' Open the 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 the direct opening of the RecordSet
Built Connection fast 23% (fast in the page processing, the speed of retrieving the database is the same. So, the conclusion of this article is: when working with a
Single Recordset,pass The connection string into the Activeconenction property.

I have a problem with this: Fast code is not necessarily good code.

The first thing to note is that the way to open the RecordSet directly still creates a connection to the database, but it is done automatically by ADO. Second, this
Connection can be freed only after the page has been processed (either Recordset.close or Set Recordset = Nothing can release the
Object).

Dim RS
Dim strcnn
strcnn = "Driver={sql Server}"; server=.;D Atabase=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 that the database connection has been created 20!

Dim Cnn,rs
strcnn = "Driver={sql Server}"; server=.;D atabase=pubs; "&_
"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

Using the above code, the number of connections only need 2! (only to illustrate the problem, regardless of speed)

Another point to note is that the server processing ASP is not only one at a time, nor is the entire page processed at once, no closed connection will only occupy more
of resources. So it's good programming practice to release Connection as soon as possible and make it back into the connection pool. A connection pool is a resource shared by a server that does not belong to a
ASP, also does not belong to a session, or some application.

Wast is my common test tool, but testing is a means to replace the actual. If you are interested in the way wast works, you can go to its homepage to see.
http://webtool.rte.microsoft.com/

4. Create a Connection object for multiple recordsets

Dim CNN,RS1,RS2,R3
strcnn = "Driver={sql Server}"; server=.;D Atabase=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 simple truth is that this practice does not require creating a connection for each RecordSet, as long as passing a reference is enough.

5. Connection to multiple databases.

In most applications, we operate on only one database, but sometimes we encounter multiple database connections. Here we discuss two ways of connecting:

' First: Two Connection, two recordsets.
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 pubs. Authors ", CNN
Rs2. Open ' select * from Coolbel. MyTable ", CNN
......


The second approach is highly undesirable, not only at a low speed, but also as a waste of resources. The fundamental reason for its inefficiency is that this connection does not take advantage of connection pooling, making each request
To re-create the connection to the database.

click! test the first code click! test the second code (Coolbel is not stable, need to test more than a few times. )

6. For disconnected RecordSet, don't forget to make ActiveConnection = Nothing