topic BSAD table performance in Application Development and Automation Discussions

BSAD table performance

Former Member — Tue, 31 Jul 2007 02:26:10 GMT

Hi Experts,

I have a performance issue with the table BSAD.

I have an excel which has nearly 1660 records with ZUONR - Assignment number only in that records

But on the report selection screen i have UMSKZ - Special G/L Indicator and in the select query where clause i use AUGDT - Clearing Date & BLDAT - Document date in document

With these fields i query on the BSAD table using for all entries of the excel internal table which has 1660 records.

But seems that this is taking long time.

Can any body increase the performance of this query.

More over i have been using <b>Appending table</b> for it_accnt

see my query below :


* Selecting Data from BSID and BSAD tables
  select bukrs kunnr umskz zuonr gjahr belnr bldat
         xblnr shkzg dmbtr wrbtr sgtxt zfbdt
         from bsid
         into table it_accnt
         for all entries in it_tab1 " IT_TAB1 HAS 1660 RECORDS
         where umskz in so_umskz
           and zuonr eq it_tab1-zuonr
           and bldat le p_date.

  select bukrs kunnr umskz zuonr gjahr belnr bldat
         xblnr shkzg dmbtr wrbtr sgtxt zfbdt
         from bsad
         appending table it_accnt
         for all entries in it_tab1 " IT_TAB1 HAS 1660 RECORDS
         where umskz in so_umskz
           and zuonr eq it_tab1-zuonr
           and bldat le p_date
           and augdt ge p_date.

  if not it_accnt[] is initial.
    sort it_accnt by bukrs kunnr zuonr.
  endif.

Regards

Pratyusha

Re: BSAD table performance

Former Member — Tue, 31 Jul 2007 02:43:04 GMT

If the values of any additional key fields of the tables are available, please include in the where clause. this will improve performance. try fields like BUKRS, GJAHR.

Re: BSAD table performance

Former Member — Tue, 31 Jul 2007 03:02:53 GMT

Have a look at the indexes on BSAD and consider its primary purpose - i.e. it is designed to give you a way of getting cleared lines for a customer... therefore your selects should, if possible, contain as many of the following as possible:

BUKRS    
KUNNR    
UMSKS    
UMSKZ    
AUGDT    
AUGBL    
ZUONR    
GJAHR    
BELNR    
BUZEI

Basically if you don't have the customer number, then you may be better off first identifying matching documents from BKPF (e.g. doc type and date range) and then getting the associated customer lines from BSEG.

Re: BSAD table performance

Former Member — Tue, 31 Jul 2007 03:10:01 GMT

I agree to both of you Sudhir and Jonathan,

But in my case i only have the Assignment Number(ZUONR), Special G/L Indicator(UMSKZ)

Other than these two fields i dont have any other inputs for this table. So this is taking time infact i never executed the program totally, i always had to interrupt the transaction in between coz it went running for hours together.

If this is the case can i insist to get more inputs like BUKRS KUNNR from the user side. Will this improve my performance to an extent?

Regards

Pratyusha

Re: BSAD table performance

Former Member — Tue, 31 Jul 2007 03:47:39 GMT

Hi Pratyusha,

Even with all those details there will always be a performance issue with this table, but it will be less.

This table is containing huge amount of data in production so it is not advisable to query this table without all key fields.

Regards,

Atish

Re: BSAD table performance

Former Member — Tue, 31 Jul 2007 04:09:27 GMT

Hi Pratya,

You can create an index (to avoid full table scan) in table BSAD with key umskz, zuonr, bldat, augdt.

You can try it in you Development System and see how fast is it..

If the speed is not as expected than you can delete the index.

Regards,

Hendy

Re: BSAD table performance

Former Member — Wed, 01 Aug 2007 02:21:55 GMT

I used the primary keys BUKRS KUNNR, and it solved to a maximum extent

Thanks to all