select statement from bkpf slows down the system

[Former Member]

hi guys,

i have this syntax for this coding:

SELECT * FROM bkpf

WHERE bukrs = ekko-bukrs

AND awkey = xawkey

AND gjahr = i_ir-budat(4)

AND blart = 'RE'.

CLEAR: titab.

titab-ebeln = itab-ebeln.

titab-ebelp = itab-ebelp.

titab-budat = bkpf-budat.

titab-bldat = bkpf-bldat.

titab-xblnr = bkpf-xblnr.

APPEND titab.

ENDSELECT.

this coding really slows down the report seleection and sytem , can anybody advice on how to select the better way

[gopi_narendra]

define an internal table it_bkpf

data : it_bkpf type table of BKPF initial size 0,
       is_bkpf type bkpf.
 " if not specify a internal table with the desired fields.
data : begin of it_bkpf occurs 0 with header line,
         belnr like bkpf-belnr,
         ........." give in all fields necessary
       end of it_bkpf.

select * from BKPF
         into table it_bkpf
         where bukrs = ekko-bukrs
           and awkey = xawkey
           and gjahr   = i_ir_budat(4)
           and blart    = 'RE'.

 " if you have defined ur own internla table using occurs 0 as above
 " write the select as below
 " <fields> resemble the fields of the internal table
select <fields> from bkpf
                into table it_bkpf
                where bukrs = ekko-bukrs
                  and awkey = xawkey
                  and gjahr   = i_ir_budat(4)
                  and blart    = 'RE'.

Regards

Gopi

Message was edited by:

Gopi Narendra

[Former Member]

hi Gopi,

still it slows down the sytem , anyways thanks for the help

[gopi_narendra]

Yes it still slows because you have not used the BELNR in your select statement which is a primary key, <u>using the primary keys gives you good performance</u>.

More over using select and endselect worsens the performance.

so avoid using select endselect and use into table method of coding which always yields you good performance.

Regards

Gopi

[Former Member]

lv_awkey+0(10) = gwa_ekbein-belnr.

lv_awkey+10(4) = gwa_ekbein-gjahr.

Hi frnd,

SELECT SINGLE *

INTO gwa_bkpf

FROM bkpf

WHERE bukrs = '6520'

AND gjahr = gwa_ekbein-gjahr

AND awtyp = 'RMRP'

AND awkey = lv_awkey.

IF sy-subrc = 0.

gwa_ekbein-xrefr = gwa_bkpf-belnr.

gwa_ekbein-cpudt = gwa_bkpf-cpudt.

ENDIF.

Try to give all the primary key values in the selection and also try to give awtyp and awkey.

The tables BKPF and BSEG used to have lot of entries.

I too faced the same problem....

Thanks

[Former Member]

Hi Ester,

First you need to avoid the select and endselect statement by using SELECT INTO TABLE.

Second issue is u are using non-key fields in where conditions are aweky, belnr and u r not using the belnr which is first key-field in the BKPF table.

Third one is try to use the fields in correct order which it is in table say in ur where codition it should be like bukrs, gjahr, blart, awkey,....

if you dont have a input field BELNR and u need to do really improve the performance with these input fields wht you have, its is better to create Secondary index with proper sequence for felds.

But i am not sure to give permission to create secondary index bec it takes more memory occupation like what exact BKPF tbale contains, you need to permission from client people along with BASIS people.

i am give sample code :

SELECT * FROM bkpf " try to specify the internal table with exact fileds instead '*'

into table tbl _ bkpf

WHERE belnr = w_belnr

and bukrs = ekko-bukrs

AND gjahr = i_ir-budat(4)

AND blart = 'RE'.

AND awkey = xawkey.

If sy-subrc = 0.

sory tbl_bkpf by belnr.

endif.

Let me know if any doubts.

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

Regards,

Vija

[Former Member]

Hi Estar,

From the code I understand that you are trying to pass the values into TITAB.

For performance, try using the SELECT stmt. as

SELECT * from (DB TABLE) into TABLE (Int. Table) . This will give sure shot performance.

Hope this resolves your query.

Reward All the helpful answers.

Regards

Nagaraj

[uongf]

Hi Ester,

a lot of valid responses from previous replies... i would suggest the following:

1. instead of your select.. append.. endselect, use

select ( field list ) into corresponding fields of table titab

from bkpf

where....

2. do a SQL db trace via tx ST05 and check which index was selected during execution of your select statement. use that info and go to SE11 and see if you have any other secondary indices that support your SELECT statement. if not, you may have to create a secondary index with the fields you used in the WHERE clause.

3. you mentioned slows down report selection and the system... is it just the report that got slower or overall system performance? if it is only the report, then steps 1 & 2 should alleviate it... if however, the entire system feels sluggish even with 1 & 2 implemented, then your Basis folks needs to do tuning and capacity planning on your database server - it may need more threads, memory, re-org, raw power, etc. if you have a huge number of old records, archiving them, then reorg of db's and indices could help. either way - you need Basis involved in this exercise.

hope this helps.

[ThomasZloch]

BKPF has a secondary index BKPF~4:

MANDT

AWTYP

AWKEY

AWSYS

If you add AWTYP to your select statement (should be "RMRP" in your case, but doublecheck), it should run much faster. Check also if that index is active in your database.

Cheers

Thomas