ADO Performance Tips

ADO is by far the most commonly used data-access middleware for retrieving data and updating SQL Server databases. However, ADO's default settings aren't always the best options for optimal performance. Here are seven ways you can boost ADO performance with SQL Server.

7. Use the Native SQL Server OLE DB Provider

By default, an ADO Recordset uses the OLE DB provider for ODBC (MSDASQL) to connect to SQL Server. However, the native OLE DB provider for SQL Server (SQLOLEDB) offers a shorter code path, which results in better performance.

6. Reuse an Existing Connection Object

If you're developing a stateful application, reusing an existing ADO Connection object in a Command or Recordset object's ActiveConnection property lets you avoid the overhead incurred when ADO implicitly creates and opens a Connection object for you.

5. Explicitly Define Parameters

ADO's ability to dynamically determine the properties of the parameters that a Command object uses can be a time-saver during development, but this feature typically adds unnecessary round-trips to the server in a production application. Explicitly defining a parameter's Type, Direction, and Size reduces the number of round-trips your application makes to the server.

4. Tune the CacheSize

The ADO CacheSize affects server-side Keyset, Static, and Dynamic Recordsets. The default CacheSize of 1 works well for updates and combinations of operations, but if your application needs to retrieve large resultsets, try increasing this value. Reducing the number of round-trips to the server is one of the most important keys to ADO and SQL Server application performance.

3. Use Command Objects Instead of Cursors

Sometimes using a cursor for updates is almost too easy to avoid. However, updateable cursors carry overhead, and you get better performance if you use Command objects that contain T-SQL INSERT, UPDATE, and DELETE statements to send updates to SQL Server.

2. Use Fast Forward Only Recordsets

The best ADO data-retrieval performance comes from using Fast Forward Only Recordsets. ADO creates a Fast Forward Only Recordset, sometimes called a fire hose cursor, when you specify a Recordset as Forward Only, Read Only, with a CacheSize of 1. The Fast Forward Recordset lets SQL Server quickly stream large amounts of data to the client with low overhead.

1. Use Smart SQL

Using good SQL is the best way to get better performance from ADO and all SQL-based data-access technologies. When you build SQL statements, include only the rows and columns that you really need. Take advantage of SQL's set-based processing to let the server efficiently handle all data-retrieval requirements.

Discuss this Article 7

The tips sounds good, but it would be Great if there is an code example for each tip OR to generalise u may consider to give code example to anything u recomend. This will help the developer very much!!

1.) what can you say about the SQL option clause, did it help in the process or more on data retrieval and short queries.
2.) Can we give more advices and possible SQL commands to make my processing more fast.
3.) What can you say about SQL LiteSpeed?

1.) what can you say about the SQL option clause, did it help in the process or more on data retrieval and short queries.
2.) Can we give more advices and possible SQL commands to make my processing more fast.
3.) What can you say about SQL LiteSpeed?

ADO performance tips - sounds good. With some more examples and codes, this blog would be more useful. But, still I would recommend my friends to go through this post. I always go through http://www.payforessayonline.com but have never seen such type of information as in here. The smater use of SQL is really awesome.

From the Blogs

Don’t let bad data sneak up on you when and where you least expect it. Ferret out bad data with Melissa Data’s newest Profiling Component for SSIS. Learn how to take control of your data using knowledge-base-driven metadata. The truth shall set you free!...More

Now that we’ve outlined the process to let servers in a SQL Server AlwaysOn Availability Group "talk to each other" by means of setting up linked servers, it’s possible to set up some additional or improved checks on Availability Group Health....More

In my previous post, I provided a high-level outline of the core logic (and rationale behind that logic) that would be needed to set up regular synchronization checks on SQL Server Agent Jobs for servers where AlwaysOn Availability Groups have been deployed. In this post, I’ll walk through the steps--and the code--needed to setup those checks....More