topic Re: very slow SELECT SINGLE in Application Development and Automation Discussions

very slow SELECT SINGLE

Former Member — Wed, 26 Sep 2007 07:14:20 GMT

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

Re: very slow SELECT SINGLE

former_member189629 — Wed, 26 Sep 2007 07:27:45 GMT

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

Re: very slow SELECT SINGLE

Former Member — Wed, 26 Sep 2007 07:37:37 GMT

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^

Re: very slow SELECT SINGLE

ThomasZloch — Wed, 26 Sep 2007 07:54:23 GMT

Hi Piotr,

replace your loop with this code:

LOOP AT itab.

SELECT SINGLE name1 FROM lfa1 INTO itab-name1

WHERE lifnr = itab-lifnr.

MODIFY itab.

ENDLOOP.

Cheers

Thomas

Re: very slow SELECT SINGLE

Former Member — Wed, 26 Sep 2007 08:23:56 GMT

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.

Reward with points if helpful.

Regards,

Vijay