Re: Does Oracle encourage developers to use cursors?
From: Harry Tuttle <SOZRBLNTLEEE_at_spammotel.com>
Date: Sat, 27 Oct 2012 11:14:23 +0200
Message-ID: <af1mv2Fqb8iU1_at_mid.individual.net>
Robert Klemme wrote on 27.10.2012 10:54:
> 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.)
>
Date: Sat, 27 Oct 2012 11:14:23 +0200
Message-ID: <af1mv2Fqb8iU1_at_mid.individual.net>
Robert Klemme wrote on 27.10.2012 10:54:
> 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.)
>
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". Received on Sat Oct 27 2012 - 11:14:23 CEST