very slow SELECT SINGLE

[Former Member]

Hi. I have problem with SELECT SINGLE statement.

DATA: BEGIN OF itab OCCURS 0,
        lifnr LIKE s012-lifnr,
        wewrt_b LIKE s012-wewrt_b,
        wewrt_r_b LIKE s012-wewrt_r_b,
        name1 LIKE lfa1-name1,
      END OF itab.

DATA: spmon LIKE s012-spmon.
DATA: ekorg LIKE s012-ekorg.
spmon = '200701'.
ekorg = '1200'.

*SELECT s012~lifnr wewrt_b wewrt_r_b FROM s012
*JOIN lfa1 ON lfa1~lifnr = s012~lifnr
SELECT lifnr wewrt_b wewrt_r_b FROM s012
INTO CORRESPONDING FIELDS OF itab
WHERE spmon = spmon AND
      ekorg = ekorg.
  COLLECT itab.
ENDSELECT.


SORT itab BY lifnr.

LOOP AT itab.
  SELECT SINGLE name1 FROM lfa1 INTO itab-lifnr
  WHERE lifnr = itab-lifnr.
  APPEND itab.
ENDLOOP.

SELECT with COLLECT inside performs very fast but LOOP at the end of code - it's a tragedy. itab has only 62 records. Why it takes so long?

Message was edited by:

Piotr Wojciechowski

[Former Member]

hi

good

use CORRESPONDING FIELDS OF TABLE with the select single ,hope it would be bit faster than the before,if possible try to filter the data using the where condition as per the data you required from the table.

thanks

mrutyun^

[former_member189629]

Piotr,

Y r u overwriting ITAB-LIFNR? Instead just use 1 SELECT like below

SELECT lfa1_{lifnr s012}wewrt_b s012~wewrt_r_b FROM lfa1

INNER JOIN s012 ON lfa1_{lifnr = s012}lifnr

INTO TABLE itab

WHERE spmon = spmon AND

ekorg = ekorg.

COLLECT itab.

ENDSELECT.

The result must be same...

reward if helpful,

ragards,

Karthik

Message was edited by:

Karthik

[Former Member]

hi

good

use CORRESPONDING FIELDS OF TABLE with the select single ,hope it would be bit faster than the before,if possible try to filter the data using the where condition as per the data you required from the table.

thanks

mrutyun^

[ThomasZloch]

Hi Piotr,

replace your loop with this code:

LOOP AT itab.

SELECT SINGLE name1 FROM lfa1 INTO itab-<b>name1</b>

WHERE lifnr = itab-lifnr.

<b>MODIFY</b> itab.

ENDLOOP.

Cheers

Thomas

[Former Member]

Hi,

Dont use INTO CORRESPONDIG FIELDS which it cause perfromance issue.

Here i am recoding.....

DATA: BEGIN OF itab OCCURS 0,

lifnr LIKE s012-lifnr,

wewrt_b LIKE s012-wewrt_b,

wewrt_r_b LIKE s012-wewrt_r_b,

END OF itab.

DATA: BEGIN OF itab_name OCCURS 0,

lifnr LIKE s012-lifnr,

name1 LIKE lfa1-name1,

END OF itab_name .

DATA: BEGIN OF itab_final OCCURS 0,

lifnr LIKE s012-lifnr,

wewrt_b LIKE s012-wewrt_b,

wewrt_r_b LIKE s012-wewrt_r_b,

name1 LIKE lfa1-name1,

END OF itab_final .

DATA: spmon LIKE s012-spmon.

DATA: ekorg LIKE s012-ekorg.

spmon = '200701'.

ekorg = '1200'.

SELECT lifnr wewrt_b wewrt_r_b FROM s012

INTO TABLE itab

WHERE spmon = spmon AND

ekorg = ekorg.

IF sy-subrc = 0.

sort itab by lifnr.

endif.

If not itab[] is initial.

SELECT lifnr name1

FROM lfa1

INTO itab_name

FOR ALL ENTRIES IN itab

WHERE lifnr = itab-lifnr.

sort itab_name by lifnr.

endif.

loop at itab.

itab_final-lifnr = itab-lifnr

itab_final-wewrt_b = itab-wewrt_b

itab_final-wewrt_r_b = itab-wewrt_r_b

clear itab_name .

READ table itab_name with lifnr = itab-lifnr BINARY SEARCH.

if sy-subrc = 0.

itab_final-name1 = itab_name-name1.

APPEND itab_final.

Clear itab,itab_final.

endloop.

The above code give good porformance.

<b>Reward with points if helpful.</b>

Regards,

Vijay