Support Article
Performance degrades for cases with large attachments
SA-69244
Summary
In the alert logs, multiple alerts related to pc_link_attachment and pc_data_workattach tables display. Performance degrades for cases that have a large number of attachments and when the attachment size is large.
Error Messages
PEGA0042
Steps to Reproduce
- Navigate to the case Attachments tab.
- Navigate to case Notes tab.
- In the case, open an attachment whose size is large.
Root Cause
Large number of records are present in pc_link_attachment and pc_data_workattach tables.
pega_data.pc_data_workattach has multiple PEGA0042 alerts that range between 10-19 seconds. pega_data.pc_link_attachment has PEGA0042 alerts that are below 5 seconds.
Resolution
Perform the following local-change:
Obtain the Execute plan for the below queries and create the correct indexes to optimize the plan with the help of a database administrator (DBA).
- SELECT "pxRefObjectKey" AS "pxRefObjectKey" , "pxInsName" AS "pxInsName", "pzInsKey" as "pxInsHandle" from pega_data.pc_data_workattach WHERE ( "pxInsName" LIKE ? ) AND ( "pxObjClass" = ? )
- SELECT "pxRefObjectKey" AS "pxRefObjectKey" , "pxInsName" AS "pxInsName", "pzInsKey" as "pxInsHandle" from pega_data.pc_data_workattach WHERE "pxObjClass" = ?
- Select a.pxLinkedClassFrom, a.pyMemo,a.pyCategory,a.pxCreateOperator, a.pxObjClass, a.pxLinkedRefTo, a.pxCreateSystemID, a.pxCreateOpName, a.pxInsName,a.pxLinkedClassTo,a.pzInsKey, a.pxLinkedRefFrom, a.pxCreateDateTime, a.AttachCategory, b.pyStatusWork as pyStatusLabel, b.CurrentAssignedOrgUnit, b.pxUpdateOpName from pega_data.pc_link_attachment a,pega_data.pc_UHG_UHCI_GlobalSoluti_e81de b where a.pyCategory='Note' AND a.pxLinkedRefFrom IN ('UHG-UHCI-GLOBALSOLUTIONS-WORK-SERVICEINQUIRY SR-234353') AND a.pxLinkedRefFrom=b.pzInsKey
For more information, refer to: https://community.pega.com/knowledgebase/articles/pega0042-alert-packaging-database-query-has-exceeded-operation-time
Published January 5, 2019 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.