Skip to Content
0
Former Member
Apr 04, 2012 at 06:14 PM

Performance issue - SELECT query and Logical DB

310 Views

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.