In the past, I have read about the performance of exception handlers and all that is said is, There is performance overhead. Okay, the generic answer of “performance overhead” tells us nothing.
One of the great pleasures of what I do is code reviews. I get to learn techniques from other developers and I get to pass on some of my knowledge. Code reviews are a win-win all around.
Recently, I reviewed some code that had the following code block to enforce a unique constraint. (tables and variables have been changed to protect intellectual property) There is a unique constraint on ins_demo.column1, so the select will do a index unique scan for every select. The use case for this example is a transactional system where different users will be imputing data.
begin
select count(*)
into dummy
from ins_demo
where column1 = p_value;
if dummy = 0 then
insert into ins_demo values (p_value);
return true;
else
return false;
end if;
end;
My knee jerk reaction is to change this from a select+insert to insert with an exception handler to improve performance.
Begin
insert into ins_demo values (p_value);
return true;
exception when dup_val_on_index then
return false;
end;
Before making recommendations, make sure you first do no harm. So, in this case I decided to run some performance test against the select+insert and the insert with an exception. The test will attempt to do 100,000 inserts starting will 100% success to 100% failures.
The results of the test have a few surprises.
Select+Insert 100% success:
execution time = 21.93 seconds
Insert CPU= 9.6
Select CPU = 2.55
Insert with exception 100% success:
execution time = 14.29 seconds
Insert CPU = 9.95
Recursive CPU = 8.96
Select+Insert 100% reject due to duplicate rows:
execution time = 5.86 seconds
Insert CPU = 0
Select CPU = 1.73
Insert with exception 100% rejected due to duplicate rows:
execution time = 135.03 seconds
Insert CPU = 16.85
Exception CPU = 20.7
We can learn a few things from this.
1) In the select + insert method, as the number of rejections increased execution time decreased, insert CPU decreased and select CPU decreased. This is due to a couple of things. A unique index scan is faster when the data is found and as the number of rejected rows increase due to unique index violation, the fewer times an insert is executed.
2) In the insert with exception handler, execution time increases as the number of rows are rejected due to unique index violations increases. This is because Oracle attempts to do the insert and then must roll the transaction back.
I will post a more detailed analysis with the raw performance data tomorrow.