Performance issue - SELECT query and Logical DB

Former Member · ‎04-04-2012

Hello All,

We are having a Z program for computing the 401K contributions. This is one of the longest running program for over 12-14 hours. I traced this program using ST05 and found that there are certain SELECT queries which pull up data from the infotypes are very very time consuming. However these SELECT queries are not there in the Z program they are all coming from SAP standard program DBPNPF01. For example, the trace file shows that the system dies when pulling data from infotype 302, 375, 40, 16 at these statements -

SELECT

"MANDT" , "PERNR" , "SUBTY" , "OBJPS" , "SPRPS" , "ENDDA" , "BEGDA" , "SEQNR" , "AEDTM" ,

"UNAME" , "HISTO" , "ITXEX" , "REFEX" , "ORDEX" , "ITBLD" , "PREAS" , "FLAG1" , "FLAG2" ,

"FLAG3" , "FLAG4" , "RESE1" , "RESE2" , "GRPVL" , "OFFIC" , "OWNER" , "SEASN" , "OTHER" ,

"AFFDT" , "HICMP"

FROM

"PA0375"

WHERE

"MANDT" = :A0 AND "PERNR" = :A1 UNION ALL SELECT "MANDT" , "PERNR" , "SUBTY" , "OBJPS" , "SPRPS"

, "ENDDA" , "BEGDA" , "SEQNR" , "AEDTM" , "UNAME" , "HISTO" , "ITXEX" , "REFEX" , "ORDEX" ,

"ITBLD" , "PREAS" , "FLAG1" , "FLAG2" , "FLAG3" , "FLAG4" , "RESE1" , "RESE2" , "GRPVL" , "OFFIC"

, "OWNER" , "SEASN" , "OTHER" , "AFFDT" , "HICMP" FROM "PA0375" WHERE "MANDT" = :A2 AND "PERNR" =

:A3 UNION ALL SELECT "MANDT" , "PERNR" , "SUBTY" , "OBJPS" , "SPRPS" , "ENDDA" , "BEGDA" ,

"SEQNR" , "AEDTM" , "UNAME" , "HISTO" , "ITXEX" , "REFEX" , "ORDEX" , "ITBLD" , "PREAS" , "FLAG1"

, "FLAG2" , "FLAG3" , "FLAG4" , "RESE1" , "RESE2" , "GRPVL" , "OFFIC" , "OWNER" , "SEASN" ,

"OTHER" , "AFFDT" , "HICMP" FROM "PA0375" WHERE "MANDT" = :A4 AND "PERNR" = :A5 UNION ALL SELECT

"MANDT" , "PERNR" , "SUBTY" , "OBJPS" , "SPRPS" , "ENDDA" , "BEGDA" , "SEQNR" , "AEDTM" , "UNAME"

, "HISTO" , "ITXEX" , "REFEX" , "ORDEX" , "ITBLD" , "PREAS" , "FLAG1" , "FLAG2" , "FLAG3" ,

"FLAG4" , "RESE1" , "RESE2" , "GRPVL" , "OFFIC" , "OWNER" , "SEASN" , "OTHER" , "AFFDT" , "HICMP"

FROM "PA0375" WHERE "MANDT" = :A6 AND "PERNR" = :A7 UNION ALL SELECT "MANDT" , "PERNR" , "SUBTY"

, "OBJPS" , "SPRPS" , "ENDDA" , "BEGDA" , "SEQNR" , "AEDTM" , "UNAME" , "HISTO" , "ITXEX" ,

"REFEX" , "ORDEX" , "ITBLD" , "PREAS" , "FLAG1" , "FLAG2" , "FLAG3" , "FLAG4" , "RESE1" , "RESE2"

, "GRPVL" , "OFFIC" , "OWNER" , "SEASN" , "OTHER" , "AFFDT" , "HICMP" FROM "PA0375" WHERE "MANDT"

= :A8 AND "PERNR" = :A9

Now when I check the "Display call position in the ABAP program" for these Select queries in ST05, it shows that it comes from SAP standard program DBPNPF01 -

    SELECT * FROM (DBNAME)
          INTO CORRESPONDING FIELDS OF <INFTY_RECORD_WA>
               FOR ALL ENTRIES IN PERNR_TAB
               WHERE PERNR = PERNR_TAB-PERNR
               ORDER BY PRIMARY KEY.

Can anybody please assist, how can I improve performance of this program since it is a SAP standard program for pulling data for logical databases.

Appreciate all your help truly !!!

Thanks and Regards,

Samta.

Former Member · ‎04-04-2012

Have you doe a runtime analysis to see how often the standard SAP report is called?

Rob

Former Member · ‎04-04-2012

Hey Rob,

Thanks for the prompt response. Really appreciate it.

Yeah its called for all infotypes referred in the program. Looking at the trace I would say it appears N no. of times - with very high response times for all infotypes 0001, 0019, 0014, 0302, etc.

10,319 PA0014 FETCH 140 0

8,458 PA0014 FETCH 89 1403

8 PA0014 REOPEN 0 SELECT WHERE "MANDT" = '020' AND "PERNR" = 00017419 UNION ALL SELECT "MANDT" ,

15,271 PA0014 FETCH 140 0

10,988 PA0014 FETCH 140 0

1,968 PA0014 FETCH 140 0

7,343 PA0014 FETCH 101 1403

9 PA0014 REOPEN 0 SELECT WHERE "MANDT" = '020' AND "PERNR" = 00017424 UNION ALL SELECT "MANDT" ,

516 PA0014 FETCH 4 1403

4 PA0014 REOPEN 0 SELECT WHERE "MANDT" = '020' AND "PERNR" = 00017429 UNION ALL SELECT "MANDT" ,

10,866 PA0014 FETCH 75 1403

9 PA0014 REOPEN 0 SELECT WHERE "MANDT" = '020' AND "PERNR" = 00017434 UNION ALL SELECT "MANDT" ,

1,015 PA0014 FETCH 17 1403

5 PA0014 REOPEN 0 SELECT WHERE "MANDT" = '020' AND "PERNR" = 00017439 UNION ALL SELECT "MANDT" ,

787 PA0014 FETCH 23 1403

4 PA0014 REOPEN 0 SELECT WHERE "MANDT" = '020' AND "PERNR" = 00017444 UNION ALL SELECT "MANDT" ,

12,876 PA0014 FETCH 140 0

1,919 PA0014 FETCH 140 0

17,489 PA0019 FETCH 52 1403

11 PA0019 REOPEN 0 SELECT WHERE "MANDT" = '020' AND "PERNR" = 00017384 UNION ALL SELECT "MAND

2,616 PA0019 FETCH 51 1403

5 PA0019 REOPEN 0 SELECT WHERE "MANDT" = '020' AND "PERNR" = 00017389 UNION ALL SELECT "MAND

6,824 PA0019 FETCH 27 1403

9 PA0019 REOPEN 0 SELECT WHERE "MANDT" = '020' AND "PERNR" = 00017394 UNION ALL SELECT "MAND

3,245 PA0019 FETCH 52 1403

8 PA0019 REOPEN 0 SELECT WHERE "MANDT" = '020' AND "PERNR" = 00017399 UNION ALL SELECT "MAND

1,026 PA0019 FETCH 34 1403

4 PA0019 REOPEN 0 SELECT WHERE "MANDT" = '020' AND "PERNR" = 00017404 UNION ALL SELECT "MAND

11,755 PA0019 FETCH 66 1403

10 PA0019 REOPEN 0 SELECT WHERE "MANDT" = '020' AND "PERNR" = 00017409 UNION ALL SELECT "MAND

10,422 PA0019 FETCH 35 1403

12 PA0019 REOPEN 0 SELECT WHERE "MANDT" = '020' AND "PERNR" = 00017414 UNION ALL SELECT "MAND

35,725 PA0019 FETCH 84 1403

10 PA0019 REOPEN 0 SELECT WHERE "MANDT" = '020' AND "PERNR" = 00017419 UNION ALL SELECT "MAND

22,532 PA0019 FETCH 100 1403

11 PA0019 REOPEN 0 SELECT WHERE "MANDT" = '020' AND "PERNR" = 00017424 UNION ALL SELECT "MAND

10,806 PA0019 FETCH 36 1403

8 PA0019 REOPEN 0 SELECT WHERE "MANDT" = '020' AND "PERNR" = 00017429 UNION ALL SELECT "MAND

11,687 PA0019 FETCH 42 1403

9 PA0019 REOPEN 0 SELECT WHERE "MANDT" = '020' AND "PERNR" = 00017434 UNION ALL SELECT "MAND

1,789 PA0019 FETCH 41 1403

Thanks and Regards,

Samta.

Former Member · ‎04-04-2012

A Z program calling a logical database program N times sounds like trouble.

I would re-design the program to do the work internally using SELECTs.

Rob

yuri_ziryukin · ‎04-05-2012

Hello Samta,

looking at the SQL trace I notice that quite many records are read.

Example:

15,271 PA0014 FETCH 140 0

10,988 PA0014 FETCH 140 0

1,968 PA0014 FETCH 140 0

7,343 PA0014 FETCH 101 1403

If I understand correctly, the first number after FETCH is the record count and the second number is the return code.

So, your database managed to fetch 521 records in 35,57 milliseconds. It corresponds to 68 MICROSECONDS per record which is very fast.

So, technically there is no optimization potential.

If you see that fetching this data takes too long then it should be releated to the number of records to be fetched. So you are using logical database to read infotype information for too many PERNR.

Please make sure in your program that you are NOT reading this information for the same employee (PERNR) many times. If this is the case, try to implement internal buffering of the selected information in your Z report. Means that before you request information from LDB, try to read from the internal table. Only if not found - read from LDB and save this information into your internal table for the next access.

Regards,

Yuri

Former Member · ‎04-09-2012

Hey Rob,

Thanks for your prompt response. But wont doing the same thing with SELECT queries result in same query performance time ? Can something be done at indexing level on tables ?

Regards,

Samta.

Former Member · ‎04-09-2012

Yes - doing the same thing would be inefficient. I'm suggesting that the current process is itself inefficient and could be re-designed (not-re-written).

Rob

Former Member · ‎04-09-2012

Hey Yuri,

Thanks for your prompt response. Yeah this definitely helps me drill down the problem.

I dont think we are reeading the same PERNR multiple times.

The program code looks like this -

START-OF-SELECTION.
PERFORM DETERMINE_COMPANY_CODE TABLES   PNPBUKRS
                                 USING    C_CODE
                                 CHANGING V_CCODE1.
PERFORM INITIALIZE_TABLE.                                 "SFPSR2257

GET PERNR.
IF S_TAXAU IS NOT INITIAL.
RP-PROVIDE-FROM-LAST P0209 SPACE PNPBEGDA PNPENDDA.       " it209
CHECK NOT P0209-TAXAU IN S_TAXAU.
ENDIF.
PERFORM PROCESS_SELECTION.
PERFORM PROCESS_DATA USING V_CCODE1.

PERFORM UPDATE-AUDIT-TABLE.

PERFORM REFRESH_TABLES.

Coming to the second part - How can we buffer data from so many infotypes. Is it feasible or will it have some other performance DB issues. Is there anything particular that you are pointing to when you refer to internal buffering. Sorry but I didnt quite understand this one.

The program is pulling up data from these infotypes -

INFOTYPES : 0000,
0001,0002, 0003, 0006, 0014, 0015, 0019, 0041, 0094,0169,0209,0236, 0302, 0375.

Please let me know incase I have missed on something.

Thanks and truly appreciate all your help.

Regards,

Samta.

Former Member · ‎04-09-2012

Hey Rob,

Thanks for your prompt response as always.

Doesn't SAP suggests to use LDB and GET PERNR. From what I understand it comes with additional security authorization checks and pulls all infotypes data at once. How can this part be re-designed. Is there something at indexing level that can be done to reduce high response times of intermittent SELECT statements that GET PERNR generates.

Also from the trace I saw that the PCL2 fetch from these includes are heavy on response times too. These are all SAP standard programs.

   INCLUDE: RPC2CD09,                     " data declaration for key
         RPC2RUU0,                     " data declaration for results
         RPC2RX09,               " data declaration for cluster data
         RPPPXD00,
         RPPPXD10,
         RPPPXM00.

START-OF-SELECTION.
PERFORM DETERMINE_COMPANY_CODE TABLES   PNPBUKRS
                                 USING    C_CODE
                                 CHANGING V_CCODE1.
PERFORM INITIALIZE_TABLE.

GET PERNR.
IF S_TAXAU IS NOT INITIAL.
RP-PROVIDE-FROM-LAST P0209 SPACE PNPBEGDA PNPENDDA.       " it209
CHECK NOT P0209-TAXAU IN S_TAXAU.
ENDIF.
PERFORM PROCESS_SELECTION.          " check and process the selection
PERFORM PROCESS_DATA USING V_CCODE1." extract data and process
PERFORM UPDATE-AUDIT-TABLE.         " update audit table for reporting
PERFORM REFRESH_TABLES.             " refresh tables to process next

************************************************************************
*end-of-selection                                                      *
************************************************************************
END-OF-SELECTION.
CHECK V_TOTALEMP GT 0.               " if there are any records
PERFORM CONVERT_DATA_SINGLE_TABLE.   " convert data into one int table
PERFORM DOWN_LOAD_TO_FILE.           " Down load file to Unix
PERFORM PRINT_PROG_SUMMARY.          " print the summary report
PERFORM PRINT_EXTRACT_REPORT.        " print the audit report
PERFORM PRINT_COMPANY_CODE_REPORT.   " print the cc summary totals

Please assist.

Thanks and Regards,

Samta.

Former Member · ‎04-09-2012

think about this, if you are executing this program for all the PERNRS in your system every time, could this work be broken into smaller pieces? We have learned that for our Mass HR Update programs the best thing to do is to break the number of PERNRS into small chunks and create multiple jobs that will run at the same time.

I have written a Splitter program that reads HR Master data selecting the records that control what needs updating and loading the data needed for the Selection Screen of the update program into an Internal Table. it then reads this Internal table and builds the Selection Screen of the Update program keeping a count of the number of PERNRS read, when the count reaches the number of records defined to make up a job I then submit a job with my update program as the step, reset my selection screen and counters and go back and read the next record from the table. We keep this up until we reach the end of the internal table. We used to have an annual job that would run for 24 hours processing every PERNR in one job, when we went to this method we now are done with all records in just a couple of hours.

yuri_ziryukin · ‎04-10-2012

Hello Samta,

if you are sure that no duplicate requests for the same PERNR are taking place, then you can forget about buffering. It will not help. As the next step for the analysis I'd try to calculate time per PERNR that your report takes. We call it "normalization per business object". It will give you an idea if your report is more or less optimal or not. If the response time is less then 30-20 ms per employee (PERNR), then you may have hard time further optimizing your selections. In that case please refer to another reply above this one where you are advised to split the selection and implement parallel processing.

Regards,

Yuri