ado|web| Skills | data | database | Performance ADO (ActiveX Data Object) is an important component of ASP, built into ASP. ASP uses the database access component to connect to the background database. The database access component accesses information stored in databases or other tabular database structures through ADO. As a result, ADO has become the most popular database access component of today's developers to recover and modify Sql_server database data, compared to the previous programming of a CGI program to access the databases. However, the default settings for ADO are not always optimized, and to develop a strong client/server and web based application, it is necessary to look at how to improve its performance. Practice has proved that the performance of ADO can be improved from 7 aspects.
1. Use local SQL SERVER OLE DB Provider Properties
Microsoft's approach to accessing a wide variety of data sources for applications is that OLE Db,ole DB is between the ODBC (Open Database Connectivity standard) layer and the application. In your ASP page, ADO is "application" above OLE DB. Your ADO calls are 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 to SQL SERVER. However, the local OLE DB Provider provides a shorter code path for SQL SERVER, which tends to greatly improve the performance of ADO.
2. Reusing an existing Connection object
If you are developing an APS page application, reuse an existing ADO Connection object in the ActiveConnection attribute of a command or Recordset object. Because when ADO secretly opens or creates a connection object, you can avoid some extra overhead by using this method.
3. Define various parameters explicitly
Although ADO can dynamically determine the parameters used by a command object to better shorten the development cycle, but this feature, in the application is very typical of the server to increase the loop, to some extent, to reduce the performance of ADO. So, by explicitly defining the type of a parameter, Direction and Size can reduce the number of loops the application causes to the server.
4. Resize the buffer
The cachesize of ADO affects server-side keyset cursors and static and dynamic recordsets. With the default settings for CacheSize, ADO works well when modifying and merging operations, but if the application needs to recover a large amount of data, this value needs to be added. Reducing the number of loops on your server is key to improving the performance of your ADO and SQL Server applications.
5. Using command objects instead of cursors
Sometimes it is almost inevitable that a cursor is used to modify the operation. However, modifying a cursor can incur additional overhead and thus degrade performance. However, if you attempt to use a command object containing a T-SQL Insert, Update, and DELETE statement to send a modification to the server, you can get better performance from the developer.
6. Using pointer fast forward methods in a Recordset object
The best ADO Data update performance comes from using the pointer fast forward method in a Recordset object. Once you set a Recordset object to move forward, a cachesize,ado that is read-only and with a value of 1 automatically generates a quick forward pointer in the object. This approach allows a large amount of data to flow from SQL Server to the client side with very low overhead.
7. Use the best SQL
Using better SQL is the best way to get better performance from ADO and all SQL based data access technologies. When you build a SQL statement that contains the rows and columns you really need, take advantage of the SQL built-in storage process to enable the server to effectively handle all of the data update requirements.
