Effect of a check constraint on Performance
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Effect of a check constraint on Performance

  1. #1
    Join Date
    Nov 2000
    Posts
    416
    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)


    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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%.

  3. #3
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Classic example Shestakov. I'm doing the same thing in my applications.
    Sandy
    "Greatest Rewards come only with Greatest Commitments!"

  4. #4
    Join Date
    Nov 2000
    Posts
    416
    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?

    An ounce of prevention is worth a pound of cure

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  6. #6
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width