topic Re: SELECT statement, more efficient? in Application Development and Automation Discussions

SELECT statement, more efficient?

Former Member — Mon, 23 Apr 2007 14:04:38 GMT

Hi,

How to make this code faster/better more efficient?


  LOOP AT it_output INTO wa_output.

*Select currency from table Company Codes
    SELECT SINGLE waers
           INTO   wa_output-waers
           FROM   t001
           WHERE  bukrs EQ wa_output-bukrs.

*select Purchasing Source List
    SELECT SINGLE ebeln ebelp werks lifnr
           FROM   eord
           INTO CORRESPONDING FIELDS OF wa_output
           WHERE  matnr EQ wa_output-matnr
           AND    werks EQ wa_output-bwkey.


    " Select manis(Base Unit of measure) from table MARA

    SELECT SINGLE meins
           INTO   wa_output-meins
           FROM   mara
           WHERE  matnr EQ wa_output-matnr.


    " Select lv_adrnr from table lfa1, need to select search term
    SELECT SINGLE adrnr
           INTO   lv_adrnr
           FROM   lfa1
           WHERE  lifnr EQ wa_output-lifnr.
    IF sy-subrc EQ '0'.

    " Select search term from table adrc
      SELECT SINGLE sort1
         INTO   wa_output-sort1
         FROM   adrc
         WHERE  addrnumber EQ lv_adrnr.
    ENDIF.

    MODIFY it_output FROM wa_output.

*it_output_down is used for download to excel
    APPEND wa_output TO it_output_down.

  ENDLOOP.

Adnan.

Re: SELECT statement, more efficient?

Former Member — Mon, 23 Apr 2007 14:10:11 GMT

I wil give an example for one select, do the same for all other selects inside the loops

if not it_output[] is initial.

SELECT bukrs waers

INTO table it_waers

FROM t001

for all entries in it_output

WHERE bukrs EQ it_output-bukrs.

sort it_waers by bukrs waers.

endif.

LOOP AT it_output INTO wa_output.

*Select currency from table Company Codes

read table it_waers into wa_waers

With key bukrs EQ wa_output-bukrs binary search.

if sy-surbc = 0.

wa_output-waers = wa_waers-waers.

endif.

*select Purchasing Source List

SELECT SINGLE ebeln ebelp werks lifnr

FROM eord

INTO CORRESPONDING FIELDS OF wa_output

WHERE matnr EQ wa_output-matnr

AND werks EQ wa_output-bwkey.

" Select manis(Base Unit of measure) from table MARA

SELECT SINGLE meins

INTO wa_output-meins

FROM mara

WHERE matnr EQ wa_output-matnr.

" Select lv_adrnr from table lfa1, need to select search term

SELECT SINGLE adrnr

INTO lv_adrnr

FROM lfa1

WHERE lifnr EQ wa_output-lifnr.

IF sy-subrc EQ '0'.

" Select search term from table adrc

SELECT SINGLE sort1

INTO wa_output-sort1

FROM adrc

WHERE addrnumber EQ lv_adrnr.

ENDIF.

MODIFY it_output FROM wa_output.

*it_output_down is used for download to excel

APPEND wa_output TO it_output_down.

ENDLOOP.

Re: SELECT statement, more efficient?

Former Member — Mon, 23 Apr 2007 14:10:36 GMT

*Select currency from table Company Codes
    SELECT  bukrs waers
           INTO   table  it_waers 
           FROM   t001.

sort it_waers by bukrs.

 LOOP AT it_output INTO wa_output.

   read table it_waers with key bukrs  eq wa_output-bukrs binary search.
    if sy-subrc eq 0.
          it_waers-waers = wa_output-waers.
    endif.

"like wise declare internal tables for each select single and use READ inside loop

endloop.

Re: SELECT statement, more efficient?

former_member491305 — Mon, 23 Apr 2007 14:10:51 GMT

Hi,

Avoid select stmt. inside the loop.Use for all entries to fetch those details.

And then use Read table stmt inside the loop to get those details into it_output.

Re: SELECT statement, more efficient?

Former Member — Mon, 23 Apr 2007 14:23:57 GMT

Hi Adibo,

the code can be made efficient to a far extend.

*Select currency from table Company Codes

refresh: it_comp_codes.

SELECT bukrs waers

INTO table it_comp_codes

FROM t001

for all entries in it_output

WHERE bukrs EQ it_output-bukrs.

sort it_comp_codes.

*select Purchasing Source List

refresh it_pur_source.

SELECT matnr werks ebeln ebelp werks lifnr

FROM eord

INTO table it_pur_source

for all entries in it_output

WHERE matnr EQ it_output-matnr

AND werks EQ it_output-bwkey.

sort it_pur_source.

*Select manis(Base Unit of measure) from table MARA

refresh it_measure.

SELECT matnr meins

INTO table it_measure

FROM mara

for all entries in it_output

WHERE matnr EQ it_output-matnr.

sort it_measure.

*Select lv_adrnr from table lfa1, need to select search term

refresh it_search_term.

SELECT a_{lifnr a}adrnr b~sort1

INTO table it_search_term

FROM lfa1 as a inner join adrc as b

on a_{adrnr = b}addrnumber

for all entries in it_output

WHERE lifnr EQ it_output-lifnr.

sort it_search_term.

Loop at it_output into wa_output.

*now read the internal tables using Binary search.

endloop.

*hope this solves ur issue of performance***

Do not forget to mark points for Helpful answers.