| Pass parameter to DML or new procedure? [message #65984] |
Tue, 15 February 2005 11:55  |
Xenofon Grigoriadis
Messages: 33
Registered: May 2002
|
Member |
|
|
Hi,
I want to know what is the better thing to do from a performance view point, when I have lots of inserts to do and one of the field values to be inserted only varies from say, only 1 to 4.
From the programming style view, I should pass this parameter to the procedure and lastly to the insert statement. I wonder whether it makes a difference if i have a dedicated procedure for each value of the parameter.
Anybody any idea? I've already made a test, but am not sure how to interpret the results, which seem not giving any clear clue.
Thankful for any helpful thought on that.
Xenofon
|
|
|
|
|
|
| Re: Pass parameter to DML or new procedure? [message #65988 is a reply to message #65986] |
Wed, 16 February 2005 11:35  |
Xenofon Grigoriadis
Messages: 33
Registered: May 2002
|
Member |
|
|
Well...ok
Here they are:
In package I have a procedure
proc_param ( param IN integer, recno IN integer );
proc1 ( recno IN integer );
proc2 ( recno IN integer );
Each of these procedures inserts (recno) rows.
Proc1 inserts the value 1. Proc2 inserts the value 2. Proc_param inserts the parameter passed in param.
So I compare the following two:
1)
proc_param(1,10000);
proc_param(2,10000);
commit;
and
2)
proc1(10000);
proc2(10000);
commit;
That should be the same isn't it?
Well it's not. But I still cannot explain the result, because it just is not clear enough.
Enough for the explanation, here are the results (each row is a repeat of the test; all results are seconds):
proc_param(1) proc_param(2) proc1 proc2
08.09 08.09 06.83 06.11
05.84 05.57 08.58 05.87
05.76 06.62 07.47 05.68
05.74 05.79 05.32 05.65
(please copy this block into a text editor in order to see it right)
???
|
|
|
|
|
|
| Re: Pass parameter to DML or new procedure? [message #65991 is a reply to message #65989] |
Thu, 17 February 2005 01:09 |
Xenofon Grigoriadis
Messages: 33
Registered: May 2002
|
Member |
|
|
Ok.
So here is the table and the package script:
"
set serveroutput on
set linesize 200
drop table ttable2;
create table ttable2
(
t1_id integer,
t1_bez varchar2(60),
t1_fac integer
);
-- Package zum Performance-Testing für konditionelle Anweisungen
create or replace package test is
procedure proc_param(param IN integer,
recno IN integer);
procedure proc1(recno IN integer);
procedure proc2(recno IN integer);
end;
/
create or replace package body test is
--
-- Prozedur proc_param
procedure proc_param(param IN integer ,
recno IN integer ) is
i integer := 0;
imax integer := 0;
t1 integer := 0;
t2 integer := 0;
begin
dbms_output.enable(1000000);
t1 := dbms_utility.get_time;
select nvl(max(t1_id),0) into imax from ttable2;
for i in imax..imax+recno
loop
insert into ttable2 values (i,to_char(i)||' row(s)', param);
end loop;
commit;
t2 := dbms_utility.get_time;
dbms_output.put_line('test_cond1.proc_param - Time elapsed: '||to_char((t2-t1)/100,'09.99'));
exception
when others then
dbms_output.put_line(sqlerrm);
rollback;
end;-- End of procedure test_c1
--
-- Prozedur proc1
procedure proc1(recno IN integer ) is
i integer := 0;
imax integer := 0;
t1 integer := 0;
t2 integer := 0;
begin
dbms_output.enable(1000000);
t1 := dbms_utility.get_time;
select nvl(max(t1_id),0) into imax from ttable2;
for i in imax..imax+recno
loop
insert into ttable2 values (i,to_char(i)||' row(s)', 1);
end loop;
commit;
t2 := dbms_utility.get_time;
dbms_output.put_line('test_cond2.proc1 - Time elapsed: '||to_char((t2-t1)/100,'09.99'));
exception
when others then
dbms_output.put_line(sqlerrm);
rollback;
end;-- End of procedure test_c1
--
-- Prozedur proc2
procedure proc2(recno IN integer ) is
i integer := 0;
imax integer := 0;
t1 integer := 0;
t2 integer := 0;
begin
dbms_output.enable(1000000);
t1 := dbms_utility.get_time;
select nvl(max(t1_id),0) into imax from ttable2;
for i in imax..imax+recno
loop
insert into ttable2 values (i,to_char(i)||' row(s)', 2);
end loop;
commit;
t2 := dbms_utility.get_time;
dbms_output.put_line('test_cond2.proc2 - Time elapsed: '||to_char((t2-t1)/100,'09.99'));
exception
when others then
dbms_output.put_line(sqlerrm);
rollback;
end;-- End of procedure test_c1
--
end;-- End of package test
/
show error
"
and here is the test-script:
"
set echo off
set feedback off
set showmode off
set termout on
set serveroutput on
set verify off
set linesize 100
define ROWS=&1
spool test_cond_&&ROWS.rows.log
truncate table ttable2 drop storage;
begin
test.proc_param(1,&&ROWS.);
test.proc_param(2,&&ROWS.);
end;
/
truncate table ttable2 drop storage;
begin
test.proc1(&&ROWS.);
test.proc2(&&ROWS.);
end;
/
spool off
show error
"
I've been using the test-script for 10.000 rows. I repeated this test 4 times.
|
|
|
|
| Re: Pass parameter to DML or new procedure? [message #109186 is a reply to message #65991] |
Tue, 22 February 2005 15:51 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
I see no statistically significant difference between the two calls to proc_param and the sequential calls to proc1/proc2.
Here are my results on two runs:
Enter value for 1: 10000
test_cond1.proc_param - Time elapsed: 02.90
test_cond1.proc_param - Time elapsed: 02.88
test_cond2.proc1 - Time elapsed: 02.83
test_cond2.proc2 - Time elapsed: 02.78
sql>@c:\test
Enter value for 1: 10000
test_cond1.proc_param - Time elapsed: 02.87
test_cond1.proc_param - Time elapsed: 02.90
test_cond2.proc1 - Time elapsed: 02.83
test_cond2.proc2 - Time elapsed: 02.84
You know the saying about concentrating on "low-hanging fruit." I would "major on the majors" - spend your time on more critical areas.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
