07-31-2007 3:26 AM
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
07-31-2007 5:09 AM
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
07-31-2007 3:43 AM
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.
07-31-2007 4:02 AM
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.
07-31-2007 4:10 AM
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
07-31-2007 4:47 AM
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
07-31-2007 5:09 AM
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
08-01-2007 3:21 AM
I used the primary keys BUKRS KUNNR, and it solved to a maximum extent
Thanks to all