Comprehensive optimization ADO is selected from the Delphi Garden
http://www2.delphifans.com/blog/more.asp?name=hawkliu&id=438
1 Connection
1.1 pooling
In the Web Application, there is often a case where many users have access to the database, and the object scope in the ASP is the page level, that is
Said that every page is counted and disconnected, isn't it very slow? And each of the joints of the SQL Server database will bring 37K system overhead, how
do?
Some people may think of using Application and Session to solve the problem, but this is not advisable. If you use Application, you will have multiple users.
When the database is accessed through a Connection, although the time to establish a connection is saved, the speed of accessing the database will become very slow. If used
SESSION, the problem, what is the session timeout? If you set the session.timeout, then the connection will retain a section after leaving.
Time will also bring additional overhead.
In fact, you don't have to consider this problem. If you access the database through Ole DB, it will solve this problem for you, Ole DB has a resource poibility, which
Licen your connection request, then connect the connection you just used by others. (The specific mechanism is no longer elaborated, in fact, I didn't understand too much, 嘻嘻)
1.2 Provider
There may be no many people have used this Property, and its default is MSDasql, and MSIDXS and AdsDSOOBJECT, but in ADO2.0 (see vs98) and
Ado2.1 (see SQL7) provides some new provider:
MSDara (Ole DB Provider for Oracle)
Microsoft.jet.OleDb.3.51 (OLE DB Provider for Microsoft Jet (for Access)
SQLOLOLDB (Microsoft SQL Server Ole DB Provider)
If the database you use is these, use these new Provider to directly access the database directly, it is conceivable to improve the efficiency.
2 Command
2.1 CommandType
The default is AdcmDunkNown, and ADO will judge your CommandType one by one until it is considered appropriate, it is not recommended. (In Recordset.open and
Can also be used when Connection.execute is
Adcmdtext is performing your SQL statement as an original, but if your SQL Language is the following, you can improve it with 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 objCmd.Execute "proceuure _name", adCmdStoredProc Another important point is that, if your SQL statement does not return record set, such as insert and update, etc., then use adExecuteNoRecords
(ADO2.0) can reduce system overhead (you can add AdcmdText and AdcmdStoredProc, such as AdcmdStoredProc Adexecutenorecords)
There is also AdcmdTableDirect and AdcmdFile (ado2.0), I am not very clear how to use, AdcmdFile can be used to access an XML file.
2.2 prepared
If you need to repeat similar SQL statements, then you can precompile your SQL statement and improve the efficiency is also very considerable.
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 kil
Strnamesame = Strnamesame & Strchar
Else
Strnamesame = Strnamesame & Objrs ("Spell")
END IF
Next '' i = 1 to len (strname)
3 recordset
3.1 LockType
The default is AdlockReadOnly. If you don't have to modify the data, don't change it into an AdlockOptimistic, otherwise the speed and increase overhead will be reduced.
AdlockreadInly> AdlockPESSIMISTIC> AdlockOptimistic> AdlockBatchOptiMistic
3.2 CURSORTYPE
The default is AdopenForwardonly. If you use Movenext Method, it is best not to change, the speed affects about 140%.
AdopenForwardONLY> AdoPENDYNAMIC> AdopenKeySet> AdoPenStatic
3.3 CURSORLOCATION
The default is ADUSSERVER, in fact, it can reflect changes on the database server at any time, but the system is over-selling, but also requires maintenance and database services.
The connection of the device, but it is faster when the database server and the Web Server are together. But when Adlockoptimistic, I can't use it.
RecordCount, etc. Property.
With aduseclient, you can sort, filter, Shape, etc.
If you don't require the real-time performance of the data, try to use aduseclient.
4 other
4.1 Early Bind
If you use the ASP, you don't have to look, if you use VB
Dim objconn as adoDb.connection is better than set objconn = creteObject ("adodb.connection")
4.2 Shape in ADO 2.1 is really fun
4.3 ADO 2.1 can create a Recordset with objrs.fields.append
4.4 Putting a column of Recordset directly into an array to operate fast, but the system overhead is large
