Does Oracle encourage developers to use cursors?

[gnews...@gmail.com]

I've asked this at asp.net forum, but I think we have more gurus here to get more educational, informative information about my confusion.

I am new to Oracle, but not new to SQL Server and .NET application development. I think it is a general understanding that we should try to avoid using cursors as much as possible at least with sql server, because it just doesn't perform that well.

But if we read any Oracle PL/SQL primer, I bet you will be reading about cursors after cursors.

So, is cursor simply the established practice of Oracle to get our data? Is oracle cursor internally different from that of SQL Server such that it doesn't cause any performance hit?

Any thoughts? Thank you.

[John Hurley]

gnew:

# So, is cursor simply the established practice of Oracle to get our
data?

Yup.

# Is oracle cursor internally different from that of SQL Server such

that it doesn't cause any performance hit?

Don't think you are going to get too many people here chiming in on
any "internal differences" ... cursors in Oracle been around for well
before any reasonable person considered moving any significant
application onto SQL Server.

Lots of good reading in the Oracle land ...

Start with the free concepts manual from Oracle.

Try this one on for size ( by Tom Kyte ): Expert Oracle Database
Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques
and Solutions

Another good architecture book by Jonathan Lewis: Oracle Core:
Essential Internals for DBAs and Developers

Try just to learn how Oracle works do not try to compare everything
with any other database.

[joel garry]

Also see http://www.oracle.com/technetwork/articles/dotnet/williams-refcursors-092375.html

I second what John referenced, and emphasize there are things that
work significantly different in Oracle. Pay especial attention to the
transaction management chapters in the concepts manual. Even some
experienced developers do it wrong. Manuals are online at
tahiti.oracle.com.

jg
--
@home.com is bogus.
http://www.zdnet.com/oracles-hurd-world-is-drowning-in-data-warns-of-overload-7000006141/

[Jonathan Lewis]

If you want a useful answer to this question you probably ought to start
from the assumption that the people you're talking to have no idea how SQL
Server handles cursors (or what the term even means in SQL Server) and why
they are considered slow. So tell us about cursors in SQL Server and we can
tell you about the things that look different in Oracle.

It may come down to something as simple as "cursors imply row by row
processing, which tends to be much slower than set-wise processing with
simple SQL" - that's a statement that is essentially true in either
environment. On the other hand you may have some completely different
comparison in mind when you pose your question.

--

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

<gnews...@gmail.com> wrote in message
news:4941cf9b-3831-4efe...@googlegroups.com...

[zigz...@yahoo.com]

I am an oracle dba/developer and am learning sql server t-sql. I have also read many articles in sql server which sat avoid cursors but I did not find anything in sql server internals which make cursors slow (coming from oracle back ground). I have seen in sql server people avoid cursor based code and then write round about set based code which is even slower than cursor based code!!!!
Yes cursor based code is row by row and cannot take advantage of any parallelization, set based code can be parallelized by sql server engine, so it can be more efficient.

However, if your logic is serial; cursor based code is not inefficient!! I do not find anything in sql server internals which makes sql server cursors very inefficient !!!

[Robert Klemme]

On 24.10.2012 12:50, Jonathan Lewis wrote:
> If you want a useful answer to this question you probably ought to start
> from the assumption that the people you're talking to have no idea how SQL
> Server handles cursors (or what the term even means in SQL Server) and why
> they are considered slow. So tell us about cursors in SQL Server and we can
> tell you about the things that look different in Oracle.
>
> It may come down to something as simple as "cursors imply row by row
> processing, which tends to be much slower than set-wise processing with
> simple SQL" - that's a statement that is essentially true in either
> environment. On the other hand you may have some completely different
> comparison in mind when you pose your question.

Or it could even boil down to the advice to mostly restrict cursor use
to stored procedures and functions and not use them from a client
application to avoid the overhead of relatively slow network
communication _per row_. (Although I believe some drivers nowadays do
some optimization with client side caching etc.)

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

[Harry Tuttle]

But isn't any result that is returned to the client technically some kind of "cursor"?

The server has to built up a cursor and then return the rows in there to the client.
There might be some optimization on the server to pack rows into larger packets, but to
"keep track" of what has been sent to the client I can't see any other option for the
server to use a "cursor".

[Jonathan Lewis]

----- Original Message -----
From: "Harry Tuttle" <SOZRBL...@spammotel.com>
|
| But isn't any result that is returned to the client technically some kind
of "cursor"?
|

Yes - some kind. (At least, when thinking of select statements).

| The server has to built up a cursor and then return the rows in there to
the client.

This demonstrates why it's worth asking about SQL Server "cursors". For
simple select statements there isn't a "there" for rows to be "in" - they
are acquired dynamically and read-consistently as needed. A cursor, in
Oracle, is not a pre-built result set waiting to be transferred to the
client. If that is the implementation in SQL Server then that's one reason
why cursors may (sometimes) be less efficient than one might hope. (Select
for update, and some selects with order by or group by clauses may require
completely pre-built result sets to be acquired, of course.)