CODEWITHSUNDEEP
sqlperformanceoracle-database
PhatHV
PhatHV

Reputation: 8141

Should I use exception instead count(*) in functions?

I am new to Oracle and I have two functions which will used with high frequency. And I wonder which is better between them.

This one:

FUNCTION GET_MY_MONEY (myType IN NUMBER) RETURN NUMBER AS
    var_amount   NUMBER;
    var_result   NUMBER;
BEGIN
    var_result := 0;
    var_amount := 0;
    SELECT amount INTO var_amount FROM mytable WHERE type = myType AND sysdate >= date_from AND sysdate <= date_to;
    var_result := var_amount*1000;
    RETURN var_result;
EXCEPTION
    WHEN OTHERS THEN
        RETURN 0;
END;

or this one:

FUNCTION GET_MY_MONEY (myType IN NUMBER) RETURN NUMBER AS
    var_count    NUMBER;
    var_amount   NUMBER;
    var_result   NUMBER;
BEGIN
    var_result := 0;
    var_count := 0;
    var_amount := 0;
    SELECT count(*) INTO var_count FROM mytable WHERE type = myType AND sysdate >= date_from AND sysdate <= date_to;
    IF (var_count > 0) THEN
        SELECT amount INTO var_amount FROM mytable WHERE type = myType AND sysdate >= date_from AND sysdate <= date_to;
        var_result := var_amount*1000;
        RETURN var_result;
    ELSE RETURN 0; END IF;
EXCEPTION
    WHEN OTHERS THEN
        RETURN 0;
END;

which is better for performance? which is return faster when they are called?

Thanks in advance.

Upvotes: 1

Views: 148

Answers (3)

Art
Art

Reputation: 5792

In addition:

DECLARE
  v_retVal NUMBER:= 0;
  v_sal    NUMBER:= 0;
BEGIN
  SELECT nvl(sum(sal),0) as sal INTO v_sal 
    FROM scott.emp 
   WHERE deptno = 40; -- no data for deptno = 40 

   -- No exception needed, function will always return 0 or value --
   v_retVal:= (CASE WHEN v_sal = 0 THEN 0 ELSE v_sal*1000 END);

   dbms_output.put_line (v_retVal);

  -- RETURN v_retVal;
END;
/

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231791

In general, it depends. How frequently will you call the function and pass in a myType value that causes the query to return 0 rows?

If in the 99.9% of calls, the query will return exactly 1 row, then the second approach will run the query to be executed twice. While it's likely that the second call won't cause the function to be twice as expensive as the first since the blocks you're interested in are almost guaranteed to be cached, the second approach will almost certainly be substantially slower.

On the other hand, if a large fraction of calls will involve a myType value that doesn't return a row, the second approach will frequently not have to execute the query a second time. And the first approach will incur the overhead of handling the exception a large fraction of the time which will almost certainly be more expensive than the second query.

In most cases, the more efficient solution will be obvious based on the probability that 0 rows will be returned. Most of the time, the function is only called when the caller is pretty confident that the myType value they would be passing in is valid so the first approach would end up more efficient. As the fraction of calls that result in 0 rows being found increases, the second approach gets more efficient. Where that line is will depend on a number of factors not least of which being your table, your data, your hardware, and your Oracle version. You'd need to run a benchmark test to determine whether for your particular code the line of demarcation is 10% or 20% or 90%.

Upvotes: 4

Florin Ghita
Florin Ghita

Reputation: 17643

The question was edited but SELECT amount INTO var_amount ... will fail if the there are more than one row.(maybe you want to select sum(amount)).

First approach is better because:

  • is far more intelligible
  • you don't scan the table twice.
  • in the second you get var_amount but you don't use it.
  • the count(*) is useless, you can just select nvl(sum(amount),0) as amount

However, you must assign a value to date variables(date_from and date_to) or put them as parameters. And you can return an expression, ie: RETURN var_count*1000;

Upvotes: 2

Related Questions