02-24-2007 11:54 AM
Dear Friends,
I have written one query to select data from BSEG table, but it is taking too much time to execute (40 sec per record) . I want to improve the performance of it. Please suggest the solutions. Query is below.
SELECT * FROM bseg INTO CORRESPONDING FIELDS OF wa_itab
WHERE matnr IN matnr
AND werks IN werks
AND bschl IN ('99' , '89' , '86' , '96')
AND ( ktosl IN ('BSX' , 'PRD')
OR sgtxt = 'Debit/credit to a material from a price change' ).
SELECT SINGLE cpudt cputm budat blart bldat AWKEY FROM bkpf INTO CORRESPONDING FIELDS OF wa_itab
WHERE blart IN ('RE' , 'PR')
AND belnr = wa_itab-belnr.
wa_itab-pperiod = wa_itab-budat(6).
APPEND wa_itab TO itab.
CLEAR wa_itab.
ENDSELECT.
Should I create secondory index on bseg or something else?
02-24-2007 12:58 PM
remove the nested selects..
SELECT * FROM bseg INTO CORRESPONDING FIELDS OF table itab
WHERE matnr IN matnr
AND werks IN werks
AND bschl IN ('99' , '89' , '86' , '96')
AND ( ktosl IN ('BSX' , 'PRD')
OR sgtxt = 'Debit/credit to a material from a price change' ).
loop at itab into wa_itab.
SELECT SINGLE cpudt cputm budat blart bldat AWKEY FROM bkpf INTO CORRESPONDING FIELDS OF wa_itab
WHERE blart IN ('RE' , 'PR')
AND belnr = wa_itab-belnr.
modify itab from wa_itab.
clear wa_itab.
endloop.
02-24-2007 8:01 PM
Hi Madan,
Is there any typo in the code you have written here. Because both the SELECTS are filling the same work area WA_ITAB..
Please reply,
Thanks and regards,
Ravi.
02-24-2007 8:37 PM
BSEG is a cluster table, namely RFBLG. you won't be able to create index on such a table.
And access with fields out of the tkeys ikf RFBLG BUKRS/BELNR/GJAHR/BUZEI risk to read the whole data table mapping the fields from cluster data.
(Look at http://help.sap.com/saphelp_nw04/helpdata/en/cf/21f083446011d189700000e8322d00/content.htm for miore info on cluster table)
Hopefully there are "database indexes" on BSEG, which are actual database table : BSIS, BSAS, BSID, BSAS, and so on.
BSEG Access
BSAD Accounting: Secondary index for customers (cleared items)
BSAK Accounting: Secondary index for vendors (cleared items)
BSAS Accounting: Secondary index for G/L accounts (cleared items)
BSID Accounting: Secondary index for customers
BSIK Accounting: Secondary index for vendors
BSIM Secondary Index, Documents for Material
BSIS Accounting: Secondary index for G/L accounts
In your case, as you need material posts, use BSIM.
If some fields are missing you can then use a SELECT FOR ALL ENTRIES FROM BSEG/BKPF with the keys found in BSIM.
Regards.
Regards.
02-26-2007 12:39 PM
for bseg use all the primary key in the where clause to improve the performance.
reward this with points.
02-26-2007 12:56 PM
1) In the above code avoid using SELECT...ENDSELECT (nested Select), instead use INTO TABLE
2) If possible change the order of fields in wa_itab and avoid using INTO CORRESPONDING FIELDS OF in the select statement.
3) BSEG is a table with huge number of fields. Donot use SELECT * ......, Select only required fields or columns
4) Bring the fetch for table BKPF outside the loop using SELECT ..... FOR ALL ENTRIES
5) If possible, try to use secondary index for table BKPF if available.
Regards,
KK