FIX: Cursor overhead higher in SQL Server 7.0 than in SQL Server 6.x for small result sets (197800)

CAUSE

The overhead of cursor operations on small result sets may be larger in SQL Server 7.0 than in SQL Server 6.x, due to query compilation times and the method used to create and manipulate the internal worktables used by the cursors. By default, cursor queries are not eligible for auto-parameterization, so each execution of a cursor opened on an ad-hoc query must incur the costs compiling the query. For additional information about cursor worktables, please see the following article in the Microsoft Knowledge Base:

168678 INF: Understanding Worktables Used by Server Side Cursors

WORKAROUND

To work around this problem, use SQLPrepare, sp_sqlexecute, or create a stored procedure for the query the cursor is being opened on. This will eliminate the compilation time for subsequent executions of the cursor and may increase performance. Please note that when using SQLPrepare, SQL Server 7.0 supports the prepare execute model directly, while SQL Server 6.x uses temporary stored procedures. For more information, see the "Preparing SQL Statements" and "Prepared Execution" topics in the SQL Server 7.0 Books Online.

From a design standpoint, if an application is repetitively opening, fetching, and closing cursors based on small result sets, consider the following:

Does this operation need a cursor?

In general, if an operation can be performed with a simple, set-based query, performance will be faster than using cursors. This is true regardless of the relative speeds of cursor operations in SQL Server 7.0 and SQL Server 6.x.
Can a client-side cursor be used?

Default result sets (client-side cursors, firehouse cursors) do not use a server-based cursor. All that is sent to SQL Server is the query the cursor is opened on, and SQL Server returns the rows to the client for buffering. This is very efficient because there is only one round trip from the client to the server and back. This will be faster than server-side cursors for small result sets in both SQL Server 6.x and SQL Server 7.0.
Would a fast forward-only cursor be helpful?

One of the limitations of the default result set is that you may only have one active statement per connection. If you need multiple active statements, you will need to use server-side cursors. SQL Server 7.0 introduced a new type of server-side cursor called "fast forward-only." With the autofetch option set, fast forward-only cursors can be used to retrieve small result sets in one round trip to the server, similar to the default result set. Because fast forward-only cursors are server-based, you can have multiple active statements open per connection.

For more information on SQL Server fast forward-only cursors, see SQL Server 7.0 Books Online.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in U.S. Service Pack for Microsoft SQL Server 6.5. For information about how to download and install the latest SQL Server Service Pack, see the following Microsoft Web site:

http://www.microsoft.com/sql/prodinfo/previousversions/downloads/servicepacks.mspx

For more information, contact your primary support provider.

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server 7.0. For information about how to download and install the latest SQL Server Service Pack, see the following Microsoft Web site: