Issue with Performance -

[Former Member]

Hello All,

We have a Z program for computing the 401K contributions of the employees. The problem with this program is it runs for 10-12 hours almost.

We ran a trace for this program and found that there are  certain SQL statements which are taking most of the time. But all these SQL statements appear in the standard SAP programs and not the Z program that we have written. For example - This is the SELECT that shows long running DB access times in ST05. This is not a SELECT query in the Z program but appears from the SAP program DBPNPF01 for accessing logical database -

Since this is not a Z program, has anybody seen this issue in the past. If yes, what could be the solution to this issue.

Program DBPNPF01 -

ABAP statement -

    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.

Corresponding translation to fetch data from infotype -

Appreciate all your help truly !!!

Regards,
Samta.

SELECT

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

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

"FLAG3" , "FLAG4" , "RESE1" , "RESE2" , "GRPVL" , "DAR01" , "DAT01" , "DAR02" , "DAT02" ,

"DAR03" , "DAT03" , "DAR04" , "DAT04" , "DAR05" , "DAT05" , "DAR06" , "DAT06" , "DAR07" ,

"DAT07" , "DAR08" , "DAT08" , "DAR09" , "DAT09" , "DAR10" , "DAT10" , "DAR11" , "DAT11" ,

"DAR12" , "DAT12"

FROM

"PA0041"

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" , "DAR01"

, "DAT01" , "DAR02" , "DAT02" , "DAR03" , "DAT03" , "DAR04" , "DAT04" , "DAR05" , "DAT05" ,

"DAR06" , "DAT06" , "DAR07" , "DAT07" , "DAR08" , "DAT08" , "DAR09" , "DAT09" , "DAR10" , "DAT10"

, "DAR11" , "DAT11" , "DAR12" , "DAT12" FROM "PA0041" 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" , "DAR01" , "DAT01" , "DAR02" , "DAT02" ,

"DAR03" , "DAT03" , "DAR04" , "DAT04" , "DAR05" , "DAT05" , "DAR06" , "DAT06" , "DAR07" , "DAT07"

, "DAR08" , "DAT08" , "DAR09" , "DAT09" , "DAR10" , "DAT10" , "DAR11" , "DAT11" , "DAR12" ,

"DAT12" FROM "PA0041" 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" , "DAR01" , "DAT01" , "DAR02" , "DAT02" , "DAR03" , "DAT03" , "DAR04" , "DAT04"

, "DAR05" , "DAT05" , "DAR06" , "DAT06" , "DAR07" , "DAT07" , "DAR08" , "DAT08" , "DAR09" ,

"DAT09" , "DAR10" , "DAT10" , "DAR11" , "DAT11" , "DAR12" , "DAT12" FROM "PA0041" 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" , "DAR01" , "DAT01"

, "DAR02" , "DAT02" , "DAR03" , "DAT03" , "DAR04" , "DAT04" , "DAR05" , "DAT05" , "DAR06" ,

"DAT06" , "DAR07" , "DAT07" , "DAR08" , "DAT08" , "DAR09" , "DAT09" , "DAR10" , "DAT10" , "DAR11"

, "DAT11" , "DAR12" , "DAT12" FROM "PA0041" WHERE "MANDT" = :A8 AND "PERNR" = :A9

[Former Member · Accepted solution]

Hi,

I will recommend to contact SAP on this, since it was standard they must have the logic  for it  why it was running for long time.

May be you can try with more indexes on where clause, but as given above there are lot of fields..not sure if that works..

Run the stats daily for the  tables manually using scripts  and try to run in the apps which are good in SAPS and dedicate a group for the run . Run the program during non-business time. Do not assign lot of apps servers for the run as wore wp hitting DB there will be lot of pressure on DB it can result in slow processing too. Try to make trail and error on decresing the apps for this program run.

Thanks,

Subhash.G

[former_member197561]

Dear Samta,

If the performance issue is narrow down to SAP code, then there are 2 ways to resolve:

- if possible avoid calls to the function which causes the problem, for example by using cached result or so on

- open OSS ticket to SAP, requesting a correction

With the concrete statement (select for all entries and also order by) the issue could be due to too many records of data retrieved from the table and time spend on sorting  ...

Best Regards,

Sylvia