x

how to reduce the execution time of the below procedure

CREATE OR REPLACE procedure AVLMFG.proc_wip_v2009_matl
( t_fromdt in varchar2,
  t_todate in varchar2 ) as
cursor c0 is
select a.*
  from sap_wip055_data a
for update
;

cursor c1 is
select
'MMMMMM' month,
get_spart(b.matnr) divn,
a.geometry,
a.draw_type,
a.spec,
a.out_dia,
a.in_dia,
a.class,
a.thickness,
count(*) nos
from sap_physstock_20100401 b
   , sapr3.ympct_tub_matl@l_mfg_sap a
where a.mandt = '600'
  and b.matnr = a.matnr
  and b.invtype = 'WIP'
  and b.werks = '055'
group by
get_spart(b.matnr),
a.geometry,
a.draw_type,
a.spec,
a.out_dia,
a.in_dia,
a.class,
a.thickness
union all
select
'MMMMMM' month,
get_spart(a.matnr) divn,
a.geometry,
a.draw_type,
a.spec,
a.out_dia,
a.in_dia,
a.class,
a.thickness,
count(*) nos
from sap_mseg_20062007 a
where a.cpudt >= '20100401'
and a.budat >= '20100401'
and werks = '055'
and lgort in ('STHI', 'STCI', 'STH3', 'STGW', 'STGL', 'STGC', 'STB1', 'STBS', 'STBC', 'STBH')
and bwart in ('101', '102')
group by
substr(a.budat, 1, 6),
get_spart(a.matnr),
a.geometry,
a.draw_type,
a.spec,
a.out_dia,
a.in_dia,
a.class,
a.thickness
;

cursor c2 is
select divn
     , geometry
     , draw_type
     , spec
     , out_dia
     , in_dia
     , class
     , thickness
from sap_wip055_data
where month = 'MMMMMM'
;

t_divn varchar2(2);
t_geometry varchar2(1);
t_draw_type varchar2(1);
t_spec varchar2(3);
t_out_dia number default 0;
t_in_dia number default 0;
t_class varchar2(1);
t_thickness number default 0;

cursor c3 is
select a.sur_finish
     , sum(b.clabs+b.cinsm) kgs
  from sap_physstock_20100401 b
     , sapr3.ympct_tub_matl@l_mfg_sap a
 where b.werks = '055'
   and b.invtype = 'WIP'
   and a.mandt = '600'
   and b.matnr = a.matnr
   and t_divn = get_spart(b.matnr)
   and t_geometry = a.geometry
   and t_draw_type = a.draw_type
   and t_spec = a.spec
   and t_out_dia = a.out_dia
   and t_in_dia = a.in_dia
   and t_class = a.class
   and t_thickness = a.thickness
group by a.sur_finish
;

cursor c4 is
select a.lgort
     , 'MMMMMM' month
     , a.sur_finish
     , sum(decode(a.shkzg, 'S', a.menge, -1*a.menge)) kgs
  from sap_mseg_20062007 a
 where a.werks = '055'
   and cpudt >= t_fromdt
   and cpudt <= t_todate
   and bwart in ('101', '102')
   and t_divn = get_spart(a.matnr)
   and t_geometry = a.geometry
   and t_draw_type = a.draw_type
   and t_spec = a.spec
   and t_out_dia = a.out_dia
   and t_in_dia = a.in_dia
   and t_class = a.class
   and t_thickness = a.thickness
group by a.lgort
       , substr(a.budat, 1, 6)
       , a.sur_finish
;

begin

for c0rec in c0 loop
exit when c0%notfound;

begin

delete sap_wip055_data
where current of c0;

end;

end loop;

commit;

for c1rec in c1 loop
exit when c1%notfound;

begin

insert into sap_wip055_data (
 DIVN,
 GEOMETRY,
 DRAW_TYPE,
 SPEC,
 OUT_DIA,
 IN_DIA,
 CLASS,
 THICKNESS,
 MONTH,
 BLACK_OPNG,
 BLACK_PRDN,
 BLACK_TRFR_GALV,
 BLACK_TRFR_PACK,
 GALV_SCRAP_GALV,
 BLACK_CLSG,
 BLACK_SCRAP_FIN,
 GALV_OPNG,
 GALV_PRDN,
 GALV_TRFR,
 GALV_SCRAP_FIN,
 GALV_CLSG,
 CPUDT,
 CPUTM ) values (
c1rec.divn,
c1rec.geometry,
c1rec.draw_type,
c1rec.spec,
c1rec.out_dia,
c1rec.in_dia,
c1rec.class,
c1rec.thickness,
c1rec.month,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
to_char(sysdate, 'yyyymmdd'),
to_char(sysdate, 'hh24miss') );

exception when dup_val_on_index then
null;

end;

end loop;

commit;

for c2rec in c2 loop
exit when c2%notfound;

t_divn := c2rec.divn;
t_geometry := c2rec.geometry;
t_draw_type := c2rec.draw_type;
t_spec := c2rec.spec;
t_out_dia := c2rec.out_dia;
t_in_dia := c2rec.in_dia;
t_class := c2rec.class;
t_thickness := c2rec.thickness;

for c3rec in c3 loop
exit when c3%notfound;

if c3rec.sur_finish = 'GL' then

   begin

   update sap_wip055_data
   set galv_opng = galv_opng + c3rec.kgs
   where divn = t_divn
     and geometry = t_geometry
     and draw_type = t_draw_type
     and spec = t_spec
     and out_dia = t_out_dia
     and in_dia = t_in_dia
     and class = t_class
     and thickness = t_thickness
     and month = 'MMMMMM'
   ;

   end;

else

   begin

   update sap_wip055_data
   set black_opng = black_opng + c3rec.kgs
   where divn = t_divn
     and geometry = t_geometry
     and draw_type = t_draw_type
     and spec = t_spec
     and out_dia = t_out_dia
     and in_dia = t_in_dia
     and class = t_class
     and thickness = t_thickness
     and month = 'MMMMMM'
   ;

   end;

end if;

end loop;

commit;

for c4rec in c4 loop
exit when c4%notfound;

if c4rec.sur_finish = 'GL' then

if c4rec.lgort in ('STGL', 'STGC') then

   begin

   update sap_wip055_data
   set black_trfr_galv = black_trfr_galv + c4rec.kgs
     , galv_prdn = galv_prdn + c4rec.kgs
   where divn = t_divn
     and geometry = t_geometry
     and draw_type = t_draw_type
     and spec = t_spec
     and out_dia = t_out_dia
     and in_dia= t_in_dia
     and class = t_class
     and thickness = t_thickness
     and month = c4rec.month
   ;

   end;

elsif c4rec.lgort in ('STB1', 'STBH', 'STBS', 'STBC') then

   begin

   update sap_wip055_data
   set galv_trfr = galv_trfr + c4rec.kgs
   where divn = t_divn
     and geometry = t_geometry
     and draw_type = t_draw_type
     and spec = t_spec
     and out_dia = t_out_dia
     and in_dia= t_in_dia
     and class = t_class
     and thickness = t_thickness
     and month = c4rec.month
   ;

   end;

else

   null;

end if;

else -- black

if c4rec.lgort in ('STHI', 'STCI', 'STH3', 'STGW') then

   begin

   update sap_wip055_data
   set black_prdn = black_prdn + c4rec.kgs
   where divn = t_divn
     and geometry = t_geometry
     and draw_type = t_draw_type
     and spec = t_spec
     and out_dia = t_out_dia
     and in_dia= t_in_dia
     and class = t_class
     and thickness = t_thickness
     and month = c4rec.month
   ;

   end;

elsif c4rec.lgort in ('STB1', 'STBH', 'STBC', 'STBS;') then

   begin

   update sap_wip055_data
   set black_trfr_pack = black_trfr_pack + c4rec.kgs
   where divn = t_divn
     and geometry = t_geometry
     and draw_type = t_draw_type
     and spec = t_spec
     and out_dia = t_out_dia
     and in_dia= t_in_dia
     and class = t_class
     and thickness = t_thickness
     and month = c4rec.month
   ;

   end;

else

   null;

end if;

end if;

end loop;

commit;

end loop;

end;
/
more ▼

asked May 20 '11 at 04:02 AM in Default

scr1 gravatar image

scr1
11 1 1 1

What SQL dialect is this? What DBMS is it? It doesn't look very T-SQL/SQL Server to me.
May 20 '11 at 04:06 AM Magnus Ahlkvist
@Magnus - it's oracle - I've just retagged
May 20 '11 at 04:07 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

With this being Oracle, I can only offer the most general of advice.

Divide and conquer! Split the code into smaller modules if possible, calling these separately.

Try and keep your transactions short. I don't know if this is the same in Oracle as MS SQL Server, but it seems that your entire work is inside a transaction. If it were SQL Server, this could cause blocking until that entire thing is done. If Oracle works the same, that may be a point to consider.

Does this all have to be done in loops? It doesn't seem to be a set-based approach. Again, this may be an Oracle/SQL Server thing, but loops and cursors are a performance killer in SQL Server.
more ▼

answered May 20 '11 at 04:24 AM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

+1 for sensible answer
May 20 '11 at 04:26 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Remove the cursor?
more ▼

answered May 20 '11 at 04:19 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

+1, cursor**s** - plural.
May 20 '11 at 04:21 AM Matt Whitfield ♦♦
Nope. It's Oracle. It likes cursors.
May 20 '11 at 05:02 AM Grant Fritchey ♦♦
Yes, that's the most common mistake of oracle developers trying to convert to SQL service. Sorry I mean SQL server, my company, SQL service will never try to convert an oracle developer.
May 20 '11 at 05:16 AM Håkan Winther
Oracle likes cursors because SQL Server doesnt. It's just contrary. :)
May 20 '11 at 05:32 AM Fatherjack ♦♦
Oracle works far better with cursors than SQL Server does, in Oracle I would probably keep the cursor.
May 20 '11 at 10:13 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

There's not much I know about Oracle, so my advice is somewhat general too:

  • My impression is that Oracle = cursors.
  • I also remember from uni transactions running for whole work sessions, so locking and transactions seems to be handled very differently in Oracle and SQL Server.
On top of the advices you already received, I'd recommend you to have a look at your indexing. You have update-conditions in a loop, and if the WHERE clauses for these updates are not optimized, you'll ask Oracle to scan tables over and over again. So look at your WHERE-clauses and see if you have proper indexes on the columns involved.
more ▼

answered May 20 '11 at 05:14 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x375
x21

asked: May 20 '11 at 04:02 AM

Seen: 1221 times

Last Updated: May 20 '11 at 04:06 AM