topic Re: Selecting data from BSEG. in Application Development and Automation Discussions

Selecting data from BSEG.

Former Member — Sat, 24 Feb 2007 11:54:50 GMT

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?

Re: Selecting data from BSEG.

Former Member — Sat, 24 Feb 2007 12:58:53 GMT

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.

Re: Selecting data from BSEG.

raviprakash — Sat, 24 Feb 2007 20:01:45 GMT

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.

Re: Selecting data from BSEG.

RaymondGiuseppi — Sat, 24 Feb 2007 20:37:00 GMT

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.

Re: Selecting data from BSEG.

Former Member — Mon, 26 Feb 2007 12:39:30 GMT

for bseg use all the primary key in the where clause to improve the performance.

reward this with points.

Re: Selecting data from BSEG.

Former Member — Mon, 26 Feb 2007 12:56:28 GMT

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,