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.

Solution 1

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

Solution 2

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.

Solution 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.

Solution 4

@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.

Solution 5

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.

View more solutions