ADO (ActiveX Data Object) is an important component of ASP and built in ASP. The ASP is connected to the background database with the Database Access component. The Database Access component uses ADO to access information stored in a database or other formal database structure. Therefore, ADO has become the favorite Database Access component of today's developers, and modify SQL_Server database data compared to past CGI programs in the past. However, the default settings of ADO do not always optimize settings. To develop a powerful CLIENT / Server and web-based applications, it is necessary to study how to improve its performance. Practice has proven to increase the performance of ADO from 7 aspects.
1. Use the local SQL Server OLE DB Provider attribute
Microsoft uses the method to access a wide variety of data sources for applications that OLE DB, OLE DB is between the ODBC (Open Database Connection Standard) layer and the application. In your ASP page, ADO is "Application" on OLE DB. Your ADO call is first sent to OLE DB and then sent to the ODBC layer. OLE DB is a set of component object model (COM) interfaces. In general, ADO defaults, an ADO RecordSet object uses OLE DB Provider as ODBC to connect SQL Server. However, the local OLE DB Provider provides short code paths to SQL Server, which tends to increase the performance of ADO.
2. Reuse existing connection objects
If you are developing an APS page, you are multiplexed with an existing ADO Connection object in a Command or RecordSet property of an ActiveConnection property. By using this approach you can avoid some additional overhead by using this method when you open or create a Connection object.
3. Clearly define various parameters
Although the ADO can dynamically determine the various parameter properties used by a Command object, better shorten the development cycle, but this feature, in the application, it is typically increased the loop of the server, and reduces ADO to some extent. Performance. Therefore, clearly define a parameter Type, Direction and size can reduce the number of loops caused by the application to the server.
4. Adjust the size of the buffer
ADO's Cachesize affects the KeySet cursor and static and dynamic Recordsets in terms of server. Under the default setting of Cachesize, the ADO has a good working state when modifying and merge operation, but if the application needs to recover a large amount of data, you need to add this value. Reduced the number of loops of the server is the key to improve the performance of ADO and SQL Server applications.
5. Use the Command object instead of the cursor
Sometimes, using cursors to make modifications are almost inevitable. However, modifying a cursor may bring additional overhead to reduce performance. However, if you try to use a Command object containing T-SQL INSERT, UPDATE, the DELETE statement, you can send a modification operation to the server to make developers get better performance.
6. Use pointer fast forward movement in the Recordset object
The best ADO data update performance comes from the use of pointer fast forward movement methods in the Recordset object. Once you set a RecordSet object as a forward, read-only, and with value 1 Cachesize, ADO automatically generates a fast forward-moving pointer in this object. This approach allows a lot of data to flow from SQL Server to the Client end with very low overhead.
7. Use the best SQL
