topic Re: Improve SELECT statement? in Application Development and Automation Discussions

Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 15:15:27 GMT

Hi,

Can someone plz help me to improve my code:


  SELECT * FROM  lfa1
           WHERE konzs IN so_conc.

    SELECT * FROM  bseg
             WHERE lifnr EQ lfa1-lifnr.


      wa_table-konzs = lfa1-konzs.
      wa_table-lifnr = bseg-lifnr.
      wa_table-bukrs = bseg-bukrs.
      wa_table-wrbtr = bseg-wrbtr.
      wa_table-belnr = bseg-belnr.
      wa_table-gjahr = bseg-gjahr.
      wa_table-buzei = bseg-buzei.

      APPEND wa_table TO it_table.

    ENDSELECT.

  ENDSELECT.

Adibo..:)

Re: Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 15:19:00 GMT

Can u plz be more clear .

As I understood, It is better to select the required fields from database tables instead selecting all ( * ).

Cheers.

Re: Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 15:21:11 GMT

hi,

Avoid using nested selects, donot include * and specify the required fields ... use select into table statement rather than select end select statement ..


  SELECT konzs FROM  lfa1
         into table it_lfa1 
           WHERE konzs IN so_conc.
 
    SELECT lifnr 
           bukrs 
          ..
          FROM  bseg
         into table it_bseg
           WHERE lifnr EQ lfa1-lifnr.
 
 loop at it_lfa1.
  read table it_bseg with key konzs = it_lfa1-konzs.
   if sy-subrc = 0.
      it_table-konzs = lfa1-konzs.
      it_table-lifnr = bseg-lifnr.
      it_table-bukrs = bseg-bukrs.
      it_table-wrbtr = bseg-wrbtr.
      it_table-belnr = bseg-belnr.
      it_table-gjahr = bseg-gjahr.
      it_table-buzei = bseg-buzei.
 
      APPEND  it_table.
  endif.

Regards,

Santosh

Re: Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 15:22:01 GMT

by improve i mean faster..

Re: Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 15:24:07 GMT

To start with, use BSIK and BSAK instead of BSEG.

Rob

Re: Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 15:24:16 GMT

declare internal table it_lfa1 with lifnr & konzs fields.

SELECT lifnr konzs FROM lfa1 into table it_lfa1

WHERE konzs IN so_conc.

if not it_lfa1[] is initial.

loop at it_lfa1.

SELECT belnr gjahr buzei bukrs wrbtr FROM bseg

WHERE lifnr EQ lfa1-lifnr.

wa_table-konzs = lfa1-konzs.

wa_table-lifnr = bseg-lifnr.

wa_table-bukrs = bseg-bukrs.

wa_table-wrbtr = bseg-wrbtr.

wa_table-belnr = bseg-belnr.

wa_table-gjahr = bseg-gjahr.

wa_table-buzei = bseg-buzei.

APPEND wa_table TO it_table.

ENDSELECT.

endloop.

endif.

Re: Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 15:25:35 GMT

First...never use SELECT-ENDSELECT...


DATA: T_LFA1 TYPE STANDARD TABLE OF LFA1 WITH HEADER LINE,
          T_BSEG TYPE STANDARD TABLE OF BSEG WITH HEADER LINE.

SELECT * 
INTO TABLE T_LFA1
FROM  lfa1
WHERE konzs IN so_conc.

SELECT * 
INTO TABLE T_BSEG
FROM  bseg
FOR ALL ENTRIES IN T_LFA1
WHERE lifnr EQ T_LFA1-lifnr.

LOOP AT T_BSEG.
READ TABLE T_LFA1 WITH KEY LIFNR = T_BSEG-LIFNR.
IF SY-SUBRC EQ 0.
wa_table-konzs = lfa1-konzs.
ENDIF.
wa_table-lifnr = t_bseg-lifnr.
wa_table-bukrs = t_bseg-bukrs.
wa_table-wrbtr = t_bseg-wrbtr.
wa_table-belnr = t_bseg-belnr.
wa_table-gjahr = t_bseg-gjahr.
wa_table-buzei = t_bseg-buzei.
APPEND wa_table TO it_table.
ENDLOOP.

Greetings,

Blag.

Re: Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 15:25:42 GMT

HI,

Select lifnr from lfa1 into table it_lfa1 where konzs in so_conc.

loop at it_lfa1.

select lifnr bukrs wrtbr belnr gjahr buzei from bseg into it_table where lifnr EQ it_lfa1-lifnr.

it_table-konzs = it_lfa1-konzs.

append it_table.

Endselect.

endloop.

Regards

CNU

Re: Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 15:26:25 GMT

SELECT * FROM  lfa1 into table it_lfa1
           WHERE konzs IN so_conc.

if not it_lfa1[] is initial.
  SELECT * FROM  bseg into table it_bseg
             for all entries in it_lfa1
             WHERE lifnr EQ it_lfa-lifnr.
endif.

loop at it_lfa1.
  read table it_bseg with key lifnr eq it_lfa1-lifnr.
   if sy-subrc eq 0.
       wa_table-konzs = it_lfa1-konzs.
      wa_table-lifnr = it_bseg-lifnr.
      wa_table-bukrs = it_bseg-bukrs.
      wa_table-wrbtr = it_bseg-wrbtr.
      wa_table-belnr = it_bseg-belnr.
      wa_table-gjahr = it_bseg-gjahr.
      wa_table-buzei = it_bseg-buzei.
     APPEND wa_table TO it_table.

   endif.

Re: Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 15:26:37 GMT

hi,

do in the below specified fashion to improve the performance of your code ..


  SELECT konzs FROM  lfa1
         into table it_lfa1 
           WHERE konzs IN so_conc.
 
    SELECT lifnr 
           bukrs 
          ..
          FROM  bseg
         into table it_bseg
           WHERE lifnr EQ lfa1-lifnr.
 
 loop at it_lfa1.
  read table it_bseg with key konzs = it_lfa1-konzs.
   if sy-subrc = 0.
      it_table-konzs = lfa1-konzs.
      it_table-lifnr = bseg-lifnr.
      it_table-bukrs = bseg-bukrs.
      it_table-wrbtr = bseg-wrbtr.
      it_table-belnr = bseg-belnr.
      it_table-gjahr = bseg-gjahr.
      it_table-buzei = bseg-buzei.
 
      APPEND  it_table.
  endif.

regards,

santosh

Re: Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 15:28:52 GMT

Hi,

Follow the following points.

1.Retrieve the lfa1-lifnr from lfa1 table

select lifnr from lfa1 into table i_lfa1

where konzs in so_conc.

here i_lfa1 is an internal table with field lifnr.

2. By using all entries in i_lfa1 retrieve data from bseg.

select (required fields) into table i_bseg

for all entries in i_lfa1

where lifnr = i_lfa1-lifnr.

3. Now you can build it_table using the data in these two tables.By using loop at and read table statements.

Reward points if helpful.

Thanks

Giridhar Karnam

Re: Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 15:30:02 GMT

HI Adibo,

Instead of cluster table BSEG, use BSIS, BSAS, BSID, BSAD, BSIK or BSAK tables...

Regards,

Vivek

Re: Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 15:34:47 GMT

Hi Adibo,

Use BSIK transparent table as you have all entries required rather than doing a select on BSEG.

Vivek

Re: Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 15:36:24 GMT

I agree with Rob and Vivek. In order to improve the performance, you should use BSIK and BSAK tables instead of BSEG:


  SELECT * FROM  lfa1
           WHERE konzs IN so_conc.
 
    SELECT * FROM  bsik
             WHERE lifnr EQ lfa1-lifnr.
 
 
      wa_table-konzs = lfa1-konzs.
      wa_table-lifnr = bsik-lifnr.
      wa_table-bukrs = bsik-bukrs.
      wa_table-wrbtr = bsik-wrbtr.
      wa_table-belnr = bsik-belnr.
      wa_table-gjahr = bsik-gjahr.
      wa_table-buzei = bsik-buzei.
 
      APPEND wa_table TO it_table.
 
    ENDSELECT.
 
    SELECT * FROM  bsak
             WHERE lifnr EQ lfa1-lifnr.
 
 
      wa_table-konzs = lfa1-konzs.
      wa_table-lifnr = bsak-lifnr.
      wa_table-bukrs = bsak-bukrs.
      wa_table-wrbtr = bsak-wrbtr.
      wa_table-belnr = bsak-belnr.
      wa_table-gjahr = bsak-gjahr.
      wa_table-buzei = bsak-buzei.
 
      APPEND wa_table TO it_table.
 
    ENDSELECT.
 
  ENDSELECT.

I hope it helps. Best regards,

Alvaro

Re: Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 15:42:33 GMT

Hi ALL, thanks!!

I am trying everything you posted..

Re: Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 15:50:00 GMT

This should work:


REPORT ztest.

TABLES: lfa1, bseg, bkpf, bsik, bsak.

SELECT-OPTIONS so_conc FOR lfa1-konzs.

DATA: BEGIN OF lifnr_int OCCURS 0,
        lifnr LIKE lfa1-lifnr,
        konzs LIKE lfa1-konzs,
      END   OF lifnr_int,

      BEGIN OF wa_table,
        konzs LIKE lfa1-konzs,
        lifnr LIKE lfa1-lifnr,
        bukrs LIKE bsik-bukrs,
        wrbtr LIKE bsik-wrbtr,
        belnr LIKE bsik-belnr,
        gjahr LIKE bsik-gjahr,
        buzei LIKE bsik-buzei,
      END OF wa_table,

      it_table LIKE wa_table OCCURS 0.

SELECT lifnr konzs FROM  lfa1
  INTO TABLE lifnr_int
           WHERE konzs IN so_conc.
SORT lifnr_int BY lifnr.

SELECT lifnr bukrs wrbtr belnr gjahr buzei
  FROM  bsik
  INTO CORRESPONDING FIELDS OF wa_table
  FOR ALL ENTRIES IN lifnr_int
  WHERE lifnr EQ lifnr_int-lifnr.

  READ TABLE lifnr_int WITH KEY
    lifnr = wa_table-lifnr
    BINARY SEARCH.
  wa_table-konzs = lifnr_int-konzs.
  APPEND wa_table TO it_table.

ENDSELECT.

SELECT lifnr bukrs wrbtr belnr gjahr buzei
  FROM  bsak
  INTO CORRESPONDING FIELDS OF wa_table
  FOR ALL ENTRIES IN lifnr_int
  WHERE lifnr EQ lifnr_int-lifnr.

  READ TABLE lifnr_int WITH KEY
    lifnr = wa_table-lifnr
    BINARY SEARCH.
  wa_table-konzs = lifnr_int-konzs.
  APPEND wa_table TO it_table.

ENDSELECT.

Rob

Re: Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 15:54:14 GMT

You should also select SHKZG from both BSIK and BSAK (the debit/credit indicator).

Rob

Re: Improve SELECT statement?

Former Member — Mon, 22 Jan 2007 16:02:23 GMT

Wooow what a different!!

This works fine!

Thanks ALL!!



FORM selectie_concerncode.

  DATA: t_lfa1 TYPE STANDARD TABLE OF lfa1 WITH HEADER LINE,
            t_bsik TYPE STANDARD TABLE OF bsik WITH HEADER LINE,
            t_bsak TYPE STANDARD TABLE OF bsak WITH HEADER LINE.

  SELECT * INTO TABLE  t_lfa1
           FROM        lfa1
           WHERE       konzs IN so_conc.

  SELECT * INTO TABLE t_bsik
           FROM       bsik
           FOR ALL ENTRIES IN t_lfa1
           WHERE      lifnr EQ t_lfa1-lifnr.

  LOOP AT t_bsik.
    READ TABLE t_lfa1 WITH KEY lifnr = t_bsik-lifnr.
    IF sy-subrc EQ 0.
      wa_table-konzs = t_lfa1-konzs.
    ENDIF.
    wa_table-lifnr = t_bsik-lifnr.
    wa_table-bukrs = t_bsik-bukrs.
    wa_table-wrbtr = t_bsik-wrbtr.
    wa_table-belnr = t_bsik-belnr.
    wa_table-gjahr = t_bsik-gjahr.
    wa_table-buzei = t_bsik-buzei.
    APPEND wa_table TO it_table.
  ENDLOOP.


  SELECT * INTO TABLE t_bsak
           FROM       bsak
           FOR ALL ENTRIES IN t_lfa1
           WHERE      lifnr EQ t_lfa1-lifnr.

  LOOP AT t_bsak.
    READ TABLE t_lfa1 WITH KEY lifnr = t_bsak-lifnr.
    IF sy-subrc EQ 0.
      wa_table-konzs = t_lfa1-konzs.
    ENDIF.
    wa_table-lifnr = t_bsak-lifnr.
    wa_table-bukrs = t_bsak-bukrs.
    wa_table-wrbtr = t_bsak-wrbtr.
    wa_table-belnr = t_bsak-belnr.
    wa_table-gjahr = t_bsak-gjahr.
    wa_table-buzei = t_bsak-buzei.
    APPEND wa_table TO it_table.
  ENDLOOP.


ENDFORM.