Effect of a check constraint on Performance

[farrokhp]

Let's say I need to add a check constraint like below to employee table with 2 million records
My questions is :

What's the Rules of thumb , how much performance loose I should expect for each DML after adding this check constraint

How do I check the effects systematically before I apply this to Production environment



ALTER TABLE employees ADD CONSTRAINT check_comp
CHECK (salary + (commission_pct*salary) <= 5000)

[Shestakov]

In common the anwer is --> YES constraint will have effect on perfomance.

How u can check this:

SQL> create table employees
2 ( emp_id number,
3 salary number,
4 commission_pct number);

Table created.

Elapsed: 00:00:00.00
SQL>
SQL> declare
2 i number;
3 begin
4 for i in 1..10000 loop
5 insert into employees
6 ( emp_id, salary, commission_pct)
7 values (i, mod(i,30)*mod(i,10), 0.2);
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.08

SQL> ALTER TABLE employees ADD CONSTRAINT check_comp
2 CHECK (salary + (commission_pct*salary) <= 5000);

Table altered.

Elapsed: 00:00:00.00

SQL> declare
2 i number;
3 begin
4 for i in 10000..20000 loop
5 insert into employees
6 ( emp_id, salary, commission_pct)
7 values (i, mod(i,20)*mod(i,10), 0.2);
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.01

In this example we have a degradation of perfomance ~ 25-30%.

[sandycrab]

Classic example Shestakov. I'm doing the same thing in my applications.
Sandy

[farrokhp]

Thanks Shestakov. It is a good and simple example.

Now what is te best practioce in real life for this case?

What about using trigger instead of check const? Performance better?

Let's say manager is angry with %30 decline but they want this check. What's the best remedy?

[Shestakov]

No perfomace if we are using triggers as a rule more bag, then we are using constaints:

SQL> drop table employees;

Table dropped.

Elapsed: 00:00:00.00
SQL>
SQL> create table employees
2 ( emp_id number,
3 salary number,
4 commission_pct number);

Table created.

Elapsed: 00:00:00.00
SQL>
SQL> declare
2 i number;
3 begin
4 for i in 1..50000 loop
5 insert into employees
6 ( emp_id, salary, commission_pct)
7 values (i, mod(i,30)*mod(i,10), 0.2);
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.05
SQL>
SQL> ALTER TABLE employees ADD CONSTRAINT check_comp
2 CHECK (salary + (commission_pct*salary) <= 5000);

Table altered.

Elapsed: 00:00:00.00
SQL>
SQL> declare
2 i number;
3 begin
4 for i in 100000..150000 loop
5 insert into employees
6 ( emp_id, salary, commission_pct)
7 values (i, mod(i,20)*mod(i,10), 0.2);
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.00
SQL>
SQL> alter table employees drop constraint check_comp;

Table altered.

Elapsed: 00:00:00.00
SQL>
SQL> truncate table employees;

Table truncated.

Elapsed: 00:00:00.04
SQL>
SQL> create or replace trigger bi_employees
2 before insert on employees
3 for each row
4 begin
5 if :new.salary + :new.commission_pct*:new.salary > 5000 then
6 raise_application_error(-20100, 'Wrong salary value, cant insert record');
7 end if;
8 end;
9 /

Trigger created.

Elapsed: 00:00:00.00
SQL> show errors;
No errors.
SQL>
SQL> declare
2 i number;
3 begin
4 for i in 100000..150000 loop
5 insert into employees
6 ( emp_id, salary, commission_pct)
7 values (i, mod(i,20)*mod(i,10), 0.2);
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.03

without any constraints and triggers: 00:00:04.05
with constraint : 00:00:05.00 (+ ~20%)
with trigger : 00:00:06.03 (+ ~34%)
U can compare all three results.

NOTE
this result valid only for this test.
in real situations results maybe another, but common rule will be the same.

[TimHall]

You have to make an individual assessment for each system to see what the performance issues are. I would suggest using the most aggressive constraint checking you can as data integrity is worth the extra processing. That said, I've worked on realtime control systems where performance issues meant the database had virtually no constraints. It all depends...

Cheers