Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

Performance degrades while running long SQL queries

SA-96540

Summary



The user has Pega Customer Service for Financial Services 7.4 installed on Pega Customer Service 7.4 on Pega Platform 7.4.

Performance degrades while running long SQL queries generated by out-of-the-box rules.



Error Messages



Embed-ReportContent*NA*ReportingPegaCPMFS-WorkCPMGetRelatedServiceItemsDetails;doActivity Rule-Obj-Report-Definition;Java;RULE-OBJ-ACTIVITY RULE-OBJ-REPORT-DEFINITION PXRETRIEVEREPORTDATA
Step: 33 Circum: 0;doActivity Rule-Obj-Activity:pxRetrieveReportData;22 additional frames in stack;*donotShowProperty=true;pyRunType=FullResolve;InteractionKey=I-187816;returnAsParam=true;pyPageName=SvcCases;pyReportClass=PegaCPMFS-Work;retainUnusedJoins=;CopyAlways=true;pyTempPlaceHolder=TempPlaceHolder;pyReportName=CPMGetRelatedServiceItemsDetails;pyReportParamPageName=pyReportParamPage;pyReportPageName=;pyForEachCount=1;*Database operation took more than the threshold of 500 ms: 23,358 ms          SQL: SELECT "PC0"."PXCREATEDATETIME" AS "pxCreateDateTime" , "PC0"."PYLABEL" AS "pyLabel" , "PC0"."ACCOUNTNUMBER" AS "AccountNumber" , "PC0"."PYID" AS "pyID" , "PC0"."PYSTATUSWORK" AS "pyStatusWork" , "PC0"."PXUPDATEDATETIME" AS "pxUpdateDateTime" , "PC0"."PYRESOLVEDTIMESTAMP" AS "pyResolvedTimestamp" , "PC0"."PZINSKEY" AS "pzInsKey" FROM PEGADATA.CPMFS_Work "PC0" WHERE  ( "PC0"."CAPARENTID" = ?  )  AND "PC0"."PXOBJCLASS" IN (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) ORDER BY 1 ASC*

*pyDefinitionKey=RULE-DECLARE-PAGES D_INTERACTIONHISTORY;pyClassName=PegaCA-Work-Interaction;pyRunType=FullResolve;pyPageName=InteractionsList;pyReportClass=PegaCPMFS-Work-Interaction;CopyAlways=true;ContactId=;Type=CONTACT;CifNbr=;pyReportName=CSFSInteractionsByCustomer;pyRuleSet=PegaAppCA;pyPageStructure=list;ID=;pyReportPageName=;*Database operation took more than the threshold of 500 ms: 9,334 ms SQL: SELECT "PC0"."PXCREATEDATETIME" AS "pxCreateDateTime" , "PC0"."INTERACTIONTYPE" AS "InteractionType" , "PC0"."PYID" AS "pyID" , "PC0"."PXCREATEOPERATOR" AS "pxCreateOperator" , "PC0"."PYLABEL" AS "pyLabel" , "PC0"."PYSTATUSWORK" AS "pyStatusWork" , "PC0"."CONTACTNAME" AS "ContactName" , "PC0"."REASONFORINTERACTION" AS "ReasonForInteraction" , "PC0"."CONTACTDISPOSITION" AS "ContactDisposition" , "PC0"."PZINSKEY" AS "pzInsKey" , "PC0"."PYCONTACTCHANNEL" AS "pyContactChannel" FROM PEGADATA.CPMFS_Work "PC0" WHERE  (  UPPER("PC0"."PXOBJCLASS") LIKE ?   )  ORDER BY 1 DESC*

*pyDefinitionKey=RULE-DECLARE-PAGES D_INTERACTIONHISTORY;pyClassName=PegaCA-Work-Interaction;pyRunType=FullResolve;pyPageName=InteractionsList;pyReportClass=PegaCPMFS-Work-Interaction;CopyAlways=true;ContactId=;Type=CONTACT;CifNbr=;pyReportName=CSFSInteractionsByCustomer;pyRuleSet=PegaAppCA;pyPageStructure=list;ID=;pyReportPageName=;*Database operation took more than the threshold of 500 ms: 13,053 ms          SQL: SELECT "PC0"."PXCREATEDATETIME" AS "pxCreateDateTime" , "PC0"."INTERACTIONTYPE" AS "InteractionType" , "PC0"."PYID" AS "pyID" , "PC0"."PXCREATEOPERATOR" AS "pxCreateOperator" , "PC0"."PYLABEL" AS "pyLabel" , "PC0"."PYSTATUSWORK" AS "pyStatusWork" , "PC0"."CONTACTNAME" AS "ContactName" , "PC0"."REASONFORINTERACTION" AS "ReasonForInteraction" , "PC0"."CONTACTDISPOSITION" AS "ContactDisposition" , "PC0"."PZINSKEY" AS "pzInsKey" , "PC0"."PYCONTACTCHANNEL" AS "pyContactChannel" FROM PEGADATA.CPMFS_Work "PC0" WHERE  ( "PC0"."PYID" <> ?  AND  UPPER("PC0"."PXOBJCLASS") LIKE ?   )  ORDER BY 1 DESC* 


Steps to Reproduce

  1. Login to the environment
  2. Start a call interaction
  3. Check the alert log


Root Cause



A defect in Pegasystems’ code or rules.
While running the CSFSInteractionsByCustomer report definition, latency occurs because the query converts pxObjClass to upper case.
While running the CPMGetRelatedServiceItemsDetails report definition, indexes are not added.



Resolution

Perform the following steps:
  1. Apply HFix-59070
  2. Create an index for CAParentID for CPMGetRelatedServiceItemsDetails report definition



 
Suggest Edit

Published January 6, 2020 - Updated December 2, 2021

Did you find this content helpful? Yes No

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us