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, 2011 at 04:02 AM in Default

avatar 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, 2011 at 04:06 AM Magnus Ahlkvist

@Magnus - it's oracle - I've just retagged

May 20, 2011 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, 2011 at 04:24 AM

avatar image

WilliamD
26.2k 18 33 48

  • for sensible answer

May 20, 2011 at 04:26 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

Remove the cursor?

more ▼

answered May 20, 2011 at 04:19 AM

avatar image

Kev Riley ♦♦
63.8k 48 61 81

+1, cursor*s* - plural.

May 20, 2011 at 04:21 AM Matt Whitfield ♦♦

Nope. It's Oracle. It likes cursors.

May 20, 2011 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, 2011 at 05:16 AM Håkan Winther

Oracle likes cursors because SQL Server doesnt. It's just contrary. :)

May 20, 2011 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, 2011 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, 2011 at 05:14 AM

avatar image

Magnus Ahlkvist
20.9k 19 38 42

(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.

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:

x424
x29

asked: May 20, 2011 at 04:02 AM

Seen: 1695 times

Last Updated: May 20, 2011 at 04:06 AM

Copyright 2016 Redgate Software. Privacy Policy