This article is about a well-known fact about the poor performance of exception handling.
Yes, the exception handling is rather slow, however, it is not necessary to try to avoid exceptions whenever possible, and by any means. For example, I often see that people are trying to avoid them even in cases of search by primary key where probability of receiving “no_data_found” is minimal.
In general, we should consider the probable frequency of exceptions and “overhead”, which is added by the chosen way around the exceptions.
Let me explain this with an example, which I mentioned earlier: suppose we have a code that returns a field from the table by “pk” and it returns “null” in case there is no such entry.
create table t_test(a primary key, b) as select level,level from dual connect by level<=1e5;
Lets create a standard function for tests:
create or replace function f1(p in number) return number as res number; begin select/*+ F1 */ b into res from t_test t where t.a=p; return res; exception when no_data_found then return null; end;
The most common options to avoid the exceptions mechanism in these cases are the followings:
By the way, don’t use this in case your cursor can not have more than one entry:
Otherwise, there will be attempts of the second iteration, which you can observe in the profiler.
Here I would like to propose my own option for this:
And now lets carry out a basic test by executing these functions for the test table:
declare v integer; v_start integer:= 1; v_end integer:= 100000; l_timer integer := dbms_utility.get_time; procedure print(msg varchar2) is begin dbms_output.put_line(to_char((dbms_utility.get_time-l_timer)/100,'9990.00')||' '||msg); l_timer:=dbms_utility.get_time; end; begin print('start'); for i in v_start..v_end loop v:=f1(i); end loop; print('1'); for i in v_start..v_end loop v:=f2(i); end loop; print('2'); for i in v_start..v_end loop v:=f3(i); end loop; print('3'); for i in v_start..v_end loop v:=f4(i); end loop; print('4'); end;
As a result, we get the following ratio:
|Variant 1(with exception)||3.03|
|Variant 2(with cycle)||3.62|
|Variant 3(with min)||3.34|
|Variant 4(скалярный подзапрос)||3.10|
As you can see, the original query is the fastest in case the exceptions are not called! Lets now check it with different percents of exceptions: exceptions will be for queries с i<=0, the total number of calls will be 100001, I will change v_start and v_end in pairs: (-5000, 95000), (10000, 90000), (-50000, 50000), (-90000, 10000):
Summary table of multiple comparisons:
|Variant 1(with exception)||3.04||3.12||3.16||3.82||4.51|
|Variant 2(with cycle)||3.18||3.21||3.20||3.51||3.85|
|Variant 3(with min)||3.37||3.34||3.29||3.25||3.18|
|Variant 4(скалярный подзапрос)||3.12||3.06||3.03||2.98||2.94|
What conclusions can be drawn from this:
- As you can see, 5% of exceptions is a kind of turning point for this table, when the standard option with exception becomes less effective than the option with subquery (by about ~4.5% to be precise), and about ~10% than the remaining two.
- Options with “min” and a cycle as a whole are worse than the option with a subquery.
- Options with subquery and “min” accelerate as the number of “empty” queries increases.