topic Performance issue with REGUP table data extraction in Application Development and Automation Discussions

Performance issue with REGUP table data extraction

Former Member — Mon, 26 Mar 2018 09:55:33 GMT

Hi All,

We have a requirement to display all the FI documents which are not in payment proposal (REGUP-XVORL <> 'X') based on selection screen parameters.

Currently in our production system the no. of entries in REGUP table are too large (crores of entries are present).

So when i try to get the entries from REGUP where BELNR as the FI document and XVORL as 'X'. Please note the no. of FI documents I am using are less than 10,000.

But to extract the data from REGUP table it is taking lot of time(sometimes it is even dumping timeout issue).

My query is:

1. Is there a way that can improve the performance of my select query to REGUP table.

2. Is there any other way/table by which i can check whether the FI document is under payment proposal or not.

Please share your inputs on this.

Thanks

Balaji.A

Re: Performance issue with REGUP table data extraction

RaymondGiuseppi — Mon, 26 Mar 2018 11:25:36 GMT

You surely noticed that REGUP is a Cluster Table, so actual keys are those of REGUC but XVORL is only 4th key, so not fulfilling your requirement and no index can be built on it, and any select without the key produce a whole table read... (Did you use ST05 to analyze SQL trace)

Could you consider using the 'Blocked Account Table' BWKS in your query, this table will provide a link between the account (vendor, customer, etc.) and the payment run. (Something like BSEG -> BWKS -(laufd+laufi) -> REGUH -> REGUP) Note that if a account is not in the table, it is not in a payment proposal, but if it's in the table it may or not be in a proposal as some document may be excluded (for example a document posted after the initial run)

NB:

Don't forget also BUKRS and GJAHR fields when using BELNR
Outside of India, the word crore may not be understood.

Re: Performance issue with REGUP table data extraction

Former Member — Mon, 26 Mar 2018 12:02:05 GMT

Hi Raymond,

Thanks a lot for your answer. However the table BWKS is not helping much, since I have an account in payment proposal but this account is not present in BWKS. (Not sure the reason for this.)

Currently I am passing below fields in my select query.

LAUFD - I got this from REGUH --> key field of REGUC

LAUFI - I got this from REGUH --> Key field of REGUC

XVORL - as 'X' --> Key field of REGUC

LIFNR --> Key field of REGUC

KUNNR --> Key field of REGUC

VBLNR--> Key field of REGUC

BUKRS --> Non key field of REGUC

BELNR --> Non key field of REGUC

GJAHR --> Non key field of REGUC

BUZEI --> Non key field of REGUC

But still it is taking lot of time to extract the data. Could the reason is due to passing the non key fields of REGUC in selection.

Is this the reason for the issue. Can i not pass the last 4 non key fields of REGUC in my select and fetch all the data using only the key fields.

Once I get the output then i will use only the relevant records.

Please share your feedback.

Re: Performance issue with REGUP table data extraction

pram_1991 — Mon, 26 Mar 2018 12:22:02 GMT

not use gjahr using BLDAT or BUDAT for where condition check month wise...

Re: Performance issue with REGUP table data extraction

RaymondGiuseppi — Mon, 26 Mar 2018 12:32:01 GMT

Sorry, blocking table is REGUS (I was working myself on BWKS, so copy/paste cross fired between F107 AND F110...)