Article ID: 168551
Article Last Modified on 2/22/2005
APPLIES TO
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q168551
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.
Additional query words: upgrade performance slow
Keywords: kbbug kbprb KB168551
