07-31-2007 3:31 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 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
07-31-2007 7:26 AM
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
07-31-2007 4:37 AM
hi,
try to use inner join between the tables bsad and bsid.
regards,
Navneeth K.
07-31-2007 7:13 AM
Can any body please answer???
Points for sure ..
07-31-2007 7:26 AM
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
07-31-2007 10:32 AM
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
07-31-2007 2:04 PM
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
08-01-2007 3:20 AM
I used the primary keys BUKRS KUNNR and it solved to a maximum extent
Thanks to all