Sql – Oracle PL/SQL – Are NO_DATA_FOUND Exceptions bad for stored procedure performance

exceptionoracleplsqlsql

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.

Best Answer

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

Related Solutions

Java – What are the effects of exceptions on performance in Java

It depends how exceptions are implemented. The simplest way is using setjmp and longjmp. That means all registers of the CPU are written to the stack (which already takes some time) and possibly some other data needs to be created... all this already happens in the try statement. The throw statement needs to unwind the stack and restore the values of all registers (and possible other values in the VM). So try and throw are equally slow, and that is pretty slow, however if no exception is thrown, exiting the try block takes no time whatsoever in most cases (as everything is put on the stack which cleans up automatically if the method exists).

Sun and others recognized, that this is possibly suboptimal and of course VMs get faster and faster over the time. There is another way to implement exceptions, which makes try itself lightning fast (actually nothing happens for try at all in general - everything that needs to happen is already done when the class is loaded by the VM) and it makes throw not quite as slow. I don't know which JVM uses this new, better technique...

...but are you writing in Java so your code later on only runs on one JVM on one specific system? Since if it may ever run on any other platform or any other JVM version (possibly of any other vendor), who says they also use the fast implementation? The fast one is more complicated than the slow one and not easily possible on all systems. You want to stay portable? Then don't rely on exceptions being fast.

It also makes a big difference what you do within a try block. If you open a try block and never call any method from within this try block, the try block will be ultra fast, as the JIT can then actually treat a throw like a simple goto. It neither needs to save stack-state nor does it need to unwind the stack if an exception is thrown (it only needs to jump to the catch handlers). However, this is not what you usually do. Usually you open a try block and then call a method that might throw an exception, right? And even if you just use the try block within your method, what kind of method will this be, that does not call any other method? Will it just calculate a number? Then what for do you need exceptions? There are much more elegant ways to regulate program flow. For pretty much anything else but simple math, you will have to call an external method and this already destroys the advantage of a local try block.

See the following test code:

public class Test {
    int value;


    public int getValue() {
        return value;
    }

    public void reset() {
        value = 0;
    }

    // Calculates without exception
    public void method1(int i) {
        value = ((value + i) / i) << 1;
        // Will never be true
        if ((i & 0xFFFFFFF) == 1000000000) {
            System.out.println("You'll never see this!");
        }
    }

    // Could in theory throw one, but never will
    public void method2(int i) throws Exception {
        value = ((value + i) / i) << 1;
        // Will never be true
        if ((i & 0xFFFFFFF) == 1000000000) {
            throw new Exception();
        }
    }

    // This one will regularly throw one
    public void method3(int i) throws Exception {
        value = ((value + i) / i) << 1;
        // i & 1 is equally fast to calculate as i & 0xFFFFFFF; it is both
        // an AND operation between two integers. The size of the number plays
        // no role. AND on 32 BIT always ANDs all 32 bits
        if ((i & 0x1) == 1) {
            throw new Exception();
        }
    }

    public static void main(String[] args) {
        int i;
        long l;
        Test t = new Test();

        l = System.currentTimeMillis();
        t.reset();
        for (i = 1; i < 100000000; i++) {
            t.method1(i);
        }
        l = System.currentTimeMillis() - l;
        System.out.println(
            "method1 took " + l + " ms, result was " + t.getValue()
        );

        l = System.currentTimeMillis();
        t.reset();
        for (i = 1; i < 100000000; i++) {
            try {
                t.method2(i);
            } catch (Exception e) {
                System.out.println("You'll never see this!");
            }
        }
        l = System.currentTimeMillis() - l;
        System.out.println(
            "method2 took " + l + " ms, result was " + t.getValue()
        );

        l = System.currentTimeMillis();
        t.reset();
        for (i = 1; i < 100000000; i++) {
            try {
                t.method3(i);
            } catch (Exception e) {
                // Do nothing here, as we will get here
            }
        }
        l = System.currentTimeMillis() - l;
        System.out.println(
            "method3 took " + l + " ms, result was " + t.getValue()
        );
    }
}

Result:

method1 took 972 ms, result was 2
method2 took 1003 ms, result was 2
method3 took 66716 ms, result was 2

The slowdown from the try block is too small to rule out confounding factors such as background processes. But the catch block killed everything and made it 66 times slower!

As I said, the result will not be that bad if you put try/catch and throw all within the same method (method3), but this is a special JIT optimization I would not rely upon. And even when using this optimization, the throw is still pretty slow. So I don't know what you are trying to do here, but there is definitely a better way of doing it than using try/catch/throw.

Sql – Function vs. Stored Procedure in SQL Server

Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e., no INSERT or UPDATE statements allowed).

A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set.

A point worth noting from comments, which summarize the answer. Thanks to @Sean K Anderson:

Functions follow the computer-science definition in that they MUST return a value and cannot alter the data they receive as parameters (the arguments). Functions are not allowed to change anything, must have at least one parameter, and they must return a value. Stored procs do not have to have a parameter, can change database objects, and do not have to return a value.

Best Answer

Related Solutions

Java – What are the effects of exceptions on performance in Java

Sql – Function vs. Stored Procedure in SQL Server

Related Topic