Optimization of ADO in ASP

Comprehensive Optimization of ADO

1 Connection

1.1 pooling

In Web applications, many users access the database at the same time, and the object scope in ASP is page-level. That is to say, each page must be connected and disconnected from the database, wouldn't it be slow? In addition, every connection to the SQL Server database will incur a system overhead of 37 KB. What should I do?

Some people may think of using application and session to solve the problem, but this is not desirable. If application is used, multiple users may access the database through one connection at the same time, although the connection time is reduced, the access speed to the database will become very slow. If a session is used, what should I do if the session times out? If you set the session. timeout to a large value, after the user leaves, the connection will be retained for a period of time and will incur additional costs.

In fact, you don't need to consider this issue. Accessing the database through ole db will solve this problem for you. ole db has a resource pooling which will proxy your connection request, then, you can use the connection that someone else has just used. (I will not elaborate on the specific mechanism, but I am not very clear about it either)

1.2 provider

Few people may have used this property. The default value is msdasql, and msidxs and adsdsoobject, but in ado2.0 (see vs98) and ado2.1 (see sql7) it provides some new providers:

Msdaora (ole db provider for Oracle)

Microsoft. Jet. oledb.3.51 (ole db provider for Microsoft Jet (for access ))

Sqloledb (Microsoft SQL Server OLE DB Provider)

If you use these databases, you can use these new providers to directly access the database without using ODBC. The improved efficiency can be imagined.
Comprehensive Optimization of ADO

2 command

2.1 commandtype

The default value is adcmdunknown. Ado will judge your commandtype one by one until it thinks it is appropriate and is not recommended. (Recordset. Open and connection. Execute can also be used)

Adshorttext executes your SQL statements as they are. However, if your SQL language is of the following types, you can use other commandtypes to improve the efficiency of SQL statement execution.

Objcmd. Execute "select * From table_name", ad1_text can be replaced with objcmd. Execute "table_name", adcmdtable

Objcmd. Execute "Exec proceuure_name", ad1_text can be replaced with objcmd. Execute "proceuure _ name", ad1_storedproc

If your SQL statement does not return a record set, such as insert or update, use adexecutenorecords (ado2.0) can reduce system overhead (can be added to ad1_text and ad1_storedproc, such as ad1_storedproc + adexecutenorecords)

There are also adcmdtabledirect and ad1_file (ado2.0). I still don't know how to use it. ad1_file can be used to access an XML file.

2.2 prepared

If you need to execute similar SQL statements repeatedly, You can pre-compile your SQL statements to Improve the efficiency.

Objcmd. commandtext = "select spell from typer. wordspell where WORD =? "

Objcmd. Prepared = true

Objcmd. Parameters. append objcmd. createparameter ("Word", advarchar, 2)

For I = 1 to Len (strname)

Strchar = mid (strname, I, 1)

Objcmd ("word") = strchar

Set objrs = objcmd. Execute

If objrs. EOF then

Strnamesame = strnamesame & strchar

Else

Strnamesame = strnamesame & objrs ("Spell ")

End if

Next 'I = 1 to Len (strname)
Comprehensive Optimization of ADO

3 recordset

3.1 locktype

The default value is adlockreadonly. If you do not need to modify the data, do not change it to adlockoptimistic. Otherwise, it will reduce the speed and increase the overhead.

Adlockreadonly> adlockpessimistic> adlockoptimistic> adlockbatchoptimistic

3.2 cursortype

The default value is adopenforwardonly. If you only use movenext method, you 'd better not change it. The speed will be around 140%.

Adopenforwardonly> adopendynamic> adopenkeyset> adopenstatic

3.3 cursorlocation

The default value is aduseserver. In fact, it is not good. It can reflect changes on the database server at any time, but the system overhead is very high and the connection to the database server needs to be maintained, however, it is faster when the database server and the Web server are together. However, when adlockoptimistic is used, I cannot use properties such as recordcount.

With aduseclient, you can sort, filter, and shape data.

If you do not need real-time data, try to use aduseclient

4 others

4.1 early bind

You don't need to read this with ASP. If you use VB

Dim objconn as ADODB. Connection is better than set objconn = Createobject ("ADODB. Connection ")

The shape in 4.2 ADO 2.1 is really fun.

4.3 ADO 2.1 you can use objrs. Fields. append to create A recordset

4.4 convert a column of recordset data directly into an array for faster operation, but the system overhead is higher.