Oracle PL/SQL - Are NO_DATA_FOUND Exceptions bad for stored procedure performance?

Question

I'm writing a stored procedure that needs to have a lot of conditioning in it. With the general knowledge from C#.NET coding that exceptions can hurt performance, I've always avoided using them in PL/SQL as well. My conditioning in this stored proc mostly revolves around whether or not a record exists, which I could do one of two ways:

SELECT COUNT(*) INTO var WHERE condition;
IF var > 0 THEN
   SELECT NEEDED_FIELD INTO otherVar WHERE condition;
....

-or-

SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND
....

The second case seems a bit more elegant to me, because then I can use NEEDED_FIELD, which I would have had to select in the first statement after the condition in the first case. Less code. But if the stored procedure will run faster using the COUNT(*), then I don't mind typing a little more to make up processing speed.

Any hints? Am I missing another possibility?

EDIT I should have mentioned that this is all already nested in a FOR LOOP. Not sure if this makes a difference with using a cursor, since I don't think I can DECLARE the cursor as a select in the FOR LOOP.

Answer 1

Yes, you're missing using cursors

DECLARE
  CURSOR foo_cur IS 
    SELECT NEEDED_FIELD WHERE condition ;
BEGIN
  OPEN foo_cur;
  FETCH foo_cur INTO foo_rec;
  IF foo_cur%FOUND THEN
     ...
  END IF;
  CLOSE foo_cur;
EXCEPTION
  WHEN OTHERS THEN
    CLOSE foo_cur;
    RAISE;
END ;

admittedly this is more code, but it doesn't use EXCEPTIONs as flow-control which, having learnt most of my PL/SQL from Steve Feuerstein's PL/SQL Programming book, I believe to be a good thing.

Whether this is faster or not I don't know (I do very little PL/SQL nowadays).

Answer 2

If it's important you really need to benchmark both options!

Having said that, I have always used the exception method, the reasoning being it's better to only hit the database once.

Answer 3

An alternative to @Steve's code.

DECLARE
  CURSOR foo_cur IS 
    SELECT NEEDED_FIELD WHERE condition ;
BEGIN
  FOR foo_rec IN foo_cur LOOP
     ...
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END ;

The loop is not executed if there is no data. Cursor FOR loops are the way to go - they help avoid a lot of housekeeping. An even more compact solution:

DECLARE
BEGIN
  FOR foo_rec IN (SELECT NEEDED_FIELD WHERE condition) LOOP
     ...
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END ;

Which works if you know the complete select statement at compile time.

Answer 4

I would not use an explicit cursor to do this. Steve F. no longer advises people to use explicit cursors when an implicit cursor could be used.

The method with count() is unsafe. If another session deletes the row that met the condition after the line with the count(), and before the line with the "select ... into", the code will throw an exception that will not get handled.

The second version from the original post does not have this problem, and it is generally preferred.

That said, there is a minor overhead using the exception, and if you are 100% sure the data will not change, you can use the count(*), but I recommend against it.

I ran these benchmarks on Oracle 10.2.0.1 on 32 bit Windows. I am only looking at elapsed time. There are other test harnesses that can give more details (such as latch counts and memory used).

SQL>create table t (NEEDED_FIELD number, COND number);

Table created.

SQL>insert into t (NEEDED_FIELD, cond) values (1, 0);

1 row created.

declare otherVar number; cnt number; begin for i in 1 .. 50000 loop select count(*) into cnt from t where cond = 1;

 if (cnt = 1) then
   select NEEDED_FIELD INTO otherVar from t where cond = 1;
 else
   otherVar := 0;
 end if;

end loop; end; /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.70

declare otherVar number; begin for i in 1 .. 50000 loop begin select NEEDED_FIELD INTO otherVar from t where cond = 1; exception when no_data_found then otherVar := 0; end; end loop; end; /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.06

Answer 5

@DCookie

I just want to point out that you can leave off the lines that say

EXCEPTION
WHEN OTHERS THEN
RAISE;

You'll get the same effect if you leave off the exception block all together, and the line number reported for the exception will be the line where the exception is actually thrown, not the line in the exception block where it was re-raised.

Answer 6

Stephen Darlington makes a very good point, and you can see that if you change my benchmark to use a more realistically sized table if I fill the table out to 10000 rows using the following:

begin for i in 2 .. 10000 loop insert into t (NEEDED_FIELD, cond) values (i, 10); end loop; end;

Then re-run the benchmarks. (I had to reduce the loop counts to 5000 to get reasonable times).

declare otherVar number; cnt number; begin for i in 1 .. 5000 loop select count(*) into cnt from t where cond = 0;

 if (cnt = 1) then
   select NEEDED_FIELD INTO otherVar from t where cond = 0;
 else
   otherVar := 0;
 end if;

end loop; end; /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.34

declare otherVar number; begin for i in 1 .. 5000 loop begin select NEEDED_FIELD INTO otherVar from t where cond = 0; exception when no_data_found then otherVar := 0; end; end loop; end; /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.10

The method with the exception is now more than twice as fast. So, for almost all cases,the method:

SELECT NEEDED_FIELD INTO var WHERE condition; EXCEPTION WHEN NO_DATA_FOUND....

is the way to go. It will give correct results and is generally the fastest.

Answer 7

May be beating a dead horse here, but I bench-marked the cursor for loop, and that performed about as well as the no_data_found method:

declare otherVar number; begin for i in 1 .. 5000 loop begin for foo_rec in (select NEEDED_FIELD from t where cond = 0) loop otherVar := foo_rec.NEEDED_FIELD; end loop; otherVar := 0; end; end loop; end;

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.18

Answer 8

Thank you all very much for your help!

Further, I've come across something annoying and I can't find anything useful in Feuerstein or on the web. I've changed my FOR LOOP to use a cursor, but now I want to use other cursors for more selects that I need to perform various conditions within the FOR LOOP. Example:

DECLARE CURSOR loopCursor IS SELECT NEEDED_FIELD FROM TABLE WHERE condition;
        CURSOR condition1Cursor(p_parm TABLE.NEEDED_FIELD%TYPE) IS
               SELECT OTHER_FIELD INTO workVar FROM TABLE 
                      WHERE condition_field = p_parm;
BEGIN
    FOR thisIteration IN loopCursor LOOP
       OPEN condition1Cursor(thisIteration.NEEDED_FIELD);
       FETCH condition1Cursor INTO testVar;
       IF condition1Cursor%FOUND THEN
       ....
       END IF;
    END LOOP;
END;

Problem is, I get a "Cursor is already open" error on the OPEN for condition1Cursor. What stupid thing am I doing wrong now?

Thanks again.

Answer 9

Since SELECT INTO assumes that a single row will be returned, you can use a statement of the form:

SELECT MAX(column)
  INTO var
  FROM table
 WHERE conditions;

IF var IS NOT NULL
THEN ...

The SELECT will give you the value if one is available, and a value of NULL instead of a NO_DATA_FOUND exception. The overhead introduced by MAX() will be minimal-to-zero since the result set contains a single row. It also has the advantage of being compact relative to a cursor-based solution, and not being vulnerable to concurrency issues like the two-step solution in the original post.

Answer 10

Rather than having nested cursor loops a more efficient approach would be to use one cursor loop with an outer join between the tables.

BEGIN
    FOR rec IN (SELECT a.needed_field,b.other_field
                  FROM table1 a
                  LEFT OUTER JOIN table2 b
                    ON a.needed_field = b.condition_field
                 WHERE a.column = ???)
    LOOP
       IF rec.other_field IS NOT NULL THEN
         -- whatever processing needs to be done to other_field
       END IF;
    END LOOP;
END;

Answer 11

you dont have to use open when you are using for loops.

declare cursor cur_name is select * from emp; begin for cur_rec in cur_name Loop dbms_output.put_line(cur_rec.ename); end loop; End ;

or

declare cursor cur_name is select * from emp; cur_rec emp%rowtype; begin Open cur_name; Loop Fetch cur_name into Cur_rec; Exit when cur_name%notfound; dbms_output.put_line(cur_rec.ename); end loop; Close cur_name; End ;