Support Article
IH queries taking a long time to complete
SA-56665
Summary
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
- Create a Strategy and Sub-Strategies.
- 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.
Resolution
Perform the following local-change:
- 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.
- 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
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.