Comprehensive optimization of ADO

ado| Optimization 1 Connection


1.1 Pooling


in the Web application, often there are many users at the same time access to the database, and the ASP's object scope is page-level, that is,


said that each page should be joined and disconnected from the database, would it be slow? And each join to the SQL Server database brings in a 37k overhead, how to


do?


Some people may think of using application and session to solve the problem, but this is not advisable, if you use application, then there will be multiple users at the same time through a connection access to the database, although saving the time to establish the connection, But the speed of accessing the database can be very slow. If you use session, the problem that arises is, how to do session timeout? If you set the Session.Timeout very large, the connection will remain for a period of time after the user leaves, and it will incur additional overhead.


actually do not have to consider this problem, access the database through OLE DB, it will solve this problem for you, OLE DB has a resource pooling, it will substitute


your connection request and then use the connection that someone just used. (Specific mechanism no longer elaborated, in fact, I did not get too clear, Xi hee)


1.2 Provider


probably not many people have used this property, its default is MSDASQL, there are MSIDXS and Adsdsoobject, but in ADO2.0 (see VS98) and


ADO2.1 (see SQL7) offers a number of new provider:


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 the database is these, with these new provider can not through ODBC direct access to the database, improve the efficiency can be imagined.


2 Command


2.1 CommandType

The default value of
is that Adcmdunknown,ado will judge your CommandType one at a time, until it deems it appropriate, not recommended. (In Recordset.Open and


Connection.Execute can also be used)


adCmdText executes your SQL statements as they are, but if your SQL language is the following, you can improve
by using other CommandType

your SQL statement execution efficiency


Objcmd.execute "Select * FROM table_name", adCmdText can be replaced with Objcmd.execute "table_name", adCmdTable


objcmd.execute "Exec proceuure_name", adCmdText can be replaced with Objcmd.execute "Proceuure _name", adCmdStoredProc


also important is that if your SQL statement does not return a recordset, such as Insert and update, then use adExecuteNoRecords


(ADO2.0) reduces system overhead (can be added to adCmdText and adCmdStoredProc, such as adCmdStoredProc + adExecuteNoRecords)


and adCmdTableDirect and adCmdFile (ADO2.0), I'm not quite sure how to use it, adcmdfile can be used to access an XML file.


2.2 Prepared


If you need to execute a similar SQL statement repeatedly, you can precompile your SQL statements and improve 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


default is adLockReadOnly, if you do not have to modify the data, do not change to adlockoptimistic or the like, otherwise it will reduce the speed and increase the cost of the


adlockreadonly > adLockPessimistic > adLockOptimistic > adLockBatchOptimistic


3.2 CursorType


The default is adOpenForwardOnly, if you only use MoveNext method, it is best not to change, speed affects about 140%


adopenforwardonly > adOpenDynamic > adOpenKeyset > adOpenStatic


3.3 CursorLocation


default is adUseServer, it is not good, it can reflect the changes on the database server at any time, but the system is expensive, and need to maintain and database services


connection, but faster when the database server and Web server are together. But I couldn't use
when I was adlockoptimistic.

RecordCount and other property.


use adUseClient words, you can reorder, filter, shape, and other operations


if the real-time nature of the data is not required, try to use adUseClient


4 Other


4.1 Early bind


with ASP this point does not need to look, if use VB words


Dim objconn as ADODB. Connection than Set objconn = CreateObject ("ADODB. Connection ") Better


4.2 The shape in ADO 2.1 is really fun


4.3 ADO 2.1 You can use ObjRS.Fields.Append to create a recordset


4.4 takes a column of data from a recordset directly into an array to operate faster, but the system overhead is a bit larger