ado| Optimization 1 Connection
1.1 Pooling
In the Web application, there are often many users at the same time access to the database, and the ASP is the scope of the object is page level, that is, each page to join and disconnect the database, will it be very slow? And what if each join to the SQL Server database brings in a 37k overhead?
Some people may think of using application and session to solve the problem, but this is not advisable, if using application, then there will be multiple users at the same time through a connection access to the database, although save 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.
In fact, there is no need to consider this problem, access to the database through OLE DB, it will solve the problem for you, OLE DB has a resource pooling, it will proxy your connection request, and then the other people just used the connection to you to use. (Specific mechanism no longer elaborated, in fact, I did not get too clear, Xi hee)
1.2 Provider
There may not be many people who have used this property, the default is Msdasql, and Msidxs and Adsdsoobject, but in ADO2.0 (see VS98) and ADO2.1 (see SQL7), some new provider are available:
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 words, 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 is that Adcmdunknown,ado will judge your CommandType one at a time until it is considered appropriate and not recommended. (can also be used in Recordset.Open and Connection.Execute)
adCmdText executes your SQL statements as they are, but if your SQL language is the following, you can improve your SQL statement execution efficiency by using other CommandType
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
It is also important that if your SQL statement does not return a recordset, such as Insert and update, then using adExecuteNoRecords (ADO2.0) can reduce the overhead (add 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)
Comprehensive optimization of ADO
3 Recordset
3.1 LockType
The 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
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
The default is adUseServer, which can reflect changes on the database server at any time, but the system is expensive and requires a connection to the database server, but faster when the database server and Web server are together. But in the adlockoptimistic I can't use RecordCount and so on.
Using adUseClient, you can reorder, filter, shape, and so on.
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 have to look, if using 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 It is faster to make a column of data in a recordset directly into an array, but the system overhead is much larger
