What is wrong with Cursors?

Question

SQL Server developers consider Cursors a bad practise , except under some circumstances. They believe that Cursors do not use the SQL engine optimally since it is a procedural construct and defeats the Set based concept of RDBMS.

However, Oracle developers do not seem to recommend against Cursors. Oracle's DML statements themselves are implicit cursors.

Why this difference in approach? Is it because of the way these 2 products are made, or does this advice apply to both products?

Answer 1

What's wrong with cursors is that they are often abused, both in Oracle and in MS SQL.

Cursor are for keeping a stable resultset which you can retrieve row-by-row. They are implicitly created when your query is run, and closed when it's finished.

Of course keeping such a resultset requires some resources: locks, latches, memory, even disk space.

The faster these resources are freed, the better.

Keeping a cursor open is like keeping a fridge door open

You don't do it for hours without necessity, but it does not mean you should never open your fridge.

That means that:

• You don't get your results row-by-row and sum them: you call the SQL's SUM instead.
• You don't execute whole query and get the first results from the cursor: you append a rownum <= 10 condition to your query

, etc.

As for Oracle, processing your cursors inside a procedure requires infamous SQL/PLSQL context switch which happens every time you get a result of an SQL query out of the cursor.

It involves passing large amounts of data between threads and synchronizing the threads.

This is one of the most irritating things in Oracle.

One of the less evident consequences of that behaviour is that triggers in Oracle should be avoided if possible.

Creating a trigger and calling a DML function is equal to opening the cursor selecting the updated rows and calling the trigger code for each row of this cursor.

Mere existence of the trigger (even the empty trigger) may slow down a DML operation 10 times or more.

A test script on 10g:

SQL> CREATE TABLE trigger_test (id INT NOT NULL)
  2  /

Table created

Executed in 0,031 seconds
SQL> INSERT
  2  INTO   trigger_test
  3  SELECT level
  4  FROM   dual
  5  CONNECT BY
  6     level <= 1000000
  7  /

1000000 rows inserted

Executed in 1,469 seconds
SQL> COMMIT
  2  /

Commit complete

Executed in 0 seconds
SQL> TRUNCATE TABLE trigger_test
  2  /

Table truncated

Executed in 3 seconds
SQL> CREATE TRIGGER trg_test_ai
  2  AFTER INSERT
  3  ON trigger_test
  4  FOR EACH ROW
  5  BEGIN
  6     NULL;
  7  END;
  8  /

Trigger created

Executed in 0,094 seconds
SQL> INSERT
  2  INTO   trigger_test
  3  SELECT level
  4  FROM   dual
  5  CONNECT BY
  6     level <= 1000000
  7  /

1000000 rows inserted

Executed in 17,578 seconds

1.47 seconds without a trigger, 17.57 seconds with an empty trigger doing nothing.