How to optimize for loop

Asked by At

I need to optimize the for loop used in the PLSQL function. That function is used for making the report, but it takes too long (about 1 hour). Here is the code:

select c.id id_constr, s.id id_side, 
       coalesce(datumz, tz_date(2099, 12).datum) datum_z
  BULK COLLECT INTO arr_idConstruction, arr_idSide, arr_datumZ  
  from sp_constructions c, sp_constr_sides s
 where c.id = s.id_construction
 order by c.nummer, s.id;

for n_month in 1..12 loop

    nArbeit := 0;
    nTRS    := 0;
    nTSJ    := 0;
    nLight  := 0;
    dDate  := tz_date(pnYear, n_month).datum;

    FOR idx IN 1 .. arr_idConstruction.COUNT LOOP

      select coalesce(sum(kost_beim_bit), 0)
        into nArbeit_
        from table(dd_side_arbeit.f(arr_idSide(idx))) a
       where a.aktiv = 1
         and a.datum between tz_date(pnYear, n_month).d01 and last_day(tz_date(pnYear, n_month).d01);

      nArbeit := nArbeit + nArbeit_;

      nTRS := nTRS + dd_bewerbung.f_trs_anrechnen(
                                                 arr_idSide(idx),
                                                 dDate,
                                                 add_months(dDate, 1)-1
                                                 );

      if ( dDate <= arr_datumZ(idx) ) then

          begin

            select tsj, light
              into nTSJ_, nLight_
              from (select tsj, light
                      from t_constr_side_expenses s
                     where dDate >= s.datum
                       and s.id_side = arr_idSide(idx)
                     order by datum desc)
             where rownum = 1;

            nTSJ   := nTSJ   + nvl(nTSJ_,   0);
            nLight := nLight + nvl(nLight_, 0);

          exception
            when others then null;
          end;

      end if;

    end loop;

    select sum(b.betrag)
      into nBez
      from t_vertrag_bezahlung b
     where b.datum between dDate and
           add_months(dDate, 1) - 1;

    ttnArbeit.extend; ttnArbeit(ttnArbeit.count) := nArbeit;
    ttnTRS.extend;    ttnTRS(ttnTRS.count)       := nTRS;
    ttnTSJ.extend;    ttnTSJ(ttnTSJ.count)       := nTSJ;
    ttnLight.extend;  ttnLight(ttnLight.count)   := nLight;
    ttnENP.extend;    ttnENP(ttnENP.count)       := round(nArbeit*nENP_prz);
    ttnBez.extend;    ttnBez(ttnBez.count)       := nBez;

    tpMX.extend;
    tpMX( tpMX.count ) := tt_varchar2(  d2c( dDate ),
                                        nArbeit,
                                        nTRS,
                                        nTSJ,
                                        nLight,
                                        round(nArbeit*nENP_prz),
                                        nBez
                                      );

  end loop;

The collection arr_idConstruction has about 1000 records and there are some calculations that need to be performed for each month of the year. So, this code is executed approximately 12000 times (12 months x 1000 recods). Is there any way to optimize this code?

0 Answers