Huge volume of records results in time limit error

[aris_hidalgo]

Hello Experts,

I am currently modifying a report where it processes more than 500K of records. Our users say that

when they run it in PROD server it always results in time limit exceeded error. Now, I can;t think of

a solution to address this problem . I need help guys. Thank you and take care!

[former_member226234]

Hi,

Instead of processing all the 500K records, you can process the data in chunks.

In this case you will need to read chunks of data (say 1K records) from the

internal table, process it, then the next 1K and so on...

Regards,

Sandeep

[aris_hidalgo]

Hi Sandeep,

How do I process the records by chunks? Examples will be hgihly appreciated. Thanks!

[Former Member]

Hi,

Check this code for how you read cetail amount of data.

*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
TABLES: vbak.

SELECT-OPTIONS: s_vbeln FOR vbak-vbeln.

DATA: lit_vbak LIKE vbak OCCURS 0 WITH HEADER LINE.

SELECT
      * FROM vbak
        INTO TABLE lit_vbak
        UP TO 1000 ROWS      " See this line
        WHERE vbeln IN s_vbeln.
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*

Let me know if you have any question.

Regards,

RS

[ferry_lianto]

Hi,

Please try to run in the background (if online report) and perhaps also you may want to check cursor concept. Check this link for more detail and sample codes.

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3b23358411d1829f0000e829fbfe/content.htm

Regards,

Ferry Lianto

[Former Member]

Hi,

Try this..

After each select..give COMMIT WORK. statement. It will reset time slice counter.

OR

You can run the program in background..

Thanks,

Naren

[aris_hidalgo]

Hello Guys,

Thank you for your replies. Can you give an example on how COMMIT WORK statement works? also, I will paste the code below where the time out occurs. Again, thank you guys and I appreciate your help!

• Get sales document and corresponding line item

SELECT a_{vbeln b}posnr a~auart

a_{vkorg a}vtweg a~kunnr

b_{matnr b}pstyv b~spart

FROM vbak AS a

INNER JOIN vbap AS b

ON a_{vbeln = b}vbeln

APPENDING TABLE lt_vbap

WHERE a~auart IN lr_auart

AND a~vbeln IN s_vbeln

AND a~vbeln IN s_vbill

AND a~vbtyp EQ p_vbtyp.

• the loop...endloop statement below is the one causing the time out. Any inputs will be highly appreciated.

LOOP AT lt_vbap ASSIGNING <wa_vbap>.

IF <wa_vbap>-auart EQ 'ZUCI' OR

<wa_vbap>-auart EQ 'ZURV' OR

<wa_vbap>-auart EQ 'ZUPR'.

• Check ifa delivery document has been created

CLEAR wa_vbill.

SELECT SINGLE vbeln

FROM vbfa

INTO wa_vbill

WHERE vbelv = <wa_vbap>-vbeln

AND ( ( vbtyp_n EQ 'J' AND vbtyp_v = 'C' )

OR ( vbtyp_n EQ 'T' AND vbtyp_v = 'H' ) ).

IF sy-subrc <> 0.

<wa_vbap>-del_ind = 'X'.

CONTINUE.

ELSE.

• Check if within selection parameters

CLEAR wa_likp.

READ TABLE lt_likp INTO wa_likp WITH KEY vbeln = wa_vbill.

IF sy-subrc <> 0.

SELECT SINGLE *

FROM likp

INTO wa_likp

WHERE vbeln = wa_vbill.

APPEND wa_likp TO lt_likp.

ENDIF.

IF NOT wa_likp-wadat_ist IN s_erdat.

<wa_vbap>-del_ind = 'X'.

CONTINUE.

ENDIF.

ENDIF.

ELSE.

• Check if this has already been billed

CLEAR wa_vbill.

SELECT SINGLE vbeln

FROM vbfa

INTO wa_vbill

WHERE vbelv = <wa_vbap>-vbeln

AND ( ( vbtyp_n EQ 'M' AND vbtyp_v = 'C' )

OR ( vbtyp_n EQ 'O' AND vbtyp_v = 'H' ) ).

IF sy-subrc <> 0.

<wa_vbap>-del_ind = 'X'.

CONTINUE.

ELSE.

• Check if within selection parameters

CLEAR wa_vbrk.

READ TABLE lt_vbrk INTO wa_vbrk WITH KEY vbeln = wa_vbill.

IF sy-subrc <> 0.

SELECT SINGLE vbeln fkdat

FROM vbrk

INTO wa_vbrk

WHERE vbeln = wa_vbill.

APPEND wa_vbrk TO lt_vbrk.

ENDIF.

IF NOT wa_vbrk-fkdat IN s_erdat.

<wa_vbap>-del_ind = 'X'.

CONTINUE.

ENDIF.

ENDIF.

ENDIF.

• Get Material Type and Division

IF <wa_vbap>-spart IS NOT INITIAL.

SELECT SINGLE mtart

FROM mara

INTO lv_mtart

WHERE matnr = <wa_vbap>-matnr.

ELSE.

SELECT SINGLE mtart spart

FROM mara

INTO (lv_mtart,<wa_vbap>-spart)

WHERE matnr = <wa_vbap>-matnr.

ENDIF.

• If material division is same as parameter division

IF <wa_vbap>-spart = p_spart.

• If material is subsidiary-owned

• IF <wa_vbap>-spart in lr_spart.

• If customer is the same as that of parameter division

IF <wa_vbap>-kunnr NE lr_kunnr OR <wa_vbap>-auart EQ 'ZUDO' OR <wa_vbap>-auart EQ 'ZUS3'.

• If item is a free good, tag order type as 'ZUPR' to treat

• the item the same way as 'ZUPR'

IF <wa_vbap>-pstyv = 'TANN' OR

<wa_vbap>-pstyv = 'ZKNF' OR

<wa_vbap>-pstyv = 'ZKNN' OR

<wa_vbap>-pstyv = 'ZFLO' OR

<wa_vbap>-pstyv = 'ZKBF' OR

<wa_vbap>-pstyv = 'ZKLN' OR

<wa_vbap>-pstyv = 'ZREN'.

lv_auart = <wa_vbap>-auart.

IF lv_auart EQ 'ZUPR'.

CLEAR <wa_vbap>-pstyv.

ENDIF.

<wa_vbap>-auart = 'ZUPR'.

CLEAR: lv_mtart.

ENDIF.

• Set Cost of Goods Sold Account

<wa_vbap>-cogshkont = '0050000010'.

• CASE <wa_vbap>-auart.

• WHEN 'ZUDO' OR 'ZUPR' OR 'ZUCI'.

• IF p_rtrns EQ 'X'.

• <wa_vbap>-del_ind = 'X'.

• CONTINUE.

• ENDIF.

*

• WHEN 'ZURD'.

• IF p_issue EQ 'X'.

• <wa_vbap>-del_ind = 'X'.

• CONTINUE.

• ENDIF.

*

• WHEN OTHERS.

• <wa_vbap>-del_ind = 'X'.

• CONTINUE.

*

• ENDCASE.

CASE <wa_vbap>-auart.

WHEN 'ZUPR'.

CASE lv_mtart.

WHEN 'ZUL4'.

<wa_vbap>-del_ind = 'X'.

CONTINUE.

WHEN OTHERS.

• Check if division is defined in mapping table

READ TABLE lt_ulsub INTO wa_ulsub

WITH KEY spart = <wa_vbap>-spart.

IF sy-subrc = 0.

<wa_vbap>-kostl = wa_ulsub-kostlp.

<wa_vbap>-bukrs = wa_ulsub-bukrsp.

<wa_vbap>-type = 'TP'.

• Set Cost of Goods Sold Account

IF lv_auart = 'ZUPR'.

<wa_vbap>-cogshkont = '0050000006'.

ENDIF.

ELSE.

<wa_vbap>-del_ind = 'X'.

CONTINUE.

ENDIF.

ENDCASE.

WHEN 'ZUCI' OR 'ZUDO' OR 'ZURD' OR 'ZUS3'.

• Get cost center from header text

PERFORM get_cost_center USING <wa_vbap>-vbeln

CHANGING <wa_vbap>-kostl.

• * Change by LGTE 11-02-2006

• Check if Medvale sales

IF <wa_vbap>-auart EQ 'ZUCI' AND <wa_vbap>-kostl IS INITIAL.

<wa_vbap>-del_ind = 'X'.

CONTINUE.

• Check if UL Cost Center

ELSEIF <wa_vbap>-kostl NE gv_ul_kostl.

• IF <wa_vbap>-kostl NE gv_ul_kostl.

• * End change.

• Get material division and check if subsidiary product

• SELECT SINGLE spart FROM mara INTO <wa_vbap>-spart

• WHERE matnr = <wa_vbap>-matnr.

READ TABLE lt_ulsub INTO wa_ulsub

WITH KEY spart = <wa_vbap>-spart.

IF sy-subrc = 0.

• Get requestor's company code from cost center data

SELECT SINGLE bukrs

FROM csks

INTO <wa_vbap>-bukrs

WHERE kokrs = lc_kokrs

AND kostl = <wa_vbap>-kostl

AND datbi GE sy-datum

AND datab LE sy-datum.

IF sy-subrc = 0.

• For donations, only include if a subsidiary company is donating

IF <wa_vbap>-auart EQ 'ZUDO' OR <wa_vbap>-auart EQ 'ZUS3'.

IF NOT <wa_vbap>-kunnr IN lr_kunnrnt.

<wa_vbap>-del_ind = 'X'.

CONTINUE.

ENDIF.

• CHECK <wa_vbap>-kunnr IN lr_kunnrnt.

ENDIF.

• For ZUCI, if the company code of the costcenter is UL, do not include

IF <wa_vbap>-bukrs EQ gc_ul_bukrs.

<wa_vbap>-del_ind = 'X'.

CONTINUE.

ENDIF.

IF <wa_vbap>-bukrs = wa_ulsub-bukrsp.

<wa_vbap>-type = 'RV'.

ELSE.

<wa_vbap>-type = 'RV'.

ENDIF.

ELSE.

• Check if customer defined in table ZFI_DONATIONS

SELECT SINGLE kunnr FROM zfi_donations INTO <wa_vbap>-kostl

WHERE kunnr = <wa_vbap>-kostl.

IF sy-subrc <> 0.

lv_error = 'X'.

CONCATENATE 'Cost center/Customer' <wa_vbap>-kostl

'does not exist. Please check Sales Order'

<wa_vbap>-vbeln

INTO lt_disp-message SEPARATED BY space.

lt_disp-type = 'E'.

<wa_vbap>-del_ind = 'X'.

APPEND lt_disp.

CONTINUE.

ELSE.

<wa_vbap>-type = 'RV'.

ENDIF.

ENDIF.

ELSE.

<wa_vbap>-del_ind = 'X'.

CONTINUE.

ENDIF.

ELSE.

<wa_vbap>-del_ind = 'X'.

CONTINUE.

ENDIF.

WHEN OTHERS.

<wa_vbap>-del_ind = 'X'.

CONTINUE.

ENDCASE.

ELSE.

<wa_vbap>-del_ind = 'X'.

CONTINUE.

ENDIF.

ELSE.

<wa_vbap>-del_ind = 'X'.

CONTINUE.

ENDIF.

• Get company code description

SELECT SINGLE butxt FROM t001 INTO <wa_vbap>-butxt

WHERE bukrs = <wa_vbap>-bukrs.

IF sy-subrc <> 0.

SELECT SINGLE name1 FROM kna1 INTO <wa_vbap>-butxt

WHERE kunnr = <wa_vbap>-kostl.

ENDIF.

• Change by LGTE on 11-02-2006

• Get Transaction Type

CASE <wa_vbap>-auart.

WHEN 'ZUPR'. "UL Promo/Samples

IF <wa_vbap>-pstyv = 'TANN' OR

<wa_vbap>-pstyv = 'ZKNF' OR

<wa_vbap>-pstyv = 'ZKNN' OR

<wa_vbap>-pstyv = 'ZFLO' OR

<wa_vbap>-pstyv = 'ZKBF' OR

<wa_vbap>-pstyv = 'ZKLN' OR

<wa_vbap>-pstyv = 'ZREN'.

<wa_vbap>-ltext = 'Free Goods'.

ELSE.

<wa_vbap>-ltext = 'Product Samples'.

ENDIF.

WHEN 'ZUDO'. "Donations - R/3

<wa_vbap>-ltext = 'Donations - R/3'.

WHEN 'ZUCI'. "Donations - ZUCI

<wa_vbap>-ltext = 'Donations - ZUCI'.

WHEN 'ZURD' OR 'ZUS3'. "UL Returns - Donation

<wa_vbap>-ltext = 'UL Returns - Donation'.

ENDCASE.

• Get cost center description

• SELECT SINGLE ltext FROM cskt INTO <wa_vbap>-ltext

• WHERE spras = sy-langu

• AND kokrs = lc_kokrs

• AND kostl = <wa_vbap>-kostl

• AND datbi GE sy-datum.

• End of change.

• Get material description

SELECT SINGLE maktx

FROM makt

INTO <wa_vbap>-maktx

WHERE matnr = <wa_vbap>-matnr

AND spras = sy-langu.

• Get division from material master

• SELECT SINGLE spart FROM mara INTO <wa_vbap>-spart

• WHERE matnr = <wa_vbap>-matnr.

• Get division description

SELECT SINGLE vtext

FROM tspat

INTO <wa_vbap>-vtext

WHERE spras = sy-langu

AND spart = <wa_vbap>-spart.

• Get Valuation Class

SELECT SINGLE bklas

FROM mbew

INTO lv_bklas

WHERE matnr = <wa_vbap>-matnr.

• Determine G/L of FG Based on Valuation Class

READ TABLE lt_val_cls INTO wa_val_cls WITH TABLE KEY bklas = lv_bklas.

<wa_vbap>-fghkont = wa_val_cls-hkont.

• Determine Cost Center of Cogs Account

READ TABLE lt_ulsub INTO wa_ulsub WITH TABLE KEY spart = <wa_vbap>-spart.

<wa_vbap>-cogskostl = wa_ulsub-kostlp.

ENDLOOP.

[former_member226234]

Hi,

As Narendra mention, using select inside the loop, the database access increases. Instead use select all the required outside the loop using 'FOR ALl ENTRIES'. When processing inside the loop you can read this initernal table to get the data. Sometimes due to business requirement we may have to use Nested loops which is perfectly fine as long as all the performance tuning is taken into consideration. This code needs to go through a lot of performance tuning as follows.

1) No Select statements should be inside the loop

2) Use Binary search for each READ statement. Sort the internal table before

READ on the key you are going to do a Binary Search.

3) Instead of 'SELECT SINGLE.... ' use SELECT... UP TO 1 ROWS...

ENDSELECT, if all the primary keys are not available for data selection. This

reduces performance issues.

Please do let me know in case of any doubts...

Regards,

Sandeep

[Former Member]

Hi,

Move all the SQL statements outside the loop..by using FOR ALL ENTRIES select the data and put in an internal table..

Then use READ TABLE itab to read the records...

Thanks,

Naren