doubt: For all entries or Loop

[Former Member]

hello experts,

in my report i had a select statement on mseg which is having a 7 lakh records

so it takes hell of time to give an output..

the query was like this..

<b>SELECT MBLNR MJAHR ZEILE BWART KDAUF KDPOS WERKS ERFMG

FROM MSEG INTO TABLE GT_MSEG

FOR ALL ENTRIES IN GT_VBAP

WHERE KDAUF = GT_VBAP-VBELN

AND KDPOS = GT_VBAP-POSNR

AND WERKS = GT_VBAP-WERKS

AND BWART IN (641, 101, 102).</b>

now i have chage the query to below and giving me the out put very fast and correct one..

<b> SELECT mblnr mjahr

zeile

bwart

kdauf

kdpos

werks

erfmg

xauto

FROM mseg

INTO TABLE gt_mseg1

WHERE bwart IN (641, 101, 102).

LOOP AT gt_mseg1 INTO wa_mseg.

READ TABLE gt_vbap INTO wa_vbap WITH KEY vbeln = wa_mseg-kdauf

posnr = wa_mseg-kdpos

werks = wa_mseg-werks.

IF sy-subrc = 0.

APPEND wa_mseg TO gt_mseg.

ENDIF.

ENDLOOP.</b>

but i have a doute that both this query gives same output and both means the same...

and particularly in case of mseg which one is better and correct one

[Former Member]

hello experts,

in my report i had a select statement on mseg which is having a 7 lakh records

so it takes hell of time to give an output..

the query was like this..

<b>SELECT MBLNR MJAHR ZEILE BWART KDAUF KDPOS WERKS ERFMG

FROM MSEG INTO TABLE GT_MSEG

FOR ALL ENTRIES IN GT_VBAP

WHERE KDAUF = GT_VBAP-VBELN

AND KDPOS = GT_VBAP-POSNR

AND WERKS = GT_VBAP-WERKS

AND BWART IN (641, 101, 102).</b>

now i have chage the query to below and giving me the out put very fast and correct one..

<b> SELECT mblnr mjahr

zeile

bwart

kdauf

kdpos

werks

erfmg

xauto

FROM mseg

INTO TABLE gt_mseg1

WHERE bwart IN (641, 101, 102).

LOOP AT gt_mseg1 INTO wa_mseg.

READ TABLE gt_vbap INTO wa_vbap WITH KEY vbeln = wa_mseg-kdauf

posnr = wa_mseg-kdpos

werks = wa_mseg-werks.

IF sy-subrc = 0.

APPEND wa_mseg TO gt_mseg.

ENDIF.

ENDLOOP.</b>

but i have a doute that both this query gives same output and both means the same...

and particularly in case of mseg which one is better and correct one

[Former Member]

Hi Raja,

1) Commend Either of the one select statement.

2) Go to SE30 transaction

3) In "Program" Give ur Program name.

4) Click on "Execute" Button

5) After Executing Click on "<- " back button ( Come out clicking back button only if its having a selection screen 2 may need tp press that twice )

6) Click on the "Evaluate " button below.

7) You can see The total time taken by Abap , Database and System.

Do the same for Select from Mseg into gt_mesg + Loop .

See which one is taking less time .You can see the details if you click "F5"

Reward points if help full.

Avi.....

[Former Member]

Hi Rajan,

Logically speaking first statement should take less time.

Just add

IF NOT GT_VBAP[] IS INITIAL.

SELECT MBLNR MJAHR ZEILE BWART KDAUF KDPOS WERKS ERFMG

FROM MSEG INTO TABLE GT_MSEG

FOR ALL ENTRIES IN GT_VBAP

WHERE KDAUF = GT_VBAP-VBELN

AND KDPOS = GT_VBAP-POSNR

AND WERKS = GT_VBAP-WERKS

AND BWART IN (641, 101, 102).

ENDIF.

Regards,

Atish

[Former Member]

Even i think so..

but i have checked in se30 so my lower statement(loop) works faster and

even i n my whole report output also gives beter performance

coz, my report was taking 10 min to give an output coz my mseg has 7 lakh and vbap(also an item table) has 10,000 entries

so for this perticular case it gives me the output in 1 min 85 sec only..

so i m in a dilema which one is better...

pls guide me on this

thx.

[romanweise]

I would never use the first statement for an easy reason. It uses more than one field of the FOR ALL ENTRIES INT-Table in the where clause. If you do so the database statement ignores all database caches which will decrease the performance extremely. getting lines from the db cache is saifd to be approx. 10 times faster than bypassing buffers.

Furthermore if you use the sencond statement you must ensure to dersign it in a way that you have a hash table and use the table key access. this ensures that the read statement won't need longer and longer the more lines you have. This should speed it even more.

Rgds.

Roman

BTW: SE30 is nice for performance checking but if you just want to check 2 statements against each other you can also navigate to the performance examples through menue in SE80 there u have boxes where u can enter the coding an check it.

[Former Member]

the first should be faster!

you should pay more attention the key fields you must select into the internal table

because sometime the record will be reduce

the best mthod is add index!