Tutorials to .com

Tutorials to .com » Asp » Optimization » ADO optimization in asp

ADO optimization in asp

Print View , by: iSee ,Total views: 20 ,Word Count: 1057 ,Date: Sat, 18 Apr 2009 Time: 12:34 AM

1 Connection

1.1 Pooling

Web application in the often occur at the same time, many users access the database at the same time the situation, and asp objects in the page-level scope is, in other words, each page must be connected and disconnected from the database, it will be very slow ? And each of the SQL Server database connected to the system will cost 37k, how do?

Some people might think to use application and Session to resolve the issue, but it is not desirable, if the Application, then there will be multiple users simultaneously access the database through a Connection, although the establishment of connections to save time, but access to the database speed will become very slow. If the Session, the problem is, Session timed out how to do? If the set Session.Timeout very large, following the departure of the user, connect will be retained for a period of time, will bring additional costs.

In fact, do not consider this issue, access 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 people just used to connect to you then use. (No longer on the specific mechanism, in fact, I did not engage in too much that hee hee)

1.2 Provider

The number of people who may not have used the Property bar, and its default value is MSDASQL, there MSIDXS and ADSDSOObject, but ADO2.0 (see VS98) and ADO2.1 (see SQL7) which provide 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 used a database of these, using these new Provider through ODBC can not access the database directly to improve the efficiency of the well imagine to what extent.
2 Command

2.1 CommandType

Default value is adCmdUnknown, ADO will judge you by the CommandType, up to date as it thinks fit, not recommended. (Recordset.Open and Connection.Execute in time can also be used)

as they stood adCmdText is the implementation of your SQL statement, but if you have the following SQL Language is, then, through the use of other CommandType can improve your efficiency in the implementation of the SQL statement

objCmd.Execute "Select * from table_name", adCmdText can be replaced by objCmd.Execute "table_name", adCmdTable

objCmd.Execute "Exec proceuure_name", adCmdText can be replaced by objCmd.Execute "proceuure _name", adCmdStoredProc

There are a very important point is that if your SQL statement does not return recordsets, such as insert and update, etc., then use adExecuteNoRecords (ADO2.0) can reduce the system overhead (adCmdText and adCmdStoredProc be added, such as adCmdStoredProc + adExecuteNoRecords)

There adCmdTableDirect and adCmdFile (ADO2.0), I do not know how to use them, adCmdFile can be used to access an xml document.

2.2 Prepared

If you need to repeat the implementation of a similar SQL statement, then you can pre-compile your SQL statements to improve efficiency is also very impressive

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


strNamesame = strNamesame & objRS ( "spell")

End If

Next 'i = 1 To Len (strName)

Comprehensive optimization of ADO

3 Recordset

3.1 LockType

Default is adLockReadOnly, if you do not need to modify data, adLockOptimistic do not like change, or will reduce speed and increase the cost of

adLockReadOnly> adLockPessimistic> adLockOptimistic> adLockBatchOptimistic

3.2 CursorType

Default is adOpenForwardOnly, if you only use MoveNext Method, it is best not to change the speed of the impact of around 140%

adOpenForwardOnly> adOpenDynamic> adOpenKeyset> adOpenStatic

3.3 CursorLocation

Default is adUseServer, not a matter of fact, it can at any time to reflect changes to the database server, but a large system overhead, and the need to maintain connections and database servers, but in the database server and Web Server with a time when things need to be expedited. AdLockOptimistic But the time I can not use the RecordCount such Property.

Use with adUseClient, you can do to re-sort data, filter, shape and other operating

If the data does not require real-time, then try to adUseClient

4 other

4.1 Early bind

ASP with no need to read this, if so vb

Dim objConn As ADODB.Connection than Set objConn = CreateObject ( "ADODB.Connection") is better

4.2 ADO 2.1's shape is really fun

4.3 ADO 2.1 can be used objRS.Fields.Append to create a Recordset

4.4 of a Recordset into an array of data directly to speed faster, but the system overhead to a large number of

Asp Optimization Articles

Can't Find What You're Looking For?

Rating: Not yet rated


No comments posted.