performance of select statement

[Former Member]

Hi everybody,

The following Select statement take to much of time.

first i am taking the doc.no based on the date interval

and after that with that doc.no and other criteria i am picking records

from bseg table but it taking much time.

please help me.

select belnr from bkpf into table it_doc

where bldat in s_bldat

and ( blart = 'CJ' or blart = 'KR'

or blart = 'KT' or blart = 'RE' or blart = 'RN' ) .

<b>

if not it_doc[] is initial.

sort it_doc by docno.

select * from bseg into table it_bseg

for all entries in it_doc

where belnr = it_doc-docno

and mwskz = p_mwskz

and gjahr = p_gjahr

and ( bschl = '40' or bschl = '31' OR BSCHL = '29' or

bschl = '83' or bschl = '86' ).

endif.</b>

Thanks in advance

V.Srinivasa rao.

[Former Member]

Hi,

Try using ranges for blart, i think it may help.

TABLES: BKPF.

RANGES: R_BLART FOR BKPF-BLART

R_BLART -SIGN = 'I'

R_BLART -OPTION = 'EQ'

R_BLART -LOW = 'CJ'

APPEND R_BLART .

R_BLART -SIGN = 'I'

R_BLART -OPTION = 'EQ'

R_BLART -LOW = 'KR'

APPEND R_BLART .

R_BLART -SIGN = 'I'

R_BLART -OPTION = 'EQ'

R_BLART -LOW = 'KT

APPEND R_BLART .

.

.

.

(CONTINUE APPENDING THIS FOR 'RE' AND 'RN' )

select belnr from bkpf into table it_doc

where bldat in s_bldat

and blart in r_blart.

do the same for 2nd select also..

Reward if helpful.

Regards,

Karthick.

[Former Member]

You need to add bukrs and gjahr to your internal table, retrieve them in your first SELECT and use them in the FOR ALL ENTRIES:

SELECT bukrs belnr gjahr
  FROM bkpf
  INTO TABLE it_doc
  WHERE bldat IN s_bldat
    AND ( blart = 'CJ'
     OR   blart = 'KR'
     OR blart = 'KT'
     OR blart = 'RE'
     OR blart = 'RN' ) .

IF NOT it_doc[] IS INITIAL.
  SORT it_doc BY bukrs docno gjahr.

  SELECT * FROM bseg
    INTO TABLE it_bseg
    FOR ALL ENTRIES IN it_doc
    WHERE bukrs = it_doc-bukrs
      AND belnr = it_doc-docno
      AND gjahr = it_doc-gjahr
      AND mwskz = p_mwskz
      AND gjahr = p_gjahr
      AND ( bschl = '40'
        OR bschl = '31'
        OR bschl = '29'
        OR bschl = '83'
        OR bschl = '86' ).

ENDIF

Rob

Changed SORT statement

Message was edited by:

Rob Burbank

[Former Member]

You need to add bukrs,gjahr to your internal table,and also retrieve them in your first SELECT and use them in the FOR ALL ENTRIES:

SELECT bukrs belnr gjahr

FROM bkpf

INTO TABLE it_doc

WHERE bldat IN s_bldat

AND ( blart = 'CJ'

OR blart = 'KR'

OR blart = 'KT'

OR blart = 'RE'

OR blart = 'RN' ) .

IF NOT it_doc[] IS INITIAL.

SORT it_doc BY bukrs docno gjahr.

SELECT * FROM bseg

INTO TABLE it_bseg

FOR ALL ENTRIES IN it_doc

WHERE bukrs = it_doc-bukrs

AND belnr = it_doc-docno

AND gjahr = it_doc-gjahr

AND mwskz = p_mwskz

AND gjahr = p_gjahr

AND ( bschl = '40'

OR bschl = '31'

OR bschl = '29'

OR bschl = '83'

OR bschl = '86' ).

ENDIF

I tried it is working perfectly with fastly.

Thanks,

Sandhya.

[Former Member]

Sandhya - very well cut and pasted.

Next time, try pressing the "code" button around the program code and you will get a better copy.

rob

[Former Member]

Hi,

Here you are using select * on BSEG. BSEG is a very huge table with huge amount of data and with huge number of fields.

Instead of using select * try to specify the fields you required (if required fields is not more than 30 or 40). It will defenetly improve the performance of the select.

regards,

Srinivasu Reddy.