performance issue for rseg table

[GauthamV]

hi,

we have an fi report related to inter state tax calculation.

the report is woking fine.

problem is here we r selecting data from rseg based on mseg

but we cannot use any primary key combination.so the database is taking long time to retrive data.

i checked in both se30 and st05.the below statement is taking more time to retrive data from database.

IF NOT it_mseg1[] IS INITIAL.

SELECT belnr gjahr buzei lfbnr lfgja lfpos ebeln ebelp FROM rseg

INTO TABLE it_rseg

FOR ALL ENTRIES IN it_mseg1

WHERE lfbnr = it_mseg1-mblnr AND

lfgja = it_mseg1-mjahr AND

lfpos = it_mseg1-zeile.

ENDIF.

can any one suggest in improving the performance .

[GauthamV]

hi,

we have an fi report related to inter state tax calculation.

the report is woking fine.

problem is here we r selecting data from rseg based on mseg

but we cannot use any primary key combination.so the database is taking long time to retrive data.

i checked in both se30 and st05.the below statement is taking more time to retrive data from database.

IF NOT it_mseg1[] IS INITIAL.

SELECT belnr gjahr buzei lfbnr lfgja lfpos ebeln ebelp FROM rseg

INTO TABLE it_rseg

FOR ALL ENTRIES IN it_mseg1

WHERE lfbnr = it_mseg1-mblnr AND

lfgja = it_mseg1-mjahr AND

lfpos = it_mseg1-zeile.

ENDIF.

can any one suggest in improving the performance .

[Former Member]

Hi,

I had created secondary index for similar problem on Rseg and it gave me very good performance.

Regards,

Mohaiyuddin

[GauthamV]

hi,

thanks for the reply.

is there any other option other than secondary index?

[Former Member]

Hello

No, only secondary index use ....

[Former Member]

If EKBE has secondary index available on LFBNR, LFPOS and LFGJA :

Use EKBE with VBAGE = 2 and BELNR, GJAHR and BUZEI will represent Invoice number, fiscal year and line item number.

You can query RSEG with these key fields.

Note : Generally, it is better check for sec index on EKBE because it give purchasing document history and used frequently, hence there are more chances to find sec index on it.

*If there is 1-1 relationship between your PO line item and Invoice line item.*

• Retrieve EBELN and EBELP from EKBE and query on RSEG

In EKBE give VGABE = 1 (to represent GR), BELNR = it_mseg-MBLNR, GJAHR = it_mseg-MJAHR and BUZEI = it_mseg-ZEILE.

• Query again on EKBE with VGABE = 2 and using EBELN and EBELP from above query. This time BELNR, GJAHR and BUZEI will represent Invoice number, fiscal year and line item number.

• Combine above 2 internal table. (if you want to maintain relationship between material doc and invoice in your program)

Note : Assumption is PO line item and Invoice line item has 1-1 relationship.

• You can use above BELNR, GJAHR and BUZEI to query on RSEG.

I think you would have taken below points already in account :

1. it_mseg1 should have unique combination of mblnr, mjahr and zeile. (if not you can transfer the content to another internal table and use sort and delete adjacent duplicates)

2. Sort it_mseg1 by mblnr, mjahr, zeile.

Regards,

Mohaiyuddin