Performance Problem in Production system only

Development (ABAP Development WorkBench, ABAP/4 programming)

Moderators: Snowy, thx4allthefish, YuriT, Gothmog

Post Reply
MTK
Posts: 4
Joined: Tue Jun 13, 2006 9:42 am

Performance Problem in Production system only

Post by MTK » Tue Mar 17, 2009 7:41 am

Hello Expers,
We have ABAP program using Inner Join and it's working good since long time.
Without doing any change in program it's run time has increased from 35 seconds to
more that 900 seconds in Production system since last week.
Program still takes 35 to 40 seconds in all other systems Dev, QA, Sandbox(copy of prod one week back) except PROD system.
What could be the possible reason for it?

Thanks...

BradB
Posts: 685
Joined: Wed Mar 22, 2006 10:27 am
Location: Standing right behind you

Re: Performance Problem in Production system only

Post by BradB » Tue Mar 17, 2009 7:44 am

Are your DB statistics up to date? Did you recently run archiving?
Ironman Triathlete and SAP Dev/Config Expert
(which is harder?)

MTK
Posts: 4
Joined: Tue Jun 13, 2006 9:42 am

Re: Performance Problem in Production system only

Post by MTK » Tue Mar 17, 2009 7:56 am

Yes, our DB statistics is up to date and we are not using archiving yet.

VLozano
Posts: 5142
Joined: Mon Sep 13, 2004 8:17 am
Location: Idiocity
Contact:

Re: Performance Problem in Production system only

Post by VLozano » Tue Mar 17, 2009 8:29 am

Which tables are you using with that INNER JOIN? Can you post here your SQL? (please, use the Code button)
Tuly Idiots
Because we know we are part of the problem

MTK
Posts: 4
Joined: Tue Jun 13, 2006 9:42 am

Re: Performance Problem in Production system only

Post by MTK » Tue Mar 17, 2009 8:36 am

SELECT PROJ~VERNR PRPS~POSKI PROJ~POST1 JEST~STAT COBRA~BZDAT
COSP~WKG001 COSP~WKG002 COSP~WKG003 COSP~WKG004
COSP~WKG005 COSP~WKG006 COSP~WKG007 COSP~WKG008
COSP~WKG009 COSP~WKG010 COSP~WKG011 COSP~WKG012
INTO TABLE ITAB
FROM ( ( ( ( COSP
INNER JOIN JEST
ON JEST~OBJNR = COSP~OBJNR )
INNER JOIN COBRA
ON COBRA~OBJNR = COSP~OBJNR )
INNER JOIN PRPS
ON PRPS~OBJNR = COSP~OBJNR )
INNER JOIN PROJ
ON PROJ~PSPNR = PRPS~PSPHI )
WHERE (WHERE_CLAUSE)
AND COSP~GJAHR = CURRENT_YEAR
AND COSP~WRTTP = '04' "Actual amount
AND COSP~VERSN = '000' "Version
AND COSP~BEKNZ IN ('S', 'H', 'L') "Debit/Credit Indicator
AND COSP~KSTAR NOT IN S_KSTAR "Exclude Cost Elements
AND JEST~STAT = P_STAT "WBS status
AND JEST~INACT <> 'X' "Active
AND COBRA~BZDAT > '1994/01/01' "AVD not blank
AND COBRA~BZDAT < NEW_DATE "Asset Valuation Date
AND PRPS~PBUKR = P_CCODE "Company code
AND PRPS~VERNR IN S_VERNR "Division
AND PROJ~PSPID IN S_PSPID. "Project number

ese_pinche_ABAP
Posts: 297
Joined: Tue Mar 01, 2005 12:57 pm

Re: Performance Problem in Production system only

Post by ese_pinche_ABAP » Tue Mar 17, 2009 9:12 am

Well he/she did use the code button ha ha ha ha ha....

Looks like a pretty nasty SELECT though...why am I not surprised it has performance problems?

Dr Sidewalk
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

Re: Performance Problem in Production system only

Post by Dr Sidewalk » Tue Mar 17, 2009 9:31 am

1). This may seem like a silly question but you mentioned that in other systems the performance is okay. Do some of those systems have the same database volumes as production?. In Dev systems sometimes indicies are not used when there are low number of records in a table because it's still quicker to perform a full-table scan. However, in a production system the indicies will be used, if possible.
2). If you set a breakpoint at the beginning of the SQL, wait until you get to it, switch across to another session, run ST05 and perform a trace of the SQL statement, then grab the code and perform an explain path on it, what do you get?. Are all the indicies used?.

If production has been running fine and now suddenly it's slowed down then there can be two things (that I know of) that can cause this. One, is that the tables have suddenly grown large in size and is therefore affecting the badly optimised code, the second is that you'll need wither the table statistics updating, or indicies for the primary tables re-built, as other people may have already suggested.

Dr Sidewalk
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it"
"Fail to plan, plan to fail"
"Success is a journey, not a destination."

VLozano
Posts: 5142
Joined: Mon Sep 13, 2004 8:17 am
Location: Idiocity
Contact:

Re: Performance Problem in Production system only

Post by VLozano » Tue Mar 17, 2009 10:27 am

Code: Select all

SELECT PROJ~VERNR  PRPS~POSKI  PROJ~POST1  JEST~STAT  COBRA~BZDAT
         COSP~WKG001 COSP~WKG002 COSP~WKG003 COSP~WKG004
         COSP~WKG005 COSP~WKG006 COSP~WKG007 COSP~WKG008
         COSP~WKG009 COSP~WKG010 COSP~WKG011 COSP~WKG012
    INTO TABLE ITAB
    FROM ( ( ( ( COSP
           INNER JOIN JEST
           ON JEST~OBJNR  = COSP~OBJNR )
           INNER JOIN COBRA
           ON COBRA~OBJNR = COSP~OBJNR )
           INNER JOIN PRPS
           ON PRPS~OBJNR  = COSP~OBJNR )
           INNER JOIN PROJ
           ON PROJ~PSPNR  = PRPS~PSPHI )
  WHERE (WHERE_CLAUSE)
    AND  COSP~GJAHR  = CURRENT_YEAR
    AND  COSP~WRTTP  = '04'                "Actual amount
    AND  COSP~VERSN  = '000'               "Version
    AND  COSP~BEKNZ  IN ('S', 'H', 'L')    "Debit/Credit Indicator
    AND  COSP~KSTAR  NOT IN S_KSTAR        "Exclude Cost Elements
    AND  JEST~STAT   =  P_STAT             "WBS status
    AND  JEST~INACT  <> 'X'                "Active
    AND  COBRA~BZDAT > '1994/01/01'        "AVD not blank
    AND  COBRA~BZDAT <  NEW_DATE           "Asset Valuation Date
    AND  PRPS~PBUKR  =  P_CCODE            "Company code
    AND  PRPS~VERNR  IN S_VERNR            "Division
    AND  PROJ~PSPID  IN S_PSPID.           "Project number
Nothing to say that Dr S did not. It seems you've reached some sort of data volume limit or your system needs a make-up.
In any case, try to read and understand the points of this thread: http://sapfans.com/forums/viewtopic.php?p=878030
There we discussed a bit about when to use JOINs, FOR ALL ENTRIES and LOOPS to make code tunning.

Good luck.
Tuly Idiots
Because we know we are part of the problem

Post Reply