I have worked with Sybase and found the restrictions in the use of cursors (for large data volumes, all Sybase
books say not to use cursors) very limiting. I have heard that Oracle does not pose any limitation on row-by-row processing cursor use. Hence I am thinking of moving on to Oracle. Can you tell me, what are the exact reasons Sybase discourage cursor use and what are the alternatives -- temporary tables and muliple updates, I assume?
This is rather an interesting question which goes to the heart of RDBMS and the way each engine, such as Sybase and Oracle, handles the cursor use.
The common understanding is that any RDBMS like Sybase or Oracle relies on variants of ANSI SQL. SQL as a 4GL is designed to handle result sets as opposed to walking through individual records. So cursors are as expensive in Oracle as in Sybase. It would be naive to assume otherwise. Oracle's locking mechanism is different from Sybase's. That in some cases improves performance, albeit relatively, but does not eliminate the issue of use of cursors in general in a 4GL. As a solution Sybase offers temporary (#) tables, to which Oracle has no answer expect for the so called permanent temporary tables that rely on the user's temporary tablespace. Here the data is temporary, as opposed to table itself. In general each engine has a different approach to cursors. For example in Sybase you can achieve the solution by using temp tables as opposed to cursors. Now let us look at the details.
(1) Advice not to use cursors is out of date, if indeed it was ever really justified, where server-side cursors are concerned, i.e. those embedded in stored procedures. These can be very fast. I have seen and investigated in detail an example where the correct use of a server-side cursor was quicker than the deferred update generated by the logically equivalent set oriented "update through a join". This was for posting trade-derived account transactions to a c. 4GB table.
(2) Much of the original advice deprecating cursors was based on the use of client-side cursors, i.e. those effected by the ct_cursor command in Open Client CT-Library, either directly or via ODBC. The problem with client-side cursors is that they can induce excessive locking delays because of the network latency from client to server. The latency gets multiplied by a large number of row fetches and induces long lock holds and consequent poor concurrency. I have not tested this personally, but can quite believe it was nasty, particularly back in the early Sybase days when even the LAN was only 10Mbit/sec and quite poor latency (I believe). Even here, I suspect, though have not proven, that things are "nothing like as bad as they used to be" with continuing general improvements in ASE, in Open Client, and in networks, plus of course row level locking ...
I am not clear there is much definite justification for using client-side cursors these days although I stand to be corrected on that.
(3) Some of the anti-cursor advice is probably just a transcription of the assumption that set-based SQL processing is good, and cursors bad. This is often true, I agree, when there are logically equivalent solutions, but beware my counter example from item (1) before rushing to any absolute and sweeping judgements! Also, this philosophy is (validly within its limits) as much based on clarity of coding as on performance. Surely when appropriate, the greater clarity of set based code is indisputable. On the other hand, when a problem really does suit cursor use, the set-based solution could look artificial, involving temporary tables and multiple processing phases simply to stay within the set oriented philosophy. Definitely horses for courses on that one. I do not see why Oracle books or documentation omit equivalent advice, as it seems vendor-independent to me by its very nature.
This response is continued...
Dig deeper on Open source databases
Related Q&A from Mich Talebzadeh
Sybase expert Mich Talebzadeh explains how to log on in ASE.continue reading
Sybase expert Mich Talebzadeh gives fifteen reasons for why Sybase will definitely be around for years to come.continue reading
Sybase expert Mich Talebzadeh explains the syntax for user-defined Sybase functions.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.