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 due to slow running queries

SA-84783

Summary



In the Pega Marketing system, SQL performance degrades for the below tables:
  1. IH_FACT
  2. PushLogUsageData/pr_perf_stats


Error Messages



For the IH_FACT table: PEGA0058 and PEGA0059 alerts.
For the PushLogUsageData/pr_perf_stats table: Pega0005 alerts



Steps to Reproduce

  1. Install Pega Platform 7.3.1 and Pega Marketing (PM) 7.31.
  2. Create the required ruleset.
  3. Run three multi-channel Campaigns for 1 million users each.


Root Cause



A defect or configuration issue in the operating environment.
The system was overloaded with a large amount of Interaction History data and System Usage information.



Resolution



Here's the explanation for the reported behavior:
  1. For the IH_FACT table: 

    Below is the SQL query that triggered the alerts,

    SELECT pxFactID AS "pxFactID", pxOutcomeTime AS "pxOutcomeTime", pySubjectID AS "pySubjectID", pxInteractionID AS "pxInteractionID", "FACTTABLE".pzActionID AS "pzActionID", "FACTTABLE".pzOutcomeID AS "pzOutcomeID", "pyAssociationStrength" AS "pyAssociationStrength", "pyAssociatedID" AS "pyAssociatedID" FROM  (SELECT "FACTTABLE".pxFactID, "FACTTABLE".pxOutcomeTime, "FACTTABLE".pySubjectID, "FACTTABLE".pxInteractionID, "FACTTABLE".pzActionID, "FACTTABLE".pzOutcomeID, null AS "pyAssociationStrength", null AS "pyAssociatedID" FROM {Class:Data-Decision-IH-Fact} "FACTTABLE"  WHERE ("FACTTABLE".pySubjectID IN ('109975891'))  UNION ALL SELECT "FACTTABLE".pxFactID, "FACTTABLE".pxOutcomeTime, "FACTTABLE".pySubjectID, "FACTTABLE".pxInteractionID, "FACTTABLE".pzActionID, "FACTTABLE".pzOutcomeID, "ASSOCIATIONTABLE".pyAssociationStrength AS "pyAssociationStrength", "ASSOCIATIONTABLE".pySubjectID AS "pyAssociatedID" FROM {Class:Data-Decision-IH-Fact} "FACTTABLE"  JOIN {Class:Data-Decision-IH-Association} "ASSOCIATIONTABLE" ON (("ASSOCIATIONTABLE".pyAssociatedID = "FACTTABLE".pySubjectID) ) WHERE ("ASSOCIATIONTABLE".pySubjectID IN ('109975891')) )  "FACTTABLE"  LEFT OUTER JOIN {Class:Data-Decision-IH-Dimension-Outcome} "OUTCOMETABLE" ON (("FACTTABLE".pzOutcomeID = "OUTCOMETABLE".pzID) ) WHERE ("FACTTABLE".pxOutcomeTime >= {IHQueryPage.pzStartFrom DateTime}) AND ( ( ("OUTCOMETABLE".pyOutcome = 'Accepted') ) )  ORDER BY 2 DESC

    The query execution time may increase overtime when the Interaction History (IH) table size increases.

    It is recommended  to purge the transaction data in the database.

    For more information, refer to:

    https://community.pega.com/support/support-articles/archive-process-interaction-history-data-slowing-performance

    https://community.pega.com/sites/default/files/help_v72/dsm/concepts/ih-scripts-con.htm

     
  2. For the pr_perf_stats table:

    The pr_perf_stats table stores the Usage information for all nodes and all Requestor types. By default, the system automatically purges records that are older than 30 days.

    If rows older than 30 days are required for Performance analysis, Debugging, or other Reporting, purge the rows by Date (known as Trimming) without affecting other system capabilities.

    Once each day, the Pega-RULES agent starts a stored procedure that purges older rows of the table and passes the pxProcess property value as the number of days to retain.

    To set a retention period longer or shorter than 30 days, add or revise the following element to the prconfig.xml file and restart the system:


    <env name="usage/retentionperiod " value="nnn" />

    Note: nnn is the number of days to retain.
Suggest Edit

Published March 18, 2020 - Updated October 8, 2020

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