topic Re: Performnace Optimisation Issue while fetching data from BSEG table in Application Development and Automation Discussions

Performnace Optimisation Issue while fetching data from BSEG table

Former Member — Wed, 17 Nov 2010 05:04:38 GMT

SELECT-OPTIONS : so_bukrs FOR bseg-bukrs NO INTERVALS,

so_lifnr FOR bseg-belnr NO INTERVALS,

so_hkont FOR bseg-hkont,

so_budat FOR bkpf-budat.

START-OF-SELECTION.

*Data Selection for BSEG

SELECT lifnr hkont belnr gsber sgtxt shkzg pswbt from BSEG INTO

CORRESPONDING FIELDS OF TABLE it_bseg

WHERE bukrs IN so_bukrs AND

lifnr IN so_lifnr AND

hkont IN so_hkont.

for a test data , it produces the following output

200000 117000 100000001 2 S 1.000,00

200000 117000 1900000000 2 H 1.000,00

200000 117000 1900000001 2 H 1.620,00

200000 117000 1900000002 2 H 830,00

200000 118010 5000000000 H 100,00

200000 118010 5000000001 H 500,00

200000 118010 5000000002 H 300,00

200000 118010 5000000003 2 H 500,00

200000 118010 5000000004 2 H 10.193,36

200000 118010 5000000005 H 1.000,00

200000 118010 5000000006 S 1.000,00 select query1 successful

while fecthing fields from BSEG table, it takes a long time to display the records. I replaced it with the following code

select bukrs lifnr hkont

from bsak

into corresponding fields of table it_bsak

where budat in so_budat.

*if sy-subrc = 0.

*loop at it_bsak into wa_bsak.

*write: / wa_bsak-bukrs.

*endloop.

*endif.

select belnr gsber sgtxt shkzg pswbt from bseg into corresponding fields of table it_bseg

for all entries in it_bsak

where lifnr = it_bsak-lifnr and

bukrs = it_bsak-bukrs and

hkont = it_bsak-hkont.

however, i am not getting the same output using the above code.

for the same test data, the output i get is as follows.

200000 117000 100000001 2 S 1.000,00

200000 117000 1900000000 2 H 1.000,00

200000 117000 1900000001 2 H 1.620,00

200000 117000 1900000002 2 H 830,00

200000 117000 100000001 2 S 1.000,00

200000 117000 1900000000 2 H 1.000,00

200000 117000 1900000001 2 H 1.620,00

200000 117000 1900000002 2 H 830,00

The values get doubled. Please Correct me if i am using the wrong select statement. Awaiting your suggestions.

Thanks in advance,

Basil

Re: Performnace Optimisation Issue while fetching data from BSEG table

Former Member — Wed, 17 Nov 2010 06:20:29 GMT

Hi,

Since BSEG is a cluster table you need to select all the primary key fields even if all the primary key values are not required in your case, along with the other required fields. So here you have to modify the SELECT statement like shown below.

Also try avoiding INTO CORRESPONDING FIELDS OF which will also cause a performance issue.

SELECT BUKRS BELNR GJAHR BUZEI  LIFNR HKONT GSBER SGTXT SHKZG PSWBT 
FROM BSEG
INTO TABLE IT_BSEG
WHERE BUKRS IN SO_BUKRS AND
                LIFNR IN SO_LIFNR AND
                HKONT IN SO_HKONT.

Re: Performnace Optimisation Issue while fetching data from BSEG table

Former Member — Wed, 17 Nov 2010 07:23:00 GMT

Hi,

I am getting the same output now, but my doubt is where does the role of Index table BSAK come into play. If i try to fetch data

using BSAK table and then use FOR ALL ENTRIES statement , is there any possiblity of getting the same output.

I am also posting the code used to fetch data from BSAK table.

select bukrs lifnr hkont

from bsak

into corresponding fields of table it_bsak

where budat in so_budat.

if sy-subrc = 0.

loop at it_bsak into wa_bsak.

write: / wa_bsak-bukrs.

endloop.

endif.

select belnr gsber sgtxt shkzg pswbt from bseg into corresponding fields of table it_bseg

for all entries in it_bsak

where lifnr = it_bsak-lifnr and

bukrs = it_bsak-bukrs and

hkont = it_bsak-hkont.

Thanks again,

Basil

Re: Performnace Optimisation Issue while fetching data from BSEG table

Former Member — Wed, 17 Nov 2010 14:45:34 GMT

When I add to use a mass selection in BSEG I managed to first select data from BKPF ( some secondary indexes are quite interesting ) and then select the items in BSEG using FOR ALL ENTRIES with records fetched from BKPF. You can then specify the whole records primary key.

I used this technic in daily interfaces checking that FI documents were not yet created ( for instance to avoid a second use of the same input file ).

Regards

Re: Performnace Optimisation Issue while fetching data from BSEG table

Former Member — Wed, 17 Nov 2010 17:59:51 GMT

In the data selection criteria, if Fiscal Year (GJAHR) is to be included alongwith

Document Number (BELNR) to resolve the current problem of same document number in different fiscal

years not getting selected but only the first yearu2019s document getting selected. Subsequent yearu2019s documents with the

same number are not selected.

select bukrs lifnr hkont

from bsak

into corresponding fields of table it_bsak

where budat in so_budat.

if sy-subrc = 0.

loop at it_bsak into wa_bsak.

write: / wa_bsak-bukrs.

endloop.

endif.

select belnr gsber sgtxt shkzg pswbt from bseg into corresponding fields of table it_bseg

for all entries in it_bsak

where lifnr = it_bsak-lifnr and

bukrs = it_bsak-bukrs.

Please suggest the query.

Thanks

hkont = it_bsak-hkont.

Re: Performnace Optimisation Issue while fetching data from BSEG table

Former Member — Wed, 17 Nov 2010 18:23:07 GMT

BSEG table has all accounting item, BSAK table has the cleared item only, so probably the open items are missing: BSIK tables.

BSAK/BSIK table have all information you need, so I think it's useless to do a selection in BSEG, probably you can display the result of selection from BSIK and BSAK

Max