Function Module time out

[dti_gpe]

Hello expert

I have faced a problem with my function module that prints an account report for a web service.

When running my function module, it gives me the error Dump timeout, through analysis I realized that it is taking a long time to read a table. however I didn't have this problem before with the same function module it took around 1 to 3 minutes to load the data, but now it does around 10 to 15 minutes.

[mateuszadamus]

Hi

You need to give more information - about the table's structure, about the function's logic.

Have you done the performance trace of the function call? You could post that, too.

Kind regards,
Mateusz

[raymond_giuseppi]

You already called SE30 (you could call SAT as SE30 is nearly obsolete)

Could you also execute a SQL trace with ST05 and analyze the SQL statements that hinder your program. (look for bad performance access or even full table scan)

I also suspect you have some SELECT embeded in some LOOP, could you try to remove those, either changing the previous access to database, or using some FOR ALL ENTRIES as a lowe problem.

[mateuszadamus]

Hello jose.lopes2

Please use "Comment" when adding additional information to question. "Answer" is for solution proposal only.

You're probably reading the BSAK without using the primary key or any index, that's why it takes so long. But that's just a guess. You need to show your logic.

Kind regards,
Mateusz

[dti_gpe]

Hello mateuszadamus

Do you want me to show the Program or the function ?.

What would be the way to be able to use index.

[mateuszadamus]

Need to see the function's code responsible for reading data from BSAK table to be able to see what might be the issue with it.

The index will be used if you use field from index in your WHERE condition of SELECT statement.

Kind regards,
Mateusz

[dti_gpe]

The code is quite large. so exclude some parts...

IF bsik-budat IN so_budat.
t_bsik-bukrs = bsik-bukrs.
t_bsik-lifnr = bsik-lifnr.
t_bsik-fwaer = bsik-waers.
t_bsik-budat = bsik-budat.
t_bsik-blart = bsik-blart.
t_bsik-belnr = bsik-belnr.
t_bsik-xblnr = bsik-xblnr.
t_bsik-shkzg = bsik-shkzg.
t_bsik-hkont = bsik-hkont.
t_bsik-umskz = bsik-umskz.
t_bsik-wrbtr = bsik-wrbtr.
t_bsik-wrshb = bsega-wrshb.
t_bsik-dmbtr = bsik-dmbtr.
t_bsik-dmshb = bsega-dmshb.
t_bsik-gjahr = bsik-gjahr.

SELECT SINGLE augbl FROM bsakINTO t_bsik-augblWHERE bukrs EQ bsik-bukrsAND lifnr EQ bsik-lifnrAND gjahr EQ bsik-gjahrAND belnr EQ bsik-belnrand SHKZG EQ bsik-SHKZGAND WAERS EQ bsik-WAERS.

APPEND t_bsik.ENDIF..

.

-----

* Tratar primeiro os ABLOOP AT lt_bsik INTO ls_bsik WHERE blart EQ 'AB'.
SELECT *FROM bsak INTO TABLE lt_bsakWHERE augbl EQ ls_bsik-belnr " and ( blart eq 'ES' or blart eq 'RE').AND budat IN so_budat.
IF sy-subrc EQ 0.
CLEAR: lv_re, lv_es, total_re, total_es, lv_total, total_ab_deb, total_ab_cre, ls_bsak_tmp.
* Calcular os totais de todos os items para doc RE e doc ES

LOOP AT lt_bsak INTO ls_bsak_tmp.
IF ls_bsak_tmp-blart EQ 'RE' AND ls_bsak_tmp-shkzg EQ 'S'.
lv_re = ls_bsak_tmp-belnr.
total_re = total_re + ls_bsak_tmp-wrbtr.ELSEIF ls_bsak_tmp-blart EQ 'RE' AND ls_bsak_tmp-shkzg EQ 'H'.
lv_re = ls_bsak_tmp-belnr.
total_re = total_re + ls_bsak_tmp-wrbtr.
total_re = total_re * -1.ELSEIF ls_bsak_tmp-blart EQ 'ES' AND ls_bsak_tmp-shkzg EQ 'H'.
lv_es = ls_bsak_tmp-belnr.
total_es = total_es + ls_bsak_tmp-wrbtr.
total_es = total_es * -1.ELSEIF ls_bsak_tmp-blart EQ 'ES' AND ls_bsak_tmp-shkzg EQ 'S'.
lv_es = ls_bsak_tmp-belnr.
total_es = total_es + ls_bsak_tmp-wrbtr.ELSEIF ls_bsak_tmp-blart EQ 'AB' AND ls_bsak_tmp-shkzg EQ 'S'.
total_ab_deb = total_ab_deb + ls_bsak_tmp-wrbtr.ELSEIF ls_bsak_tmp-blart EQ 'AB' AND ls_bsak_tmp-shkzg EQ 'H'.
total_ab_cre = total_ab_cre + ls_bsak_tmp-wrbtr.
total_ab_cre = total_ab_cre * -1.ENDIF.ENDLOOP.
IF lv_re IS NOT INITIAL AND lv_es IS NOT INITIAL.
lv_total = total_es + total_re + total_ab_cre + total_ab_deb.
IF lv_total EQ 0.CLEAR ls_excluir.
ls_excluir-num_excluir = ls_bsik-belnr.APPEND ls_excluir TO lt_excluir.
CLEAR ls_excluir.
ls_excluir-num_excluir = lv_re.APPEND ls_excluir TO lt_excluir.
CLEAR ls_excluir.
ls_excluir-num_excluir = lv_es.APPEND ls_excluir TO lt_excluir.ENDIF.ENDIF.
ELSE.
CLEAR: lt_bsak[], ls_bsak_tmp, total_blart, total_ab_deb, total_ab_cre.

SELECT * FROM bsak INTO TABLE lt_bsakWHERE augbl EQ ls_bsik-augblAND bukrs EQ ls_bsik-bukrsAND lifnr EQ ls_bsik-lifnrAND gjahr EQ ls_bsik-gjahr.
IF sy-subrc EQ 0.LOOP AT lt_bsak INTO ls_bsak_tmp.IF ls_bsak_tmp-blart NE 'AB' AND ls_bsak_tmp-shkzg EQ 'H'.
lv_blart = ls_bsak_tmp-belnr.
total_blart = total_blart + ls_bsak_tmp-wrbtr.
total_blart = total_blart * -1.ELSEIF ls_bsak_tmp-blart NE 'AB' AND ls_bsak_tmp-shkzg EQ 'S'.
lv_blart = ls_bsak_tmp-belnr.
total_blart = total_blart + ls_bsak_tmp-wrbtr.ELSEIF ls_bsak_tmp-blart EQ 'AB' AND ls_bsak_tmp-shkzg EQ 'S'.
total_ab_deb = total_ab_deb + ls_bsak_tmp-wrbtr.ELSEIF ls_bsak_tmp-blart EQ 'AB' AND ls_bsak_tmp-shkzg EQ 'H'.
total_ab_cre = total_ab_cre + ls_bsak_tmp-wrbtr.
total_ab_cre = total_ab_cre * -1.ENDIF.ENDLOOP.IN so_budat.
IF sy-subrc EQ 0.
CLEAR: lv_re, lv_es, total_re, total_es, lv_total, total_ab_deb, total_ab_cre, ls_bsak_tmp.

[mateuszadamus]

I see you're reading from BSAK based on AUGBL field only.

SELECT * FROM bsak INTO TABLE lt_bsak WHERE augbl EQ ls_bsik-belnr " and ( blart eq 'ES' or blart eq 'RE').AND budat IN so_budat.

You should check in SE11 transaction in BSAK definition what is the primary key and what indices are available and try to modify you SELECT's WHERE clause to meet one of these indices. If that's not possible, your next best option is to create a new index which with the fields you're using in your SELECT, this should speed-up the DB read.

Also, please format your code with the "Code" button of the comment editor.

Kind regards,

Mateusz

[dti_gpe]

mateuszadamus

I'm already using the primary keys, so it may be necessary to create a new index, so I'll proceed with this option. then I say if the problem is solved. Thank you very much

[dti_gpe]

se30 trace

[Sandra_Rossi]

OP image :

[Sandra_Rossi]

Almost 76.000 SELECT on BSAK, 36.000 on BKPF. It's normal that it's very long with so many SELECT! (even if you use the primary key).

Why don't you don't you just do 1 SELECT on BSAK and 1 SELECT on BKPF, outside the loop?

[dti_gpe]

Hi sandra.rossi

It is a little difficult to change the code because it is a copy of a report of national and foreign currencies making calculations of credit and debit and the total spent. I am reusing the code and changed only the necessary one, transforming it into a Web service. Here is the part of the referred selects.

Thanks.

[dti_gpe]

  IF p_est EQ 'X'.

    LOOP AT  t_bsik.
      SELECT SINGLE * FROM bkpf
            WHERE bukrs EQ t_bsik-bukrs
              AND belnr EQ t_bsik-belnr
              AND gjahr EQ t_bsik-gjahr
              AND stblg NE space.


      IF sy-subrc NE 0.
        APPEND  t_bsik TO lt_bsik.
      ENDIF.
    ENDLOOP.

    SORT lt_bsik BY blart.

*   Tratar primeiro os AB
    LOOP AT lt_bsik INTO ls_bsik WHERE blart EQ 'AB'.

      SELECT *
        FROM bsak INTO TABLE lt_bsak
        WHERE augbl EQ ls_bsik-belnr " and ( blart eq 'ES' or blart eq 'RE').
        AND   budat IN so_budat.

      IF sy-subrc EQ 0.

        CLEAR: lv_re, lv_es, total_re, total_es, lv_total, total_ab_deb, total_ab_cre, ls_bsak_tmp.

*  Calcular os totais de todos os items para doc RE e doc ES

        LOOP AT lt_bsak INTO ls_bsak_tmp.

          IF ls_bsak_tmp-blart EQ 'RE' AND ls_bsak_tmp-shkzg EQ 'S'.
            lv_re = ls_bsak_tmp-belnr.
            total_re = total_re + ls_bsak_tmp-wrbtr.
          ELSEIF ls_bsak_tmp-blart EQ 'RE' AND ls_bsak_tmp-shkzg EQ 'H'.
            lv_re = ls_bsak_tmp-belnr.
            total_re = total_re + ls_bsak_tmp-wrbtr.
            total_re = total_re * -1.
          ELSEIF ls_bsak_tmp-blart EQ 'ES' AND ls_bsak_tmp-shkzg EQ 'H'.
            lv_es = ls_bsak_tmp-belnr.
            total_es = total_es + ls_bsak_tmp-wrbtr.
            total_es = total_es * -1.
          ELSEIF ls_bsak_tmp-blart EQ 'ES' AND ls_bsak_tmp-shkzg EQ 'S'.
            lv_es = ls_bsak_tmp-belnr.
            total_es = total_es + ls_bsak_tmp-wrbtr.
          ELSEIF ls_bsak_tmp-blart EQ 'AB' AND ls_bsak_tmp-shkzg EQ 'S'.
            total_ab_deb = total_ab_deb + ls_bsak_tmp-wrbtr.
          ELSEIF ls_bsak_tmp-blart EQ 'AB' AND ls_bsak_tmp-shkzg EQ 'H'.
            total_ab_cre = total_ab_cre + ls_bsak_tmp-wrbtr.
            total_ab_cre = total_ab_cre * -1.
          ENDIF.
        ENDLOOP.



        IF lv_re IS NOT INITIAL AND lv_es IS NOT INITIAL.
          lv_total = total_es + total_re + total_ab_cre + total_ab_deb.

          IF lv_total EQ 0.
            CLEAR ls_excluir.
            ls_excluir-num_excluir = ls_bsik-belnr.
            APPEND ls_excluir TO lt_excluir.

            CLEAR ls_excluir.
            ls_excluir-num_excluir = lv_re.
            APPEND ls_excluir TO lt_excluir.

            CLEAR ls_excluir.
            ls_excluir-num_excluir = lv_es.
            APPEND ls_excluir TO lt_excluir.
          ENDIF.
        ENDIF.

      ELSE.

        CLEAR: lt_bsak[], ls_bsak_tmp, total_blart, total_ab_deb, total_ab_cre.

        SELECT * FROM bsak INTO TABLE lt_bsak
        WHERE augbl EQ ls_bsik-augbl
          AND bukrs EQ ls_bsik-bukrs
          AND lifnr EQ ls_bsik-lifnr
          AND gjahr EQ ls_bsik-gjahr.


        IF sy-subrc EQ 0.
          LOOP AT lt_bsak INTO ls_bsak_tmp.
            IF ls_bsak_tmp-blart NE 'AB' AND ls_bsak_tmp-shkzg EQ 'H'.
              lv_blart = ls_bsak_tmp-belnr.
              total_blart = total_blart + ls_bsak_tmp-wrbtr.
              total_blart = total_blart * -1.
            ELSEIF ls_bsak_tmp-blart NE 'AB' AND ls_bsak_tmp-shkzg EQ 'S'.
              lv_blart = ls_bsak_tmp-belnr.
              total_blart = total_blart + ls_bsak_tmp-wrbtr.
            ELSEIF ls_bsak_tmp-blart EQ 'AB' AND ls_bsak_tmp-shkzg EQ 'S'.
              total_ab_deb = total_ab_deb + ls_bsak_tmp-wrbtr.
            ELSEIF ls_bsak_tmp-blart EQ 'AB' AND ls_bsak_tmp-shkzg EQ 'H'.
              total_ab_cre = total_ab_cre + ls_bsak_tmp-wrbtr.
              total_ab_cre = total_ab_cre * -1.
            ENDIF.
          ENDLOOP.

          IF lv_blart IS NOT INITIAL.
            lv_total = total_ab_cre + total_ab_deb + total_blart.

            IF lv_total EQ 0.
              CLEAR ls_excluir.
              ls_excluir-num_excluir = ls_bsik-belnr.
              APPEND ls_excluir TO lt_excluir.

              CLEAR ls_excluir.
              ls_excluir-num_excluir = lv_blart.
              APPEND ls_excluir TO lt_excluir.
            ENDIF.
          ENDIF.

        ENDIF.

      ENDIF.
    ENDLOOP.

    CLEAR: ls_bsik.



    loop at lt_bsik into ls_bsik where blart ne 'AB'.
*      at new belnr.

      clear: ls_bsak_tmp2, lt_bsak_tmp[], lv_total, total_es, total_re, lv_negative, lv_kz.

* INIT Delete JArgencio 29-03-2012
*   apanhar todos os documentos que são possiveis estornos directos (KZ e ES respectivo)
*      select * from bsak into table lt_bsak_tmp where augbl eq ls_bsik-augbl
*                                                and   augdt in so_budat2
*                                                and   blart ne 'AB'
*                                                and   blart ne 'RE'.
* END Delete JArgencio 29-03-2012

* INIT Insert JArgencio 29-03-2012
*   apanhar todos os documentos que são possiveis estornos directos (KZ e ES respectivo)
* END Insert JArgencio 29-03-2012



      select * from bsak into table lt_bsak_tmp where augbl eq ls_bsik-augbl
                                                      and   augdt in so_budat
                                                      and   blart ne 'AB'
                                                      and   blart ne 'RE'
                                                      and   gjahr eq ls_bsik-gjahr.

      if sy-subrc eq 0.

        loop at lt_bsak_tmp into ls_bsak_tmp2.

          if ls_bsak_tmp2-shkzg eq 'S' and ls_bsak_tmp2-belnr eq ls_bsik-belnr.
            total_es = total_es + ls_bsak_tmp2-wrbtr.

          elseif ls_bsak_tmp2-shkzg eq 'H' and ls_bsak_tmp2-belnr eq ls_bsik-belnr.
            if total_es eq 0.
              total_es = total_es + ls_bsak_tmp2-wrbtr.
              total_es = total_es * -1.
            else.
              clear lv_negative.
              lv_negative = ls_bsak_tmp2-wrbtr * -1.
              total_es = total_es + lv_negative.
            endif.

          elseif ls_bsak_tmp2-shkzg eq 'S' and ls_bsak_tmp2-belnr ne ls_bsik-belnr.
            lv_kz = ls_bsak_tmp2-belnr.
            total_re = total_re + ls_bsak_tmp2-wrbtr.

          elseif ls_bsak_tmp2-shkzg eq 'H' and ls_bsak_tmp2-belnr ne ls_bsik-belnr.
            lv_kz = ls_bsak_tmp2-belnr.
            if total_re eq 0.
              total_re = total_re + ls_bsak_tmp2-wrbtr.
              total_re = total_re * -1.
            else.
              clear lv_negative.
              lv_negative = ls_bsak_tmp2-wrbtr * -1.
              total_re = total_re + lv_negative.
            endif.
          endif.
        endloop.

[Sandra_Rossi]

It doesn't look complex to adapt. But of course you'll need to re-test the selection logic.

The first thing I see is that one of the first rules in ABAP is to not use SELECT * (unless there are few columns or you use more than given % of columns).

I don't see the SELECT ... FROM bsik.

You can do things like that, either using join via EXISTS, or via GTT, or pseudo joins (less efficient than previous solutions) via FOR ALL ENTRIES, or AMDP:

SELECT columns FROM bkpf
    WHERE EXISTS ( SELECT * FROM bsik " or NOT EXISTS, depends what theoretically returns less records
        WHERE " here the same selections as your original SELECT ... FROM BSIK
          AND ...
          AND bkpf~bukrs = bsik-bukrs
          AND bkpf~belnr = bsik-belnr
          AND bkpf~gjahr = bsik-gjahr )
      AND stblg NE space
    INTO TABLE @DATA(bkpf_lines).

[dti_gpe]

yes i did the trace in se30