Perfomance Improvement -urgent

Former Member · ‎10-16-2007

Hello friends,

Could you please help me to improve the perfomace of the below code

Below is my code

IF SO_MATNR IS INITIAL.

SELECT MARC_{MATNR MARC}WERKS MARC_{DISMM MARC}LGFSB

MARD_{LGORT MARD}LABST MARD~LGPBE

INTO CORRESPONDING FIELDS OF TABLE IT_DATA

FROM MARC

INNER JOIN MARD ON MARD_{MATNR EQ MARC}MATNR

AND MARD_{WERKS EQ MARC}WERKS

INNER JOIN MARA ON MARC_{MATNR EQ MARA}MATNR

WHERE MARC~WERKS IN SO_WERKS

AND MARC~DISMM IN SO_DISMM

AND MARD~LGORT IN SO_LGORT

AND MARA~MTART IN SO_MTART

AND MARA~LVORM EQ SPACE

AND MARA~MSTAE IN SO_MSTAE

AND MARC~LGFSB IN SO_LGFSB.

ELSE.

SELECT MARC_{MATNR MARC}WERKS MARC_{DISMM MARC}LGFSB

MARD_{LGORT MARD}LABST MARD~LGPBE

INTO CORRESPONDING FIELDS OF TABLE IT_DATA

FROM MARC

INNER JOIN MARD ON MARD_{MATNR EQ MARC}MATNR

AND MARD_{WERKS EQ MARC}WERKS

INNER JOIN MARA ON MARC_{MATNR EQ MARA}MATNR

WHERE MARC~MATNR IN SO_MATNR

AND MARC~WERKS IN SO_WERKS

AND MARC~DISMM IN SO_DISMM

AND MARD~LGORT IN SO_LGORT

AND MARA~MTART IN SO_MTART

AND MARA~LVORM EQ SPACE

AND MARA~MSTAE IN SO_MSTAE.

ENDIF.

The main internal table IT_DATA is having nerly 1million 90 thousand recordsin production server

IF SY-SUBRC IS INITIAL.

*- Filter: Lagerort nicht leer -

*

DELETE IT_DATA WHERE LGORT EQ SPACE.

SORT IT_DATA ASCENDING BY MATNR WERKS LGORT.

DELETE ADJACENT DUPLICATES FROM IT_DATA

COMPARING MATNR WERKS LGORT.

LOOP AT IT_DATA ASSIGNING .

MOVE SY-TABIX TO LVA_TABIX.

*- Filter vorhandene Lagerfachkärtchen für alle Dispomerkmale -

*

SELECT MANDT INTO SY-MANDT

FROM Z48M3_STOCKCARDS UP TO 1 ROWS

WHERE MATNR EQ -MATNR.

CONTINUE.

ENDIF.

*- Filter Lagerbestand -

*

PERFORM LAGERBESTAND IN PROGRAM Z48M_LFK_DRUCKEN

USING -DISMM.

*- Filter VB -

*

WHEN 'VB'.

PERFORM MAKTX USING -GTXT1.

CONTINUE.

*- Filter VK -

*

WHEN 'VK'.

PERFORM MAKTX USING -GTXT1.

CONTINUE.

*- Filter V1 -

*

WHEN 'V1'.

PERFORM MAKTX USING -GTXT1.

CONTINUE.

*- Filter PD -

*

WHEN 'PD'.

SELECT SINGLE MANDT INTO SY-MANDT

FROM Z48M_UEBRIGMAT

WHERE MATNR EQ -GTXT1.

CONTINUE.

ENDIF.

*- Filter VP -

*

WHEN 'VP'.

IF DELETE -

*

DELETE IT_DATA INDEX LVA_TABIX.

ENDLOOP.

ENDIF.

ENDFORM. " daten_lesen

In side the loop of internal table it_data so many caliculations are going on and finally if the quantity is (menge) is 0 they are deleting the record from the it_data.

The output is to display the final it_data after the above caliculations in the loop.
Its taking nearly 2 or 3 days without giving any ouput .
I have avoided 1) Select * statments
2) select statments inside the loop.
But due to above million records its not showing any improvementCould you please suggest me some important points to improve the performance of the above program.
Shall i go for sorted internal table ,the how the code will be by using sorted itab

waiting for replies,

Points will be awarded for your help.

Arvind.

rainer_hbenthal · ‎10-16-2007

Use the where condition to elimenate the deletes. Its funny to select all the values, haveing a huge database load, then transferring all the data into the app server, deleteing all unwanted rows. Better to deselect them in the SQL statement. Duplicates can be elimenate using unique or group by, unwanted entries can be excluded via AND, OR and NOT.

Former Member · ‎10-16-2007

Hi

JOINS : if you use join for more than 2 tables then it will give you bad performance

you know what happends if you use joins on tables

the data base connectivity exists up to the program execution that means load on the data base will be high , thats why it will result in bad performance

FOR ALL ENTRIES : use for all entries for ur query, with this also you can get output from more than 2 tables also

what happens if you use joins you know , the 1st select query will get the data from data base in one shot . there won't be any connection with data base so that it result in good performance

all ways don't use JOINS if you have more than 2 tables . use all ways FOR ALL ENTRIES

Reward if usefull

Former Member · ‎10-16-2007

HI ,

i tried the above points as u mentioned by using for all entries than join i tried but still the problem is there .

The total time consumption is inside the loop .

i forgot to paste some code ,below is my code..

IF SY-SUBRC IS INITIAL.

*- Filter: Lagerort nicht leer -

*

DELETE IT_DATA WHERE LGORT EQ SPACE.

SORT IT_DATA ASCENDING BY MATNR WERKS LGORT.

DELETE ADJACENT DUPLICATES FROM IT_DATA

COMPARING MATNR WERKS LGORT.

LOOP AT IT_DATA ASSIGNING .

MOVE SY-TABIX TO LVA_TABIX.

*- Filter vorhandene Lagerfachkärtchen für alle Dispomerkmale -

*

SELECT MANDT INTO SY-MANDT

FROM Z48M3_STOCKCARDS UP TO 1 ROWS

WHERE MATNR EQ -MATNR.

CONTINUE.

ENDIF.

*- Filter Lagerbestand -

*

PERFORM LAGERBESTAND IN PROGRAM Z48M_LFK_DRUCKEN

USING -DISMM.

*- Filter VB -

*

WHEN 'VB'.

PERFORM MAKTX USING -GTXT1.

CONTINUE.

*- Filter VK -

*

WHEN 'VK'.

PERFORM MAKTX USING -GTXT1.

CONTINUE.

*- Filter V1 -

*

WHEN 'V1'.

PERFORM MAKTX USING -GTXT1.

CONTINUE.

*- Filter PD -

*

WHEN 'PD'.

SELECT SINGLE MANDT INTO SY-MANDT

FROM Z48M_UEBRIGMAT

WHERE MATNR EQ -GTXT1.

CONTINUE.

ENDIF.

*- Filter VP -

*

WHEN 'VP'.

IF DELETE -

*

DELETE IT_DATA INDEX LVA_TABIX.

ENDLOOP.

ENDIF.

ENDFORM. " daten_lesen

****the below code is of the performs inside the loop.

FORM LAGERBESTAND USING UPA_MATNR TYPE TY_DATA-MATNR

UPA_WERKS TYPE TY_DATA-WERKS

UPA_LGORT TYPE TY_DATA-LGORT

UPA_DISMM TYPE TY_DATA-DISMM

URA_BWART TYPE TABLE

CHANGING UPA_XOK TYPE FLAG.

DATA: LWA_MARD TYPE MARD,

LWA_MSEG TYPE MSEG,

LIT_MSEG TYPE STANDARD TABLE OF MSEG,

LIT_HBGK TYPE STANDARD TABLE OF Z48M_HBGK.

MOVE 'X' TO UPA_XOK.

CHECK UPA_DISMM EQ 'PD'

OR UPA_DISMM EQ 'VP'.

SELECT SINGLE * INTO LWA_MARD

FROM MARD

WHERE MATNR EQ UPA_MATNR

AND WERKS EQ UPA_WERKS

AND LGORT EQ UPA_LGORT.

IF SY-SUBRC IS INITIAL.

SELECT * APPENDING TABLE LIT_MSEG

FROM MSEG

WHERE MATNR EQ UPA_MATNR

AND WERKS EQ UPA_WERKS

AND LGORT EQ UPA_LGORT

AND BWART IN URA_BWART

AND SOBKZ EQ 'Q'

AND XAUTO EQ SPACE.

PERFORM DEL_STORNO_BELEGE IN PROGRAM Z48M_ZAEHLISTE

TABLES LIT_MSEG.

REFRESH LIT_HBGK.

LOOP AT LIT_MSEG INTO LWA_MSEG.

SELECT * APPENDING TABLE LIT_HBGK

FROM Z48M_HBGK

WHERE MBLNR EQ LWA_MSEG-MBLNR

AND MJAHR EQ LWA_MSEG-MJAHR

AND ZEILE EQ LWA_MSEG-ZEILE.

ENDLOOP.

PERFORM GET_VORAB_BESTAND IN PROGRAM Z48M_ZAEHLISTE

TABLES LIT_HBGK

USING UPA_MATNR UPA_WERKS UPA_LGORT

CHANGING LWA_MARD-UMLME.

IF LWA_MARD-UMLME IS INITIAL

AND LWA_MARD-LABST IS INITIAL.

CLEAR UPA_XOK.

ENDIF.

ENDFORM. " lagerbestand

*****

FORM del_storno_belege TABLES pt_mseg STRUCTURE mseg.

DATA: lwa_mseg TYPE mseg.

Stornierte Belege

LOOP AT pt_mseg INTO wa_mseg.

Wurde Beleg storniert?

READ TABLE pt_mseg INTO lwa_mseg

WITH KEY sjahr = wa_mseg-mjahr

smbln = wa_mseg-mblnr

smblp = wa_mseg-zeile.

IF sy-subrc = 0.

Stornierter Beleg und Stornobeleg löschen

DELETE pt_mseg WHERE mblnr = wa_mseg-mblnr

AND mjahr = wa_mseg-mjahr

AND zeile = wa_mseg-zeile.

DELETE pt_mseg WHERE mblnr = lwa_mseg-mblnr

AND mjahr = lwa_mseg-mjahr

AND zeile = lwa_mseg-zeile.

ENDIF.

ENDLOOP.

*********

FORM get_vorab_bestand TABLES pt_z48m_hbgk

USING p_matnr

p_werks

p_lgort

CHANGING p_menge.

DATA: lt_z48m_hbgk TYPE TABLE OF z48m_hbgk,

lwa_z48m_hbgk TYPE z48m_hbgk.

DATA: lf_sbdkz LIKE marc-sbdkz,

lf_sum411 LIKE z48m_hbgk-rest,

lf_sum412 LIKE z48m_hbgk-rest.

-----------------------------------------------
Initialisierung

REFRESH: lt_z48m_hbgk.

CLEAR: p_menge, lf_sbdkz, lf_sum411, lf_sum412.

SELECT SINGLE sbdkz

INTO lf_sbdkz

FROM marc

WHERE matnr = p_matnr

AND werks = p_werks.

sc88wa2-15.11.05 angepaßt an ZPRUEF_HBGK

HBGK auslesen (Nicht physisch entnomme Mengen)

SELECT *

FROM z48m_hbgk INTO TABLE lt_z48m_hbgk

WHERE matnr = p_matnr

AND werks = p_werks

AND lgort = p_lgort.

AND zzdruck_kz = space

AND mblnr_v = space

AND kz_vorab = space
AND bwart <> c_bwart_411.

CHECK sy-subrc = 0.

**

SORT lt_z48m_hbgk BY erdat.

**

LOOP AT lt_z48m_hbgk INTO lwa_z48m_hbgk.

Nicht physisch entnommene Menge

ADD lwa_z48m_hbgk-rest TO p_menge.
ENDLOOP.

sc88wa2-15.11.05 angepaßt an ZPRUEF_HBGK

LOOP AT lt_z48m_hbgk INTO lwa_z48m_hbgk.

CASE lwa_z48m_hbgk-bwart.

WHEN 411.

Offene Menge der 411er Datensätze
bei Einzelbedarf muß das DruckKz. noch beachtet werden

IF ( lf_sbdkz = 2 ) OR

( lf_sbdkz = 1 AND lwa_z48m_hbgk-zzdruck_kz IS INITIAL ).

lf_sum411 = lf_sum411 + lwa_z48m_hbgk-rest.

ENDIF.

WHEN 412.

Offene Menge der 412er Datensätze
nur für initiales Druckkennzeichen

IF lwa_z48m_hbgk-zzdruck_kz IS INITIAL.

lf_sum412 = lf_sum412 + lwa_z48m_hbgk-rest.

ENDIF.

ENDCASE.

ENDLOOP.

gebucht nicht entnommen berechnen

p_menge = lf_sum412 - lf_sum411.

ENDFORM. " get_vorab_bestand

Could you please suggest some points for the above code to improve perfomacnce.

Thanks in advance,

Arvind.

Former Member · ‎10-16-2007

Dear Beesarla Arvind,

Order of arrangement is very important for joining tables in SAP Query. (Put the table with least primary key first for joining table.) Although there will be millions of records selected from database, it does not matter here.

Herewith is the code for reference:

IF SO_MATNR IS INITIAL.

SELECT DISTINCT

MARC~MATNR

MARC~WERKS

MARC~DISMM

MARC~LGFSB

MARD~LGORT

MARD~LABST

MARD~LGPBE

INTO CORRESPONDING FIELDS OF TABLE IT_DATA

FROM MARC INNER JOIN MARA

ON MARC_{MATNR EQ MARA}MATNR

INNER JOIN MARD

ON MARC_{MATNR EQ MARD}MATNR AND

MARC_{WERKS EQ MARD}WERKS

WHERE MARA~MTART IN SO_MTART AND

MARA~LVORM EQ SPACE AND

MARA~MSTAE IN SO_MSTAE AND

MARC~WERKS IN SO_WERKS AND

MARC~DISMM IN SO_DISMM AND

MARC~LGFSB IN SO_LGFSB AND

MARD~LGORT IN SO_LGORT AND

MARD~LGORT NE SPACE.

ELSE.

SELECT DISTINCT

MARC~MATNR

MARC~WERKS

MARC~DISMM

MARC~LGFSB

MARD~LGORT

MARD~LABST

MARD~LGPBE

INTO CORRESPONDING FIELDS OF TABLE IT_DATA

FROM MARC INNER JOIN MARA

ON MARC_{MATNR EQ MARA}MATNR

INNER JOIN MARD

ON MARC_{MATNR EQ MARD}MATNR AND

MARC_{WERKS EQ MARD}WERKS

WHERE MARA~MTART IN SO_MTART AND

MARA~LVORM EQ SPACE AND

MARA~MSTAE IN SO_MSTAE AND

MARC~MATNR IN SO_MATNR AND

MARC~WERKS IN SO_WERKS AND

MARC~DISMM IN SO_DISMM AND

MARD~LGORT IN SO_LGORT AND

MARD~LGORT NE SPACE.

ENDIF.

that equivalent to

IF SO_MATNR IS INITIAL.

SELECT MARC_{MATNR MARC}WERKS MARC_{DISMM MARC}LGFSB

MARD_{LGORT MARD}LABST MARD~LGPBE

INTO CORRESPONDING FIELDS OF TABLE IT_DATA

FROM MARC

INNER JOIN MARD ON MARD_{MATNR EQ MARC}MATNR

AND MARD_{WERKS EQ MARC}WERKS

INNER JOIN MARA ON MARC_{MATNR EQ MARA}MATNR

WHERE MARC~WERKS IN SO_WERKS

AND MARC~DISMM IN SO_DISMM

AND MARD~LGORT IN SO_LGORT

AND MARA~MTART IN SO_MTART

AND MARA~LVORM EQ SPACE

AND MARA~MSTAE IN SO_MSTAE

AND MARC~LGFSB IN SO_LGFSB.

ELSE.

SELECT MARC_{MATNR MARC}WERKS MARC_{DISMM MARC}LGFSB

MARD_{LGORT MARD}LABST MARD~LGPBE

INTO CORRESPONDING FIELDS OF TABLE IT_DATA

FROM MARC

INNER JOIN MARD ON MARD_{MATNR EQ MARC}MATNR

AND MARD_{WERKS EQ MARC}WERKS

INNER JOIN MARA ON MARC_{MATNR EQ MARA}MATNR

WHERE MARC~MATNR IN SO_MATNR

AND MARC~WERKS IN SO_WERKS

AND MARC~DISMM IN SO_DISMM

AND MARD~LGORT IN SO_LGORT

AND MARA~MTART IN SO_MTART

AND MARA~LVORM EQ SPACE

AND MARA~MSTAE IN SO_MSTAE.

ENDIF.

The main internal table IT_DATA is having nerly 1million 90 thousand recordsin production server

IF SY-SUBRC IS INITIAL.

- Filter: Lagerort nicht leer -

DELETE IT_DATA WHERE LGORT EQ SPACE.

SORT IT_DATA ASCENDING BY MATNR WERKS LGORT.

DELETE ADJACENT DUPLICATES FROM IT_DATA

COMPARING MATNR WERKS LGORT.

Hope this will help.

p/s: You can also find the problem using debugging mode.

Former Member · ‎10-16-2007

Hi,

i tried the above points as u mentioned but still the problem is there .

The total time consumption is inside the loop .

As showh below

IF SY-SUBRC IS INITIAL.

*- Filter: Lagerort nicht leer -

*

DELETE IT_DATA WHERE LGORT EQ SPACE.

SORT IT_DATA ASCENDING BY MATNR WERKS LGORT.

DELETE ADJACENT DUPLICATES FROM IT_DATA

COMPARING MATNR WERKS LGORT.

LOOP AT IT_DATA ASSIGNING .

MOVE SY-TABIX TO LVA_TABIX.

*- Filter vorhandene Lagerfachkärtchen für alle Dispomerkmale -

*

SELECT MANDT INTO SY-MANDT

FROM Z48M3_STOCKCARDS UP TO 1 ROWS

WHERE MATNR EQ -MATNR.

CONTINUE.

ENDIF.

*- Filter Lagerbestand -

*

PERFORM LAGERBESTAND IN PROGRAM Z48M_LFK_DRUCKEN

USING -DISMM.

*- Filter VB -

*

WHEN 'VB'.

PERFORM MAKTX USING -GTXT1.

CONTINUE.

*- Filter VK -

*

WHEN 'VK'.

PERFORM MAKTX USING -GTXT1.

CONTINUE.

*- Filter V1 -

*

WHEN 'V1'.

PERFORM MAKTX USING -GTXT1.

CONTINUE.

*- Filter PD -

*

WHEN 'PD'.

SELECT SINGLE MANDT INTO SY-MANDT

FROM Z48M_UEBRIGMAT

WHERE MATNR EQ -GTXT1.

CONTINUE.

ENDIF.

*- Filter VP -

*

WHEN 'VP'.

IF DELETE -

*

DELETE IT_DATA INDEX LVA_TABIX.

ENDLOOP.

ENDIF.

ENDFORM. " daten_lesen

****the below code is of the performs inside the loop.

FORM LAGERBESTAND USING UPA_MATNR TYPE TY_DATA-MATNR

UPA_WERKS TYPE TY_DATA-WERKS

UPA_LGORT TYPE TY_DATA-LGORT

UPA_DISMM TYPE TY_DATA-DISMM

URA_BWART TYPE TABLE

CHANGING UPA_XOK TYPE FLAG.

DATA: LWA_MARD TYPE MARD,

LWA_MSEG TYPE MSEG,

LIT_MSEG TYPE STANDARD TABLE OF MSEG,

LIT_HBGK TYPE STANDARD TABLE OF Z48M_HBGK.

MOVE 'X' TO UPA_XOK.

CHECK UPA_DISMM EQ 'PD'

OR UPA_DISMM EQ 'VP'.

SELECT SINGLE * INTO LWA_MARD

FROM MARD

WHERE MATNR EQ UPA_MATNR

AND WERKS EQ UPA_WERKS

AND LGORT EQ UPA_LGORT.

IF SY-SUBRC IS INITIAL.

SELECT * APPENDING TABLE LIT_MSEG

FROM MSEG

WHERE MATNR EQ UPA_MATNR

AND WERKS EQ UPA_WERKS

AND LGORT EQ UPA_LGORT

AND BWART IN URA_BWART

AND SOBKZ EQ 'Q'

AND XAUTO EQ SPACE.

PERFORM DEL_STORNO_BELEGE IN PROGRAM Z48M_ZAEHLISTE

TABLES LIT_MSEG.

REFRESH LIT_HBGK.

LOOP AT LIT_MSEG INTO LWA_MSEG.

SELECT * APPENDING TABLE LIT_HBGK

FROM Z48M_HBGK

WHERE MBLNR EQ LWA_MSEG-MBLNR

AND MJAHR EQ LWA_MSEG-MJAHR

AND ZEILE EQ LWA_MSEG-ZEILE.

ENDLOOP.

PERFORM GET_VORAB_BESTAND IN PROGRAM Z48M_ZAEHLISTE

TABLES LIT_HBGK

USING UPA_MATNR UPA_WERKS UPA_LGORT

CHANGING LWA_MARD-UMLME.

IF LWA_MARD-UMLME IS INITIAL

AND LWA_MARD-LABST IS INITIAL.

CLEAR UPA_XOK.

ENDIF.

ENDFORM. " lagerbestand

*****

FORM del_storno_belege TABLES pt_mseg STRUCTURE mseg.

DATA: lwa_mseg TYPE mseg.

Stornierte Belege

LOOP AT pt_mseg INTO wa_mseg.

Wurde Beleg storniert?

READ TABLE pt_mseg INTO lwa_mseg

WITH KEY sjahr = wa_mseg-mjahr

smbln = wa_mseg-mblnr

smblp = wa_mseg-zeile.

IF sy-subrc = 0.

Stornierter Beleg und Stornobeleg löschen

DELETE pt_mseg WHERE mblnr = wa_mseg-mblnr

AND mjahr = wa_mseg-mjahr

AND zeile = wa_mseg-zeile.

DELETE pt_mseg WHERE mblnr = lwa_mseg-mblnr

AND mjahr = lwa_mseg-mjahr

AND zeile = lwa_mseg-zeile.

ENDIF.

ENDLOOP.

*********

FORM get_vorab_bestand TABLES pt_z48m_hbgk

USING p_matnr

p_werks

p_lgort

CHANGING p_menge.

DATA: lt_z48m_hbgk TYPE TABLE OF z48m_hbgk,

lwa_z48m_hbgk TYPE z48m_hbgk.

DATA: lf_sbdkz LIKE marc-sbdkz,

lf_sum411 LIKE z48m_hbgk-rest,

lf_sum412 LIKE z48m_hbgk-rest.

-----------------------------------------------
Initialisierung

REFRESH: lt_z48m_hbgk.

CLEAR: p_menge, lf_sbdkz, lf_sum411, lf_sum412.

SELECT SINGLE sbdkz

INTO lf_sbdkz

FROM marc

WHERE matnr = p_matnr

AND werks = p_werks.

sc88wa2-15.11.05 angepaßt an ZPRUEF_HBGK

HBGK auslesen (Nicht physisch entnomme Mengen)

SELECT *

FROM z48m_hbgk INTO TABLE lt_z48m_hbgk

WHERE matnr = p_matnr

AND werks = p_werks

AND lgort = p_lgort.

AND zzdruck_kz = space

AND mblnr_v = space

AND kz_vorab = space
AND bwart <> c_bwart_411.

CHECK sy-subrc = 0.

**

SORT lt_z48m_hbgk BY erdat.

**

LOOP AT lt_z48m_hbgk INTO lwa_z48m_hbgk.

Nicht physisch entnommene Menge

ADD lwa_z48m_hbgk-rest TO p_menge.
ENDLOOP.

sc88wa2-15.11.05 angepaßt an ZPRUEF_HBGK

LOOP AT lt_z48m_hbgk INTO lwa_z48m_hbgk.

CASE lwa_z48m_hbgk-bwart.

WHEN 411.

Offene Menge der 411er Datensätze
bei Einzelbedarf muß das DruckKz. noch beachtet werden

IF ( lf_sbdkz = 2 ) OR

( lf_sbdkz = 1 AND lwa_z48m_hbgk-zzdruck_kz IS INITIAL ).

lf_sum411 = lf_sum411 + lwa_z48m_hbgk-rest.

ENDIF.

WHEN 412.

Offene Menge der 412er Datensätze
nur für initiales Druckkennzeichen

IF lwa_z48m_hbgk-zzdruck_kz IS INITIAL.

lf_sum412 = lf_sum412 + lwa_z48m_hbgk-rest.

ENDIF.

ENDCASE.

ENDLOOP.

gebucht nicht entnommen berechnen

p_menge = lf_sum412 - lf_sum411.

ENDFORM. " get_vorab_bestand

Could you please suggest some points .

Thanks,

Arvind.

Former Member · ‎10-17-2007

Dear Beesarla Arvind,

I found that you had use too many select statements inside loop. This really decrease the performance of your program due to too many interaction between database and application server.

Probably you can try to find out the relationship between all tables that you needs to use and join them together. I had tried to join five tables before, this really helps.

It is also very important that not to delete records of internal table when you do looping. If you delete lines within a LOOP ... ENDLOOP block, the deletion affects subsequent loop passes.

Hope this will helps.

Former Member · ‎10-16-2007

Your problem is likely due to the following:


LOOP AT it_data ASSIGNING <lfs_data>.
...
    DELETE it_data WHERE matnr EQ <lfs_data>-matnr.
...
    DELETE it_data WHERE matnr EQ <lfs_data>-matnr
...
etc.
...
ENDLOOP.

These are treated as nested loops and will be extremely slow.

Rob

Former Member · ‎10-16-2007

Hello Rob,

The time consumption is extremly large inside the loop and endloop.

Could you please suggest me ,what changes shall i made?

instead of below code ,what thing should i follow?

LOOP AT it_data ASSIGNING <lfs_data>.

...

DELETE it_data WHERE matnr EQ <lfs_data>-matnr.

...

DELETE it_data WHERE matnr EQ <lfs_data>-matnr

...

etc.

...

ENDLOOP.

Thanks in advance,

Arvind.