C.J. Date trashes cursors (old news)

pkelley772_at_aol.com (PKelley772) wrote:

>Insomnia strikes again.
>
>In Jan 95 issue of Database Programming and Design, on page 18,
>
>..SQL's cursor-based updates ("positioned UPDATE and DELETE"), which are
>row-level by definition, are a very bad idea: Applications that work
>today might fail tomorrow, when proper integrity support is provided. ...
>Of course, cursor-based operations in general tend to be a bad idea anyway
>in today's distributed and client/server environments, because of their
>negative impact on performance.
>[end quote]
>
>1. I'm not sure, but he seems to be saying that an RDBMS with "proper
>integrity support" would acknowledge his point that "certain multi-row
>updates CANNOT be simulated by a series of single-row updates", and would
>prevent same. I can't find the November or December 94 editions, of DBPD,
>so I can't find examples of such updates. Actually, I'm not really sure
>that this was the point of the statement. Any ideas?
>
>2. What performance hit? I suppose that this has something to do with
>fetching
>/pushing one row at a time across a network. Perhaps programs like this

I think you are right, what I believe he is saying is:

Package the update or delete using a dynamic SQL statement and pass that to the RDBMS. If you use a cursor then you must fetch each row, look at it, update or delete it and send it back over the network.

For the life of me I can't see what he means by proper integrity support without reading the entire article.

>--
>
>DECLARE
> lkey mytable.key1%TYPE;
>
> CURSOR update_mytable IS
> SELECT * FROM mytable
> FOR UPDATE OF col1,col2,col3
> WHERE key1 = lkey
> NOWAIT;
>
>BEGIN
> lkeY := 123;
> FOR mrec IN reserve_mytable LOOP
> -- do a little work here...
> UPDATE mytable SET col1 = 'YO'
> WHERE CURRENT OF reserve_mytable;
>END LOOP;
>END;
>
>should be written differently if a large number of rows are likely to be
>updated. Once again I'm not sure, so any ideas are appreciated.
>
>Thanks for any information.
>
>Paul
>

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ Gordon E. Hooker MACS PCP
25 Clarke Street Ripley Queensland Australia Phone 61-7-32940555
Email gordonh_at_acslink.net.au
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ We are just two lost souls living in a fish bowl Year after year

Pink Floyd Received on Wed Feb 21 1996 - 00:00:00 CET