Re: Stored Procedure/Trigger Performance question

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Tue, 15 May 2001 23:49:46 +0200
Message-ID: <3B01A47A.8DB77867@0800-einwahl.de>

Please don't crosspost.

The trigger is faster in the following example by a factor of 1.5 on a Linux SuSE 7.0, Oracle 8.1.7:

set echo on
set feedback on
set linesize 1000
set pagesize 0
set trimspool on

spool trigg_proc.sql.lst

drop table trigg_proc1;
create table trigg_proc1 (n number);

drop table trigg_proc2;
create table trigg_proc2 (n number);

create or replace procedure trigg_proc (

        i_n in trigg_proc1.n%type
) is
begin

	insert into trigg_proc1 (n) values (i_n);
	insert into trigg_proc2 (n) values (2 * i_n);

end trigg_proc;
/

show errors

create or replace trigger trigg_proc_t
after insert
on trigg_proc1
for each row
begin

        insert into trigg_proc2 (n) values (2 * :new.n); end trigg_proc_t;
/

set timing on

truncate table trigg_proc1;
truncate table trigg_proc2;
begin

	for j in 1.. 1000 loop
		trigg_proc (j);
	end loop;
	commit;

end;
/

truncate table trigg_proc1;
truncate table trigg_proc2;
begin

	for j in 1.. 1000 loop
		insert into trigg_proc1 (n) values (j);
	end loop;
	commit;

end;
/

spool off Received on Tue May 15 2001 - 16:49:46 CDT