program optimization

[Former Member]

hello all,

I need your expert opinion, can you please suggest on how can i optimize this code?

SELECT ekko_{ebeln eket}eindt ekko_{lifnr ekko}reswk ekpo~werks

ekko_{bsart ekko}ekgrp ekpo_{ebelp ekpo}matnr eket~menge

eket_{wamng ekpo}meins ekpo_{elikz ekpo}eglkz ekko~unsez

ekko_{ihrez ekpo}retpo ekko_{bedat ekpo}evers eket~wemng

ekpo_{loekz ekko}absgr ekpo~bukrs

FROM ekko

JOIN ekpo

ON ekpo_{ebeln EQ ekko}ebeln

JOIN eket

ON eket_{ebeln EQ ekpo}ebeln

AND eket_{ebelp EQ ekpo}ebelp

JOIN mara

ON mara_{matnr EQ ekpo}matnr

INTO CORRESPONDING FIELDS OF TABLE i_tab

WHERE ekko~bsart IN s_bsart

AND ekko~bedat IN s_bedat

AND ekko~ebeln IN s_ebeln

AND ekko~ekgrp IN s_ekgrp

AND ekko~lifnr IN s_lifnr

AND ekko~reswk IN s_reswk

AND ekko~absgr IN s_absgr

AND ekpo~werks IN s_werks

AND ekpo~evers IN s_evers

AND ekpo~matnr IN s_matnr

• AND ekpo~loekz EQ space

AND eket~eindt IN s_eindt

AND mara~mtart IN s_mtart.

[Former Member]

Hi,

First based on the selection screen data, fetch the data from the table EKKO into some internal table T_EKKO. Now only for the T_EKKO entries by adding other conditions fetch the data from the EKPO into internal table T_EKPO. Like that using the for all entries and check the sy-subrc = 0, after every select, you can improve the performance.

Don't forget to check the internal table is not initial before using FOR ALL ENTRIES statement.

Regards,

Santhosh.

[kanishakgupta1]

Use select for all entreies queries for fertching the data from single tables into internal tables.

sort the internal tables based on common fields.]

Then appling loop read the data from the internal tables using binary search.

Also use field symbols.

regards

kanishak

[Former Member]

Hello all,

so will I completely disregard the use of JOIN tables in the existing code?

[kanishakgupta1]

yes

use select staement to fetch the data from a table and then from other table fetch the data with select for all entries from other table into other interbnal table.

now play with ur internal tables which has all the data..

[Former Member]

Hi,

It seems you are writing more number of lines of code but it is the preferred one to use FOR ALL ENTRIES etc kind of usage in the select queries.

So don't use any JOIN kind of statements in SELECT staments.

Let's say even if you do not have data in the dependecy table, if you use JOIN, it unnecessarily hit the data base.

System will get time out dump in production systems, though it works fine in the development systems.

Regards,

Santhosh.

[Former Member]

Hi,

You can do like the following:

SELECT ekko_{ebeln ekko}bsart ekko_{lifnr ekko}ekgrp ekko~bedat

ekko_{ihrez ekko}unsez ekko_{reswk ekko}absgr

ekpo_{ebelp ekpo}matnr ekpo_{bukrs ekpo}werks ekpo_{meins ekpo}evers

ekpo_{retpo ekpo}elikz ekpo_{eglkz ekpo}loekz

eket_{menge eket}eindt eket_{wamng eket}wemng

FROM ekko

JOIN ekpo

ON ekpo_{ebeln EQ ekko}ebeln

JOIN eket

ON eket_{ebeln EQ ekpo}ebeln

AND eket_{ebelp EQ ekpo}ebelp

JOIN mara

ON mara_{matnr EQ ekpo}matnr

INTO CORRESPONDING FIELDS OF TABLE i_tab

WHERE ekko~ebeln IN s_ebeln

AND ekko~bsart IN s_bsart

AND ekko~lifnr IN s_lifnr

AND ekko~ekgrp IN s_ekgrp

AND ekko~bedat IN s_bedat

AND ekko~reswk IN s_reswk

AND ekko~absgr IN s_absgr

AND ekpo~matnr IN s_matnr

AND ekpo~werks IN s_werks

AND ekpo~evers IN s_evers

• AND ekpo~loekz EQ space

AND eket~eindt IN s_eindt

AND mara~mtart IN s_mtart.

If at all needed, you can use only Select statement as follows:

SELECT ekko_{ebeln ekko}bsart ekko_{lifnr ekko}ekgrp ekko~bedat

ekko_{ihrez ekko}unsez ekko_{reswk ekko}absgr

ekpo_{ebelp ekpo}matnr ekpo_{bukrs ekpo}werks ekpo_{meins ekpo}evers

ekpo_{retpo ekpo}elikz ekpo_{eglkz ekpo}loekz

eket_{menge eket}eindt eket_{wamng eket}wemng

FROM ekko

JOIN ekpo

ON ekpo_{ebeln EQ ekko}ebeln

JOIN eket

ON eket_{ebeln EQ ekpo}ebeln

AND eket_{ebelp EQ ekpo}ebelp

INTO CORRESPONDING FIELDS OF TABLE i_tab

WHERE ekko~ebeln IN s_ebeln

AND ekko~bsart IN s_bsart

AND ekko~lifnr IN s_lifnr

AND ekko~ekgrp IN s_ekgrp

AND ekko~bedat IN s_bedat

AND ekko~reswk IN s_reswk

AND ekko~absgr IN s_absgr

AND ekpo~matnr IN s_matnr

AND ekpo~werks IN s_werks

AND ekpo~evers IN s_evers

• AND ekpo~loekz EQ space

AND eket~eindt IN s_eindt.

Then, Select matnr from mara into it_mara for all entries in i_tab where matnr = i_tab-matnr and mtart in s_mtart.

loop at it_mara.

delete those entries in i_tab where it_matnr <> i_tab-matnr.

endloop.

Thanks and Best Regards,

Suresh.

[Former Member]

Hi,

Dont neglect using INNER JOINs. Because your selection condition (input condition) is like that.

Hope if you go for some other way other than using INNER JOINs you will lose some data.

Thanks and Best Regards,

Suresh

[Former Member]

Try using the view WB2_V_EKKO_EKPO2 it contains entries of both EKKO and EKPO for all the fields, just the filed names have extension _I. Chek for the view in ur system first.

Edited by: vijetasap on May 5, 2009 8:45 AM

[ThomasZloch]

You have tons of IN operators in your select, the ranges could hold anything, from nothing (=everything) to a single value. Runtime and access path will vary based on the contents of the ranges.

Don't believe the "use FOR ALL ENTRIES" tips, the JOIN conditions look OK at quick glance.

Also:

Thomas

[Former Member]

Hi,

1) Split the join for better performance.

2) While joining the table use MANDT field inthe on condition for better perfromance.

30 Avoid using 'Into corresponding fields' in the SQL.

Check the below optimized code.

-> Split the JOIN first fetch teh data from EKKO & EKPO into internal tbale I_EKKO using below SQL .

SELECT ekko_{ebeln EKKO}EBELP ekko_{lifnr ekko}reswk ekpo~werks

ekko_{bsart ekko}ekgrp ekpo_{ebelp ekpo}matnr

ekpo_{meins ekpo}elikz ekpo_{eglkz ekko}unsez

ekko_{ihrez ekpo}retpo ekko_{bedat ekpo}evers

ekpo_{loekz ekko}absgr ekpo~bukrs

FROM ekko

JOIN ekpo

ON ekpo_{MANDT EQ ekko}MANDT and ekpo_{ebeln EQ ekko}ebeln

INTO TABLE i_ekko

WHERE ekko~bsart IN s_bsart

AND ekko~bedat IN s_bedat

AND ekko~ebeln IN s_ebeln

AND ekko~ekgrp IN s_ekgrp

AND ekko~lifnr IN s_lifnr

AND ekko~reswk IN s_reswk

AND ekko~absgr IN s_absgr

AND ekpo~werks IN s_werks

AND ekpo~evers IN s_evers

AND ekpo~matnr IN s_matnr

• AND ekpo~loekz EQ space.

->then Query EKET with I_EKKO-EBELN & EBELP.

Select ebeln ebelp menge eindt

wamng wemng

from EKET

into table i_EKET

for all entries in I_EKKO

where ebeln = i_ekko-ebeln and

ebelp = i_ekko-ebelp

AND eket~eindt IN s_eindt.

if sy-subrc = 0.

sort i_eket by ebeln ebelp.

endif.

->Then Query MARA with with I_EKKO.

MOve the data from i_EKKO to I_EKKO_TMP.

Sort I_EKKO_TMP by MATNR.

Delete the duplicates from I_EKKO_TMP comparing MATNR.

->use the below SQL to fetch the data mara.

select MATNR into I_MARA

from MARA

for all entries in I_EKKO_TMP

where matnr = i_EKKO_TMP-matnr

AND mara~mtart IN s_mtart.

if sy-subrc = 0.

sort i_mara by matnr.

endif.

then loop the table I_EKKO and reasd I_EKET and I_MARA and popualte the final internal table.

Check the below code.

loop at I_EKKO.

Read tbale i_MATA with key MATNR = I_EKKO-MATNR.

if sy-subrc = 0.

--Move fieldds from i_EKKO to itab fields*

read table i_eket eith key ebeln = i_ekko-ebeln

ebelp = i_ekko-ebelp.

if sy-subrc = 0.

--Move fieldds from i_EKET to itab fields*

endif.

Append itab.

endif.

endloop.

[Former Member]

OK, OK, OK - this is all my fault. I thought by combining some useful links into one, people would have more information at their finger tips and it would help and I could remove the sticky from and reduce some clutter at the top.

I've added back the sticky. Please read it if you haven't already.

Rob

[Rui_Dantas]

Yes, it's really amazing that people continue to suggest FOR ALL ENTRIES as the ultimate performance solution.

One of last comments even suggested to add MANDT in the join. That one I would say is rarer. I find it strange that people give these kind of advices when they obviously never tried it themselves (bectause I'm sure that never solved a performance issue...).

[ThomasZloch]

> Yes, it's really amazing that people continue to suggest FOR ALL ENTRIES as the ultimate performance solution.

Please join us in keeping such misinformation in check.

What I find disturbing as well is that often the OP does not comment on the answers.

Solved? Not a problem anymore? Overwhelmed by the replies?

[Former Member]

Hi all,

Thank you for all your suggestions, as of now I haven't tried them yet because I am not sure what is the best solution for this. I will keep you posted as soon as i have applied the code.

[Former Member]

Ideally you want to identify a suitable index on EKKO, and get rid of any superflous selection options.

[Former Member]

WHERE ekko~bsart IN s_bsart
AND ekko~bedat IN s_bedat
AND ekko~ebeln IN s_ebeln
AND ekko~ekgrp IN s_ekgrp
AND ekko~lifnr IN s_lifnr
AND ekko~reswk IN s_reswk
AND ekko~absgr IN s_absgr
AND ekpo~werks IN s_werks
AND ekpo~evers IN s_evers
AND ekpo~matnr IN s_matnr
* AND ekpo~loekz EQ space
AND eket~eindt IN s_eindt
AND mara~mtart IN s_mtart.

The is the other issue which is completely ignored in this forum. This WHERE condition allows lots of different accesses. Not the ranges go to the database, but the actual inputs in the ranges als ranges which are not filled disappear.

There are 12 different ranges, even with simple inputs of one value with equal, I get 2 ** 12 = 4096 different combinations!

Overall you can not expect that each of these combinations work fine, for example if there is onyl an

input in s_werks, then the performance will never be good, no way! But that is a stupid task anyway.

You must find out which tasks make sense and should be supported by indexes.

These, and that will be not only one, you must try to optimize!

With FOR ALL ENTRIES you would have to program different orders of the procesing, starting with one or the other table. A lot of work.

With the join is will process in different orders automatically and a JOIN is faster than a FAE always

if it works fine. There is only the problem, that such a hard task is not automatically solved by the join in all combinations.

Siegfried