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

IH queries taking a long time to complete



The Interaction History related query (below) performance degrades during execution.

SELECT pxFactID AS "pxFactID", pxOutcomeTime AS "pxOutcomeTime", pySubjectID AS "pySubjectID", pxInteractionID AS "pxInteractionID", "FACTTABLE".pzActionID AS "pzActionID", "FACTTABLE".pzChannelID AS "pzChannelID", "FACTTABLE".pzContextID AS "pzContextID", "FACTTABLE".pzOutcomeID AS "pzOutcomeID", "FACTTABLE".pxDecisionTime AS "pxDecisionTime", "FACTTABLE".LeadID AS "LeadID", "FACTTABLE".ChannelOutcome AS "ChannelOutcome", "pyAssociationStrength" AS "pyAssociationStrength", "pyAssociatedID" AS "pyAssociatedID" FROM (SELECT "FACTTABLE".pxFactID, "FACTTABLE".pxOutcomeTime, "FACTTABLE".pySubjectID, "FACTTABLE".pxInteractionID, "FACTTABLE".pzActionID, "FACTTABLE".pzChannelID, "FACTTABLE".pzContextID, "FACTTABLE".pzOutcomeID, "FACTTABLE".pxDecisionTime, "FACTTABLE".LeadID, "FACTTABLE".ChannelOutcome, null AS "pyAssociationStrength", null AS "pyAssociatedID" FROM DATA.PR_DATA_IH_FACT "FACTTABLE" WHERE ("FACTTABLE".pySubjectID IN ('xxxxxxxxxx',........, 'xxxxxxxxxx')) UNION ALL SELECT "FACTTABLE".pxFactID, "FACTTABLE".pxOutcomeTime, "FACTTABLE".pySubjectID, "FACTTABLE".pxInteractionID, "FACTTABLE".pzActionID, "FACTTABLE".pzChannelID, "FACTTABLE".pzContextID, "FACTTABLE".pzOutcomeID, "FACTTABLE".pxDecisionTime, "FACTTABLE".LeadID, "FACTTABLE".ChannelOutcome, "ASSOCIATIONTABLE".pyAssociationStrength AS "pyAssociationStrength", "ASSOCIATIONTABLE".pySubjectID AS "pyAssociatedID" FROM DATA.PR_DATA_IH_FACT "FACTTABLE" JOIN DATA.PR_DATA_IH_ASSOCIATION "ASSOCIATIONTABLE" ON (("ASSOCIATIONTABLE".pyAssociatedID = "FACTTABLE".pySubjectID) ) WHERE ("ASSOCIATIONTABLE".pySubjectID IN ('xxxxxxxxxx',........, 'xxxxxxxxxx')) ) "FACTTABLE" WHERE ("FACTTABLE".pxOutcomeTime >= ?) ORDER BY 2 DESC

The IN Filter condition in the query passes 250 bind variables (actual data in the IN Filter is masked out). The PR_DATA_IH_FACT table has more than 75 million records. The Main Strategy and Sub Strategies have about 10-11 Interaction History (IH) Shapes, and the IH records are fetched for 240 days.

If the number of days criteria is removed from the IH shape (that is, eliminate the Where condition "WHERE ("FACTTABLE".pxOutcomeTime  >= ?)" from the query), the query runs in 7 seconds. However, with the Number of Days criteria (that is, with the Where condition for pxOutcomeTime), the query runs in 7-8 minutes.

Error Messages

Not Applicable

Steps to Reproduce

  1. Create a Strategy and Sub-Strategies.
  2. Use the IH shape to fetch the Interaction History record. Verify the performance of the Interaction History related query.

Root Cause

A defect in Pegasystems’ code or  rules.
The IH query generated by the Pega application has a long list of bind variables for pySubjectID. The application already has two indexes (that is, the Composite index on the pySubjectID and pxOutcomeTime columns, and a Single index on the pxOutcomeTime column). Due to the long list of bind variables passed in the query, the DB2 in this scenario automatically selects to use the single index on the pxOutcomeTime column, instead of Composite indexing on the pySubjectID and pxOutcomeTime columns. As a result, the query performance degraded.


Perform the following local-change:
  1. In the activity which invokes the data flow, configure a page of Data-Decision-DDF-RunOptions class and set the pyBatchSize property to 100 (default value is 250) on the RunOptions page before calling the DataFlow-Execute method.
  2. Reduce the pyBatchSize to reduce the size of the IN Filter condition generated by Pega for the Interaction History component.

Published August 19, 2019 - Updated October 8, 2020

Was this useful?

0% found this useful

Have a question? Get answers now.

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

Did you find this content helpful?

Want to help us improve this content?

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