topic Re: performance issue with BSAD table in Application Development and Automation Discussions

performance issue with BSAD table

Former Member — Tue, 31 Jul 2007 02:31:37 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 Appending table for it_accnt

Is it likely that i should be giving in more KEY fields in the WHERE clause.

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.

<b>Kindly suggest, its urgent</b>

Regards

Pratyusha

Re: performance issue with BSAD table

Former Member — Tue, 31 Jul 2007 03:37:29 GMT

hi,

try to use inner join between the tables bsad and bsid.

regards,

Navneeth K.

Re: performance issue with BSAD table

Former Member — Tue, 31 Jul 2007 06:13:04 GMT

Can any body please answer???

Points for sure ..

Re: performance issue with BSAD table

Former Member — Tue, 31 Jul 2007 06:26:45 GMT

Hi,

Try using all key fields in your queries

In your second query instead of using appending, use INTO table and then you can consolidate into 1 singl etable by using the ABAP statement APPEND LINES OF...

Check if this improves the performance.

Good option is to avois for all entries. Workaround would be to create a new range table for ZUONR.

loop at it_tab1 .

append to new range table.

endloop.

use zuonr IN IT_RANGE in both your queries.

This will increase ABAP time but reduce DB access definetly.

Pls award pts if useful

Regards,

Shruthi

Re: performance issue with BSAD table

Former Member — Tue, 31 Jul 2007 09:32:37 GMT

Hi,

Do you a select option for special GL indicator (so_umskz)?

Please check if you can use a simple checkbox in the selection screen and have the condition like where umskz = p_umskz ( a parameter)

Check the data in driver table it_tab1. If it has duplicate records of zuonr U can use this trick

Create a temo table say it_temp like it_tab1.

Now it_temp[] = it_tab1.

Create a sorted and unique driver table

sort it_temp by zuonr.

delete adjacent duplicates from it_temp comparing zuonr.

Now use it_temo as the driver table.

Also check if u can have the Fiscal Year (GJAHR) as one of the selection parameter.

-Regards

Ashim

Re: performance issue with BSAD table

Former Member — Tue, 31 Jul 2007 13:04:28 GMT

You are not using any of the primary or secondary key fields for these SELECTs, so they will be slow. You can speed it up by using BKPF and BSEG instead because BKPF has a secondary index on BLDAT. It's better if you also know the company code, but if you don't, there are still ways to speed this up.

********************************************************

I did a bit of playing around with this and found that using BKPF and BSEG didn't help - probably too many tests for non-equal conditions.

I think the solution proposed by Ashim looks best now.

Rob

Message was edited by:

Rob Burbank

Re: performance issue with BSAD table

Former Member — Wed, 01 Aug 2007 02:20:27 GMT

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

Thanks to all