Fully optimized ADO_MySQL

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
Isn't it very slow to connect and disconnect databases on every page? In addition, every connection to the SQL Server database will incur a system overhead of 37 kB. how can this problem be solved?
?
Some people may think of using Application and Session to solve the problem, but this is not desirable. if Application is used, multiple users will
When a Connection is used to access the database, although the Connection time is reduced, the access speed to the database will become very slow. If you use
Session. what should I do if the Session times out? If you set the Session. Timeout to a large value, the connection will be retained after the user leaves.
Time also brings additional overhead.
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 replace
Handle your connection request, and then send the connection that someone else has just used to you for use. (I will not elaborate on the specific mechanism, but I am not very clear about it either)
1.2 Provider
Maybe not many people have used this Property. its default value is MSDASQL, and MSIDXS and ADSDSOObject, but in ADO2.0 (see VS98) and
ADO2.1 (see SQL7) 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.
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. (In Recordset. Open and
Connection. Execute can also be used)
Adshorttext executes your SQL statements as they are, but if your SQL Language is of the following types, you can use other commandtypes to improve
Your SQL statement execution efficiency
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 adshorttext and adshortstoredproc, such as adshortstoredproc + 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)
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 large and requires maintenance and database services.
But it is faster when the database Server and Web Server are together. However, I cannot use adLockOptimistic.
RecordCount and other properties.
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.