Home » RDBMS Server » Performance Tuning » Pass parameter to DML or new procedure?
Pass parameter to DML or new procedure? [message #65984] Tue, 15 February 2005 11:55 Go to next message
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 #65986 is a reply to message #65984] Wed, 16 February 2005 05:22 Go to previous messageGo to next message
Frank Naude
Messages: 4578
Registered: April 1998
Senior Member
Hi,

It should not make any noticeable difference. Please share your results with us so we can discuss them further.

Best regards.

Frank
Re: Pass parameter to DML or new procedure? [message #65988 is a reply to message #65986] Wed, 16 February 2005 11:35 Go to previous messageGo to next message
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 #65989 is a reply to message #65988] Wed, 16 February 2005 16:28 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Can you show us the code for these procs?
Re: Pass parameter to DML or new procedure? [message #65991 is a reply to message #65989] Thu, 17 February 2005 01:09 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Replace Outer Join with Unions
Next Topic: Diff Execution Plan for Same Query?Pl:help
Goto Forum:
  


Current Time: Fri Mar 01 03:51:27 CST 2024