Re: Stored Procedure/Trigger Performance question

And after conferring with several other DBAs since my original message, I've found that is most cases neither is faster with the occasional trigger being faster than a stored proc, and vice versa.

As far as applications go, I've been told that using a stored proc can make your app more resistant to changes in the database since essentially the same stored proc and parameters can be used even if all the of the tables have changed, so long as the same data is required. As well, it's been pointed out that triggers can make a horrible mess to have to maintain if the database has to go through drastic changes as you have that much more places to have to change code.

After conferring with the rest of the team I work on, we've decided that stored procs are the way to go for us as we want to have to change the application as little as possible.

"Martin Haltmayer" <Martin.Haltmayer_at_0800-einwahl.de> wrote in message news:3B01A47A.8DB77867_at_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 Wed May 16 2001 - 12:02:00 CDT