Re: Cursor Replacement

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 05/19/04 

Date: Wed, 19 May 2004 11:08:32 -0700

>> I'm asking for an ALGORITHM that will replace cursors <<

This is a question for the database theory newsgroup. My computational
theory is weak, but I seem to remember that you can prove that an
expression in primitive recursive functions (roughly equivalent to pure
SQL) and be uiquely transformed into single stack automata code (roughly
equivalent to a cursor and 3GL, but not the other way around.

That would mean that no such algorithm is even possible.

Now, it the real world you need to see code to re-write it. In the case
of cursors, I find only a few situations:

1) The newbie has not learned SQL yet and writes cursors in the style of
the 3GL language they know best. Worst example posted in recent memory:
loop to build a temp table of keys, then loop inside a loop thru a
second table to delete rows with those keys. Basically a tape file
merge from the 1950's re-cast in SQL.

2) The schema has design flaws that require/are best handled by a
cursor/3GL code. Example: an adjacency list model tree traversal.

3) This is an NP-complete problem, and we ned only the first solution
within certain limits. Example: a traveling salesman problem solution
where the total distance is less than (m) miles. Such problems occur
less than 1% of the time in the real world.

If you will post DDL, data, specs and code we can do something.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Cursor Replacement
    ... > This is a question for the database theory newsgroup. ... > of cursors, I find only a few situations: ... > loop to build a temp table of keys, then loop inside a loop thru a ... > second table to delete rows with those keys. ...
    (microsoft.public.sqlserver.programming)
  • Re: GCC 4.0 Ada.Containers Cursor danger.
    ... message headers, I think you are responding to my post?) ... able fast to do whatever i want whithout cursors under ADT. ... There is a performance cost if a library forces its user to ...
    (comp.lang.ada)
  • Re: GCC 4.0 Ada.Containers Cursor danger.
    ... what makes you sure that the programmers writing the procedure for Process in Generic_Iterate above will not use the wrong key in their procedure? ... more safe than Cursors? ... reinvent and reconsider every cursor operation as a smart pointer ... after the loop. ...
    (comp.lang.ada)
  • More on closing Oracle ref cursors
    ... My script runs through a loop many times, and each time through it calls ... the loop I run out of ref cursors; ... the stored procs aren't getting closed by Perl for some reason. ... The way I've built my system, I have a wrapper class that stores a DBI ...
    (perl.dbi.users)
  • Re: Oracle cursor help
    ... Switching context from PL/SQL for the loop to SQL for the delete takes time ... ctr = 0; ... DELETE/WHERE clauses consume too many resources. ... WHERE clauses defeated the efficiency of cursors ...) ...
    (perl.dbi.users)