BSIS performance is very Slow in query

[Former Member]

Dear All,

How to increase performance for the following query BSIS

select a~saknr as glcode

b~txt50 as gldesc

into corresponding fields of table it_final

from skb1 as a

inner join skat as b

on a_{saknr = b}saknr

where a~saknr in p_glcode

and a~bukrs eq company

and b~spras eq 'EN'

and b~ktopl eq 'ABIX'.

sort it_gl by saknr.

• the above query get 220 G/L accounts.*

if not it_final[] is initial.

select

bukrs

hkont

augdt

augbl

zuonr

gjahr

belnr

buzei

budat

werks

kostl

aufnr

shkzg

dmbtr

prctr

into corresponding fields of table it_bseg

from bsis

for all entries in it_final

where bukrs eq company

and prctr eq s_prctr

and gjahr eq s_year

and hkont eq it_final-glcode

and budat in s_budat "BETWEEN fromdt AND todt .

*above query taken above 30 minutes in production.

give me suggestions to tune query .

Regards,

Moon

Edited by: GoldMoon on Jan 13, 2010 4:35 PM

[Former Member]

HI Moon ,

Any Query on BSEG ,is going to hamper the performance . The reson for this is BSEG is the Cluster table. Indexes are hardly used in select queries on Cluster table.

To overcome this drawback, SAP has provided some secondary tables Like :

BSID Accounting: Secondary Index for Customers

BSAD Accounting: Secondary Index for Customers(Cleared Items)

BSAS Accounting: Secondary Index for G/L Accounts (Cleared Items)

BSIS Accounting: Secondary Index for G/L Accounts

BSAK Accounting: Secondary Index for Vendors (Cleared Items)

BSIK Accounting: Secondary Index for Vendors

Try to used any of the above tables as per your business requirement. Performance will be improved for sure.

Hope this will help to you.

Regards,

Nikhil

[Former Member]

HI nIkil,

My internal table is it_bseg , but i am accessing BSIS in query.

regards

[Former Member]

Hi Moon ,

Try to use IN operator( By creating RANGES table for IT_BSIS) in second select query instead of For all entries.

Hope this will help to you.

Regards,

Nikhil

[former_member182566]

>

> ** the above query get 220 G/L accounts.*
> .........
>
>
>........ 
>          for all entries in it_final
>          where bukrs eq company
>          and   prctr eq s_prctr
>          and   gjahr eq s_year			   				
>          and   hkont eq it_final-glcode
>          and   budat in s_budat                    "BETWEEN fromdt AND todt .
> 

You are only using BUKRS + HKONT from the primary key.

How many records do you have in BSIS?

If in SE16 you filter by BUKRS and your 120 HKONT's, how many entries are there?

And if you add the filter by PRCTR how many entries are there?

And if you add the BUDAT interval?

This should determine if an extra index would be advisable.

In our system we do have a Z index in BSIS by MANDT + BUKRS + HKONT + BUDAT.

[Former Member]

Hi,

In our BSIS table contains 60 lac records.

There are 3 profit centers.

I am passing only 1 profit center through selection screen.

And I am psssing 220 HKONTs.having 3 lac records.

I created Zindex for all select Columns.

(bukrs,hkont,augdt,augbl,zuonr,gjahr,belnr,buzei,budat,werks,kostl,aufnr,shkzg,dmbtr,prctr)

it will takes 20 to 30 minutes for executing.

give me suggestions to solve problem

thanks in advance

[Former Member]

Hi,

i have the same problem and was solved using index creation for Bsis table.

Regards,

Conrado

[todosap.blogspot.com|http://todosap.blogspot.com]

[Former Member]

Hi GoldMoon,

(1)

Refer to your last posting, seems that you've created index which wouldn't help your query performance.

You need to create index from the field which supplied from your 'WHERE' condition. So if I follow your initial posting in this thread, your index should contains:

- BUKRS (which will be compared with your company variable)

- PRCTR (which will be compared with your s_prctr variable)

- GJAHR (which will be compared with your s_year variable)

- HKONT (which will be compared with your glcode value in your it_final[])

- BUDAT (which will be compared with your s_budat range/select option.

And to follow the BSIS field order, the above index field need to be arranged as follow

- BUKRS

- HKONT

- GJAHR

- BUDAT

- PRCTR

(2).

Try not to use 'INTO CORRESPONDING FIELDS' as it will increase table-memory overhead, but use 'INTO' and make sure the field list and internal table fields is in the same order.

So, once the index has been created, and 'INTO CORRESPONDING FIELDS' has been changed to 'INTO' your selection should looks like this:

IF NOT it_final[] IS INITIAL.
SELECT bukrs
          hkont
          augdt
          augbl
          zuonr
          gjahr
          belnr
          buzei
          budat
          werks
          kostl
          aufnr
          shkzg
          dmbtr
          prctr
    FROM bsis
     INTO TABLE it_bseg
      FOR ALL ENTRIES IN it_final
 WHERE bukrs eq company
    AND hkont eq it_final-glcode
    AND gjahr eq s_year
    AND budat in s_budat
    AND prctr eq s_prctr.

ENDIF.

Hope it helps

[Former Member]

solved by me.

[Former Member]

Dear ,

can you provide me code for it.

Thanks in advance.

Tapovardhan