result of trigger timing test, comments requested.

From: Dave Mausner <dmausner_at_brauntech.com>
Date: Wed, 17 Aug 1994 01:29:38 GMT
Message-ID: <1994Aug17.012938.7541_at_nntpxfer.psi.com>

for your information: test results run on hp 9000 with 7.0.16 seem to show that a simple trigger slows down an insert by a factor of ten. an idle system was used and the runs were repeated.

create table t1 (v1 number);
create table t2 (v2 number);
insert into t2 values(0);

<start timer>

begin

	for i in 1..2000 loop
		insert into t1 values(i);
	end loop;

end;
/
<timer average (4 consecutive runs) = 2.9 seconds realtime>

create trigger tt1 before insert on t1 for each row begin

        update t2 set v2 = v2 + :new.t1;
end;
/
insert into t1 values(0) /* force trigger load */ ;

<start timer>

begin

	for i in 1..2000 loop
		insert into t1 values(i);
	end loop;

end;
/
<timer average (4 consecutive runs) = 30.0 seconds realtime>

in other words, insert with a simple trigger is 10 times slower than an insert without a trigger. I did not expect an order of magnitude. can anyone duplicate/contradict/explain this result? regards.

--
Dave Mausner, Senior Consultant, Braun Technology Group, Chicago.

Received on Wed Aug 17 1994 - 03:29:38 CEST