optimization of ADO in ASP

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)