(4) Cursors do still have quite complex locking and concurrency implications but (a) this does not necessarily mean poor performance, just that care has to be taken with the design, and (b) all vendors' database servers will display such complexity - I challenge anybody to show that it is specifically a Sybase weakness. Locking behaviour is mostly driven by ANSI isolation level definitions, which are common to all vendors, although they differ in which ones they offer, and in their detailed implementation solutions to the ANSI requirements. Generally, as with Sybase, at least Level 3, Level 1, and Level 0 are offered. These correspond roughly to holdlock, no holdlock, and no read locks attempted, respectively, in Sybase traditional terminology. (The are not solely cursor related of course, although again using cursors introduces more complexity in understanding what will happen and when). Perhaps appropriate use of these options would also help.
(5) Oracle cursors have some properties, e.g. read consistency, which are the subject of legitimate debate. Some see them as dubious features that Oracle have managed to "spin" into desirable ones. I feel that like all RDBMS quirks, they have a place if carefully used for well thought out reasons, and with some eye to things that are clearly not going to stay supported for ever. For Oracle fans who have found effective uses for read consistency -- which is basically the ability to query a stable logical snapshot of data during a long lived cursor loop, but without holding up other users by taking long lived shared locks -- ASE 15.0 will include an insensitive cursors feature, which should offer very similar properties. Cursor handling in general will be considerably enhanced in 15.0 in fact.
- Favor server side cursors, trial client cursors before use
- Common sense for what is the cleaner solution fit cursor or set-based
- Default to set-based approach, but not all the time
- Be prepared to get competent help, or put in the work yourself, to analyse locking behaviour in very complex, critical cases, and do not swallow the idea (surely it is not pure Oracle knocking to suggest this) that any vendor offers a free ride in all situations no matter how inherently complex. This will also apply to set-based solutions, though they are probably simpler on average.
For More Information
- Dozens more answers to tough SQL Server questions from Mich Talebzadeh are available here.
- The Best Sybase Web Links: tips, tutorials, scripts, and more.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, Sybase, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in February 2004