BUG: Joins Via Dynamic Cursor Execute Slower Than Other Cursors

- Microsoft SQL Server, version 6.5 BUG #: NT: 16936 (6.50)

SYMPTOMS

Joins via a dynamic cursor may execute considerably slower than the same query would when run using a different type of cursor, or if run outside of a cursor.

Because row eligibility within the cursor is reevaluated during each fetch, dynamic cursors are more resource intensive than other types of cursors and are thus expected to be somewhat slower. Depending on the available indexes and the table sizes, the performance difference can sometimes be an order of magnitude different.

CAUSE

The optimizer is failing to properly analyze the cost of the various join possibilities when choosing a query plan. The resulting plan may be different from what would be picked if the same query were executed outside of the cursor, or if executed using a different type of cursor.

WORKAROUND

Use a different type of cursor, such as a keyset cursor. If possible, consider changing the process to not use a cursor, as set operations are typically much faster than using any type of cursor.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

If you evaluate the showplan output, a dynamic cursor will print out two different query plans. The first plan is displayed when the cursor is declared, the second plan at the first FETCH. The second plan is the one used to perform the query. If the query plan from these two outputs is different, you may experience the slow performance possible with this bug.

With SQL Server 6.5, the default cursor syntax will declare a dynamic cursor rather than a keyset cursor, so you must now use the SCROLL keyword to declare a keyset cursor. If the application has been upgraded from 6.0 (where it was using keyset cursors by default), you can simply revert back to the 6.0 behavior by using trace flag 7501. This trace flag simply causes the default cursor type to be keyset rather than dynamic. Consult the Administrator's Companion of the SQL Server documentation for more information on using trace flags.