BUG: Slow Performance Using Cursor Positioned Update If No PK

• Microsoft SQL Server version 7.0

SYMPTOMS

Positioned updates on a cursor may experience non-linear performance degradation as the number of rows affected by the cursor increases. This will occur either if the cursor is opened using ANSI syntax and updated using the WHERE CURRENT OF syntax, or if the cursor is opened and updated through an application making DB-Library, ODBC, or OLE DB calls.



Note that this will only occur if the table does not have a primary key or unique index.

WORKAROUND

To work around this problem, create a primary key or unique index on the table. Performance should scale roughly linearly with the number of rows affected by the cursor.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

MORE INFORMATION

The primary key or unique index described in the WORKAROUND section of this article does not need to be (although it may be) on any of the columns the cursor is opened on or the column(s) being updated. Any column(s) that will uniquely identify each row is a valid choice for a primary key or unique index.

If the table can not be uniquely identified by any combination of the columns that currently exist in the table, consider adding an ID column to the table. If you do not have a meaningful value for an ID column, you can use an identity, timestamp or GUID (with a default using the newid() function) column to automatically generate IDs. Having each row be identifiably unique is a fundamental concept of any relational database and will also benefit areas other than cursor performance.

Additional query words: PK hang slows down sp_cursoropen sp_cursor oledb lib
autogenerate

Keywords          : kbbug7.00 
Version           : WINNT:7.0
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: April 19, 1999