Leave request approval - insert on PTREQ_HEADER is very slow

[Former Member]

Hi experts,

We are experiencing a major issue on the performance of leave request approval. The approval step is taking more than 1 minute per request!

We already did a trace on production system (see attachment) to find out the reason for this awful performance and we found out that an SQL statement to insert a new entry on table PTREQ_HEADER is taking 67 seconds.

We analyzed this table in detail and it has more than 1,3 million entries. Please find enclosed all details about this trace.

Do you recommend to delete old requests already approved using the standard programs RPTARQDBDEL etc?

We are about to upgrade to EHP5 using the new WD for requesting and the new inbox WD application (ibo_wda_inbox) available with MSS Addon 1.0,

but I guess the performance will not get any better as it will use the same coding. Is that so?

Thanks in advance.

Cheers,

AS

[Former Member · Accepted solution]

Dear all,

I've just found out how to fix it. I deactivated standard BADi osp_pt_gen_req_inter and it works perfectly. It seems very strange but according to sap note 1565933 you should deactivate it if you're not using Duet solution (no idea what that is).

Many thanks for your hints it helped a lot on other performance issues (like team calendar).

Best Regards,

AS

[abhinav_pattenwal]

Hi All,

Is there any report to change the status of request in table ptreq_header with 'SENT' status at month end. Plz suggest if any.

Thanks in advance.

[Former Member]

Yes there are about 60 pernr's with error. And for these pernr's with error's  the table attabs has over 1 mill entries with numerous duplicates only on the dates for which the error happened.  Shd it be approved/reject in the rptarqerr report or wud it be fine just deleting these error records.

[siddharthrajora]

just change the status using the above report and check if you are using any modification which probably causes this

advise your EA HR level as well?

For clearing leave request DB, you can use rptarqdbdel

[Former Member]

Deleting error requests stops it from multiplying in attabsdata but the already existing dual records which it created are still present in db table. Please advice if there's a way to clear attabs records.

EA HR 606 SAPK-60622INEAHR

[siddharthrajora]

SO you are missing this note 1771815. apply it first

we released note 1741216 to identify inconsistency in the system, Please can you check it

The report will simply delete everthing from

PTREQ_ITEMS & PTREQ_ATTABSDATA where there is no related data in

PTREQ_HEADER.

*&---------------------------------------------------------------------*
*& Report  RPT
*&---------------------------------------------------------------------*
REPORT rpt.

DATA ptreq_attabsdata_wa   TYPE ptreq_attabsdata.
DATA ptreq_items_wa        TYPE ptreq_items.
DATA ptreq_header_wa       TYPE ptreq_header.

DATA delete_tab            TYPE TABLE OF ptreq_attabsdata-item_id.
DATA delete_wa             TYPE ptreq_attabsdata-item_id.

DATA pernr_wa TYPE pernr.
DATA pernr TYPE pernr.
DATA pernr_tab TYPE ptim_pernr_tab.

DATA count TYPE i.

SELECTION-SCREEN BEGIN OF BLOCK block WITH FRAME TITLE text-001.
SELECT-OPTIONS: per_nr FOR pernr_wa-pernr NO INTERVALS.
PARAMETERS: test_run AS CHECKBOX DEFAULT 'X'.
SELECTION-SCREEN END OF BLOCK block.

START-OF-SELECTION.
  IF lines( per_nr ) > 0.
    SELECT pernr FROM pa0003 INTO TABLE pernr_tab WHERE pernr IN per_nr.
    SORT pernr_tab.

    LOOP AT pernr_tab INTO pernr.
      SELECT * FROM ptreq_attabsdata INTO ptreq_attabsdata_wa WHERE pernr = pernr .
        SELECT * FROM ptreq_items INTO ptreq_items_wa WHERE item_ins = ptreq_attabsdata_wa-item_id.
          SELECT * FROM ptreq_header INTO ptreq_header_wa WHERE item_list_id = ptreq_items_wa-item_list_id .
          ENDSELECT.
          IF sy-subrc <> 0.
            APPEND ptreq_attabsdata_wa-item_id TO delete_tab.
          ENDIF.
        ENDSELECT.
      ENDSELECT.
    ENDLOOP.

    LOOP AT delete_tab INTO delete_wa.
      IF test_run <> 'X'.
        DELETE FROM ptreq_items WHERE item_ins = delete_wa.
        DELETE FROM ptreq_attabsdata WHERE item_id = delete_wa.
      ENDIF.
    ENDLOOP.
  ENDIF.
  count = lines( delete_tab ).
  WRITE: 'Deleted:', count.

Hope this helps, Please test in your test system first, cause deletion is always tricky, take a backup and a extreme precaustion in doing in production

[Former Member]

Yep this custom report is awsum , it fixed the DB tables real gud . The note is already in i hv checked the code for it's corrections.

Also i see the report in 1741216 is different from the code you just mentioned. Would the report in 1741216 just identify inconsistencies or wud it fix em as well.

[siddharthrajora]

above report deletes also, the one in note I guess identifies

Please can you check again?

[Former Member]

the report for analyzing db table didn't identifying error's that i'm facing although it's good to have it  up our sleeves.

[Former Member]

I am facing the same issue but on table ptreq_attabsdata and it's count went up over a million within a month of applying the hrsp's .Basically i see one del operations repeated over 100K in it. Can this badi be causing it.  Is there a way to clean this table and stop it from multiplying.

[siddharthrajora]

why there are duplicates? Are you using anything custom to post to IT2001?

- employee creates a leave

- manageer approves the leave

- post report post the leave >>> rptarqpost is being used for this.

check any error records using RPTarqerr report

[Lukas_Weigelt]

Hi André,

In my opinion, all you have stated is correct. The WDYNs ultimately all share the same business logic so the migration to WDA won't change anything concerning Database-Performance. Also, I personally know no way other than deleting entries via RPTARQDBDEL to handle the masses of the leave receipt database. As far as I know, there is no archiving-functionality or the like.

So in my opinion, use RPTARQDBDEL for entries you definitely don't need anymore (check how long you need them for audit purposes too), but run in test-mode beforehand.

I also recommend waiting for a response from or another Mod here, since he/they should have the latest information on this (maybe I'm outdated and there is an archiving-function nowadays).

Cheers, Lukas

[Former Member]

Hi Lukas,

Thanks for your feedback. I also read SAP note 1600991 and I guess I can use archiving object HRTIM_REQ to destroy / archive requests already posted, rejected or withdrawn from DB as explained below.

https://websmp202.sap-ag.de/~sapidb/012003146900000448542011E/HR_Archiving_EA-HR_SAP-HR_EN.pdf

I've never used Information Lifecycle Management for archiving... do you know how this works? If I use RPT_REQUEST_WRITE I can store these requests in a specific archiving directory on the server for audit purposes if necessary? Or are they going to be deleted forever?

Many thanks in advance.

Best Regards,

AS

[siddharthrajora]

We have released a note  1559133 on how to make it work and its functionality

Firstly,if  There are still records in Status APPROVED, SENT.
Without clearing this we dont expect much improvement in performance
The final status should be POSTED. ie old request is not recent should be cleared, if there are records in unproved status in tables then simulation is done which takes time, if you put the pending old requests in posted or deleted status, performance will improve, we don't recommend to have more than 3 to 6 months old request in Worklist for the manager for approval

in the SAP standard in the method GET_FUTURE_RECORDS of class
CL_PT_BLP_ITATTABS by replacing one line in the source code as
follows to avoid reading in all infotype records up to HIGHDATE:

*selto   = cl_pt_tmw_const=>high_date.       "<   DELETE
selto   = irec-endda.                        "<   INSERT

Also to improve performance, we recently released new notes etc

You should implement this first and then check again

1839544 and 1850185

The DB indices for table PTQUODED have been updated by adding the field

MANDT (client) at the first position for all three indices DOC, PER, and

QNR to improve access performance. To this end, please implement notes

1721247, 1822392, and 1833463.

1661953  - WDA LEA: TeamCalendar and EmployeeCal Performance issue

1533061  - How to analyze performance issues in team calendar in ESS/MSS

[Lukas_Weigelt]

Hi André,

to be blunt, I have no idea whatsoever how this archiving works in depth; I didn't even know this possibility exists before you mentioned it . I got curious and took a look on the documentation of Report RPT_REQUEST_WRITE, there it says you can view the archived data with the reports RPTARQDBVIEW and RPTCORDBVIEW; so apparently you do have audit possibilities and the data is not destroyed (if you choose so). Still, looking at all these (to me) unknown terminology like "Lifecycle Management", "ILM", "Information Retention Manager" it seems there is quite some work to do before you can use this archiving approach . Anyway, this does look like a promising alternative to the normal deletion report to me.

Cheers, Lukas

P.S.: @ Siddharth, nice Information bundle for Performance tuning! That will come in handy for me as well