Issue with Performance - SELECT query and Logical DB

[Former Member]

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.

[adam_krawczyk1 · Accepted solution]

Hi Samta,

Your analyse is basic only on ST05 logs and I see this is not enough.

I had similar situation where I saw long database queries measured by ST05. So I ran SAT to see exactly which modules take most time. In SAT output I still saw that database queries takes long time (list sorted by NET time), but it was SAP standard code that I did not want to modify. So I sorted SAT results by Gross time (time for block of code with all subsequent calls) and then I discovered than at top of the list there was Z* function module that called inner heavy standard SAP function instead of doing simpler steps. After reimplementing Z function we reduced time of program execution about ten times and long database readings were not reported anymore.

So I recommend you the same - run SAT/SE30 analyses for your program, sort it by gross time and check if there is no Z function in top of the list of "time consumers". It may happen that you are calling SAP standard functions that do much more than you need in your program. I am sure that output from SAT will give you hints or answer.

If you will still need code logic as it is now, you can search for SAP notes regarding performance for this SAP module. Additionally you may check if indexes are used and create index with matched fields to that standard query.

Regards,

Adam