performance problems in SELECTs

[joan_ayala]

Hi friends,

I wanted to do this SELECT, but I cannot put the condition "ekpo~ebelp EQ lips~vgpos" in my INNER JOIN because one field is CHAR5 and the other one is CHAR6.

*    SELECT ekpo~ebeln ekpo~ebelp ekko~bukrs ekko~bsart ekko~lifnr ekko~ekorg ekko~ekgrp ekpo~werks

*           likp~vstel likp~bolnr likp~traid lips~matnr makt~maktx

*      INTO CORRESPONDING FIELDS OF TABLE gt_datos

*      FROM ( ( ( ( ekko INNER JOIN ekpo

*                           ON ekko~ebeln EQ ekpo~ebeln )

*                              INNER JOIN lips

*                                 ON ekpo~ebeln EQ lips~vgbel AND

*                                    ekpo~ebelp EQ lips~vgpos )

*                                    INNER JOIN likp

*                                       ON lips~vbeln EQ likp~vbeln )

*                                          INNER JOIN makt

*                                             ON lips~matnr EQ makt~matnr )

*     WHERE makt~spras EQ sy-langu AND

*           ekko~bukrs IN s_bukrs AND

*           ekko~bsart IN s_bsart AND

*           ekko~lifnr IN s_lifnr AND

*           ekko~ekorg IN s_ekorg AND

*           ekko~ekgrp IN s_ekgrp AND

*           ekpo~ebeln IN s_ebeln AND

*           ekpo~ebelp IN s_ebelp AND

*           ekpo~werks IN s_werks AND

*           likp~vstel IN s_vstel AND

*           likp~bolnr IN s_bolnr AND

*           likp~traid IN s_traid.

Instead, I did it the following way, but it is inefficient and sometimes I get a dump because there is not enough memory. Could anybody help me to make the following code more efficient? Thank you very much.

     SELECT ekpo~ebeln ekpo~ebelp ekko~bukrs ekko~bsart ekko~lifnr ekko~ekorg ekko~ekgrp ekpo~werks

       INTO CORRESPONDING FIELDS OF TABLE gt_datos

       FROM ekko INNER JOIN ekpo

                    ON ekko~ebeln EQ ekpo~ebeln

      WHERE ekko~bukrs IN s_bukrs AND

            ekko~bsart IN s_bsart AND

            ekko~lifnr IN s_lifnr AND

            ekko~ekorg IN s_ekorg AND

            ekko~ekgrp IN s_ekgrp AND

            ekpo~ebeln IN s_ebeln AND

            ekpo~ebelp IN s_ebelp AND

            ekpo~werks IN s_werks.

     LOOP AT gt_datos INTO gs_datos.

       gs_datos_aux-vgbel = gs_datos-ebeln.

       CONCATENATE '0' gs_datos-ebelp INTO gs_datos_aux-vgpos.

       APPEND gs_datos_aux TO gt_datos_aux.

     ENDLOOP.

     SELECT lips~vgbel lips~vgpos likp~vstel likp~bolnr likp~traid lips~matnr makt~maktx

       INTO CORRESPONDING FIELDS OF TABLE gt_datos2

       FROM ( ( lips INNER JOIN likp

                        ON lips~vbeln EQ likp~vbeln )

                           INNER JOIN makt

                              ON lips~matnr EQ makt~matnr )

        FOR ALL ENTRIES IN gt_datos_aux

      WHERE lips~vgbel EQ gt_datos_aux-vgbel AND

            lips~vgpos EQ gt_datos_aux-vgpos AND

            makt~spras EQ sy-langu.

     LOOP AT gt_datos2 INTO gs_datos2.

       lv_tabix = sy-tabix.

       READ TABLE gt_datos INTO gs_datos WITH KEY ebeln = gs_datos2-vgbel

                                                  ebelp = gs_datos2-vgpos+1(5).

       gs_datos2-bukrs = gs_datos-bukrs.

       gs_datos2-bsart = gs_datos-bsart.

       gs_datos2-lifnr = gs_datos-lifnr.

       gs_datos2-ekorg = gs_datos-ekorg.

       gs_datos2-ekgrp = gs_datos-ekgrp.

       gs_datos2-werks = gs_datos-werks.

       MODIFY gt_datos2 FROM gs_datos2.

     ENDLOOP.

[vinodkumar_thangavel]

Hi,

Try to avoid using INTO CORRESPONDING FIELDS OF TABLE and check the same.

Regards,

Vinodkumar.

[RaymondGiuseppi]

Read Why "INTO CORRESPONDING" is much better than its reputation by Thomas Zloch.

Regards,

Raymond

[joan_ayala]

How could I avoid INTO CORRESPONDING FIELDS OF TABLE in my case?

I am using a table without header line.

[ipravir]

Hi Joan,

Try to avain CORRESPONDING FIELDS OF TABLE statement, if you are working with heavy tables or join query.

I also, if you are using FOR ALL ENTRIES, then try to pass the Unique field Informaiton in FOR ALL ENTRIES table, which is require for you query.

Duplicate entries can also take time to execute the query.

Regards.

Praveer.

[joan_ayala]

Hi,

I don't see how I can avoid INTO CORRESPONDING FIELDS OF TABLE...

anybody could help me?

Thanks

[RaymondGiuseppi]

Try to use a sorted/hashed table type for GT_DATOS definition. Also insure to keep only the required fields in your internal tables.

You could also fill the second table by batch of some thousands of records (SELECT ...PACKAGE SIZE adding also a ENDSELECT after the following LOOP  so this temporary table won't become too big.

SELECT lips~vgbel lips~vgpos likp~vstel likp~bolnr likp~traid lips~matnr makt~maktx
        INTO CORRESPONDING FIELDS OF TABLE gt_datos2 PACKAGE SIZE 10000 " add package size
  " unchanged lines
      ENDLOOP.
 ENDSELECT. " add select
 FREE: gt_datos2, gt_datos_aux. " no longer required

Also did you search OSS and read 185530 - Performance: Customer developments in SD? Did you create an index on LIPS per VGBEL, if no better use an intermediary table as VBFA (SELECT FROM vbfa WHERE VBELV  = ... and VBTYP_N = 'J'  JOIN or other SELECT FROM lips WHERE vbeln = vbfa-vbeln AND posnr = vbfa-posnn)

Regards,

Raymond

[srinivasan_vinayagam]

Hi Joan,

try to avoid inner joint and out joins.

find some performance tips from below wiki pages.

http://wiki.scn.sap.com/wiki/display/Community/ABAP+Performance+tips

ABAP Performance and Tuning - ABAP Development - SCN Wiki

Regards,

V Srinivasan