topic Re: Performance issue - which one is better in Application Development and Automation Discussions

Performance issue - which one is better

Former Member — Wed, 02 Jan 2008 16:09:42 GMT

Method 1 :

IF ig_matwerks[] IS NOT INITIAL.
SELECT m~matnr
m~werks
m~ekgrp
m~mmsta
m~lgfsb
m~eisbe
m~bstfe
k~maktx
FROM marc AS m INNER JOIN makt AS k
ON m_{matnr = k}matnr
INTO CORRESPONDING FIELDS OF TABLE ig_prefinal
FOR ALL ENTRIES IN ig_matwerks
WHERE m~matnr = ig_matwerks-matnr AND
m~werks = ig_matwerks-werks AND
k~spras = sy-langu.

***

SELECT m~matnr
r~werks
m~mtart
m~matkl
FROM mara AS m INNER JOIN marc AS r
ON m_{matnr = r}matnr
INTO CORRESPONDING FIELDS OF TABLE ig_mara
FOR ALL ENTRIES IN ig_matwerks
WHERE m~matnr = ig_matwerks-matnr AND
r~werks = ig_matwerks-werks.
ENDIF.

***

Moving values mtart & matkl to prefinal table

***

IF ig_prefinal[] IS NOT INITIAL.
LOOP AT ig_prefinal.
SORT ig_mara BY matnr werks.
READ TABLE ig_mara WITH KEY matnr = ig_prefinal-matnr werks = ig_prefinal-werks BINARY SEARCH TRANSPORTING mtart matkl.
IF SY-SUBRC = 0.
ig_prefinal-mtart = ig_mara-mtart.
ig_prefinal-matkl = ig_mara-matkl.
MODIFY ig_prefinal TRANSPORTING mtart matkl.
ENDIF.
CLEAR: ig_mara, ig_prefinal.
ENDLOOP.
ENDIF.

( OR )

Method 2:

IF ig_matwerks[] IS NOT INITIAL.

SELECT m~matnr

m~werks

m~ekgrp

m~mmsta

m~lgfsb

m~eisbe

m~bstfe

k~maktx

a~mtart

a~matkl

FROM ( ( marc AS m INNER JOIN makt AS k

ON m_{matnr = k}matnr )

INNER JOIN mara AS a

ON a_{matnr = k}matnr )

INTO CORRESPONDING FIELDS OF TABLE ig_prefinal

FOR ALL ENTRIES IN ig_matwerks

WHERE m~matnr = ig_matwerks-matnr AND

m~werks = ig_matwerks-werks AND

a~matnr = ig_matwerks-matnr AND

k~spras = sy-langu.

ENDIF.

Please let me know ....

Regards,

Abaper.

Re: Performance issue - which one is better

Former Member — Wed, 02 Jan 2008 16:12:10 GMT

Method 2 is best than method 1 ,if possible do not use corresponding fields statement within select query,this will save some time.

Thanks

Seshu

Re: Performance issue - which one is better

rodrigo_paisante3 — Wed, 02 Jan 2008 16:12:58 GMT

Hi, try to test the performance in SE30.

You can test others ways too.

regards.

Re: Performance issue - which one is better

Former Member — Wed, 02 Jan 2008 20:33:22 GMT

The second method is better for the following reasons.

1) You access the database once.

2) You avoid the loop.

Just 3 points to note.

1) Make sure that the table used in the FOR ALL ENTRIES clause has unique MATNR and WERKS combinations.

2) The where clause a~matnr = ig_matwerks-matnr AND is unnecessary as your JOIN statement

INNER JOIN mara AS a ON a_{matnr = k}matnr already takes care of this.

3) Use INTO TABLE instead of INTO CORRESPONDING FIELDS OF TABLE.

Take a look at the code below.

DATA: ig_matwerks_tmp LIKE TABLE OF ig_matwerks.

IF ig_matwerks[] IS NOT INITIAL.

ig_matwerks_tmp[] = ig_matwerks[].

SORT ig_matwerks_tmp BY matnr werks.

DELETE ADJACENT DUPLICATES FROM ig_matwerks_tmp

COMPARING matnr werks.

SELECT m~matnr

m~werks

m~ekgrp

m~mmsta

m~lgfsb

m~eisbe

m~bstfe

k~maktx

a~mtart

a~matkl

FROM ( ( marc AS m INNER JOIN makt AS k

ON m_{matnr = k}matnr )

INNER JOIN mara AS a

ON a_{matnr = k}matnr )

INTO TABLE ig_prefinal

FOR ALL ENTRIES IN ig_matwerks_tmp

WHERE m~matnr = ig_matwerks_tmp-matnr AND

m~werks = ig_matwerks_tmp-werks AND

k~spras = sy-langu.

Edited by: Mark Christian on Jan 2, 2008 3:33 PM

Re: Performance issue - which one is better

Former Member — Mon, 07 Jan 2008 13:51:55 GMT

Better go for Select ..... For all enteries .....

Regards

Naveen Gupta

Re: Performance issue - which one is better

Former Member — Tue, 08 Jan 2008 08:44:31 GMT

use get runtime field start and get runtime field stop and measure t = stop - start by yourself,

do not forget to repeat the measurements several times

=> no guess but a prove and you get a feeling about the magnitude

Siegfried

Re: Performance issue - which one is better

Former Member — Tue, 08 Jan 2008 09:32:19 GMT

Hi,

You can still use LOOP after the SELECT statement (Option 1). This depends on the data volume you are trying to retrieve and how busy is your data server. Having MARA and MARC in 2 separate internal tables allow you the flexibility to reuse MARA for your other routines. Please pair MAKT with MARA instead of with MARC.

Also the LOOP statement relieves the data server of the burden on having to match MARC with MARA as this will be handled by the application server. But you have to put your SORT statement outside of the LOOP.

You can use SE30 - Runtime Evaluation to see how data execution was distributed.

Kind Regards,

Darwin

Re: Performance issue - which one is better

rodrigo_paisante3 — Tue, 08 Jan 2008 10:17:46 GMT

Hi again,

see this blog by Rob:

/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better