PRB: Joins Via Dynamic Cursor Execute Slower Than Other Cursors (168551)

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

On a dynamic cursor fetch, the cursor positions itself to the record obtained from the previous fetch or to the start of the scan if it is the first fetch on the cursor. Dynamic cursors try to maintain their position across statements so that for subsequent fetches they are positioned correctly to fetch data relative to the previous row. For this they use unique index information to identify a row and set the cursor position. A unique index is also required to support positioned updates and deletes through the cursor; for these cases the cursors engine has to qualify one and only one row.

The index used to drive the cursor is selected from the following (in order of decreasing preference):

User-specified index
Unique clustered index
Primary key
Other unique index on the table
If no indexes exist, the row identifier (RID) is used

It may happen that the keys of the selected index are not explicitly referenced in the original select statement.

RESOLUTION

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.

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.