topic Re: performance problems in SELECTs in Application Development and Automation Discussions

performance problems in SELECTs

joan_ayala — Thu, 22 Jan 2015 08:57:02 GMT

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.

Re: performance problems in SELECTs

vinodkumar_thangavel — Thu, 22 Jan 2015 09:03:04 GMT

Hi,

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

Regards,

Vinodkumar.

Re: performance problems in SELECTs

RaymondGiuseppi — Thu, 22 Jan 2015 09:08:41 GMT

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

Regards,

Raymond

Re: performance problems in SELECTs

ipravir — Thu, 22 Jan 2015 09:10:06 GMT

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.

Re: performance problems in SELECTs

joan_ayala — Thu, 22 Jan 2015 09:12:36 GMT

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

I am using a table without header line.

Re: performance problems in SELECTs

RaymondGiuseppi — Thu, 22 Jan 2015 09:14:49 GMT

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

Re: performance problems in SELECTs

srinivasan_vinayagam — Thu, 22 Jan 2015 09:16:45 GMT

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

Re: performance problems in SELECTs

joan_ayala — Thu, 22 Jan 2015 09:41:46 GMT

Hi,

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

anybody could help me?

Thanks