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

Missing Bind control on INTERACTION_HISTORY_QUERY or FACTTABLE

SA-103192

Summary



An out-of-the-box SQL query performance degrades on using the query without Bind variables. The pySubjectID is hardcoded in the Interaction query while the Clause values disabled query caching.


Error Messages



8_PEGA0005_586_500_11.11.1112_XXX1_1_NA_NA_AHI9K7HNOLEV42IYPDDFSOHXYMJ3S1W0BA_NA_PegaMKT-Work_XXX:01-16-01_5c656e9e83d95807f11d93b3cd9fe06e_N_103_AHI9K7HNOLEV42IYPDDFSOHXYMJ3S1W0BA_7070_[ACTIVE] ExecuteThread: '78' for queue: 'weblogic.kernel.Default (self-tuning)'_STANDARD_com.pega.pegarules.data.internal.store.DataStorePreparedStatement_mccvmap01|11.11.11|Rest|XXXServices|V1|getnba|AHI9K7HNOLEV42IYPDDFSOHXYMJ3S1W0BA_ServicePAL.Rest.XXXServices.V1.getnba_Rule-Obj-Activity:GetNBA_PEGAMKT-WORK-CONTAINER GETNBA #20200105T211653.230 GMT Step: 16 Circum: 0_NA_____pxRDBIOElapsed=0.59;pxInteractions=1;pxRuleCount=1;pxRDBIOCount=2;pxServiceActivityElapsed=0.08;pxInputBytes=298;pxTotalReqCPU=0.06;pxRunModelCount=1;pxServiceCount=1;pxRulesUsed=2,026;pxRunWhenCount=277;pxServiceInMapReqElapsed=0.01;pxRulesExecuted=326;pxOtherCount=19;pxRuleFromCacheCount=1;pxTrackedPropertyChangesCount=4;pxRDBRowWithoutStreamCount=111;pxTotalReqTime=0.68;pxActivityCount=5;pxServiceDataVolume=298;pxAlertCount=1;pxOtherFromCacheCount=20;pxRDBWithoutStreamCount=12;_XXX-Data-Customer_NA_XXX-Data-Customer_NA_doActivity Rule-Decision-Strategy;DataFlow-Execute;RULE-OBJ-ACTIVITY PEGAMKT-WORK-CONTAINER GETNBA #20200105T211653.230 GMT Step: 16 Circum: 0;doActivity Rule-Obj-Activity:GetNBA;initial Executable;0 additional frames in stack;_PZ_STRATEGY_EXECUTOR=[removed];pxDecisionRef=[removed];IH_SUMMARY_CACHE=[removed];RULE_EXECUTOR=[removed];pzMyStrategyName=[removed];STRATEGY_EXECUTION_RESULTS_CONTAINER=[removed];STRATEGY_INPUT_PARAMETERS=[removed];pxExplainModelExecutions=[removed];INTERACTION_HISTORY_QUERY=[removed];InteractionName=[removed];EXECUTION_MODE=[removed];pzRandomSeed=[removed];pxRandomObj=[removed];WhenRuleExecutorsCache=[removed];pzStrategyDataCaches=[removed];pxDecisionTime=[removed];decisionProperties=[removed];INTERACTION_HISTORY=[removed];INTERACTION_HISTORY_CONFIG=[removed];_Database operation took more than the threshold of 500 ms: 586 ms    

SQL: SELECT pxFactID AS "pxFactID", pxOutcomeTime AS "pxOutcomeTime", pySubjectID AS "pySubjectID", pxInteractionID AS "pxInteractionID", "FACTTABLE".pzActionID AS "pzActionID", "FACTTABLE".pzApplicationID AS "pzApplicationID", "FACTTABLE".pzChannelID AS "pzChannelID", "FACTTABLE".pzContextID AS "pzContextID", "FACTTABLE".pzCustomerID AS "pzCustomerID", "FACTTABLE".pzOperatorID AS "pzOperatorID", "FACTTABLE".pzOutcomeID AS "pzOutcomeID", "FACTTABLE".pzJourneyID AS "pzJourneyID", "FACTTABLE".pxDecisionTime AS "pxDecisionTime", "FACTTABLE".pxPriority AS "pxPriority", "FACTTABLE".pxRank AS "pxRank", "FACTTABLE".pyExternalID AS "pyExternalID", "FACTTABLE".pyGroupID AS "pyGroupID", "FACTTABLE".pyLatitude AS "pyLatitude", "FACTTABLE".pyLongitude AS "pyLongitude", "FACTTABLE".pyPropensity AS "pyPropensity", "FACTTABLE".pyPartitionKey AS "pyPartitionKey", "FACTTABLE".pyRevenue AS "pyRevenue", "FACTTABLE".pyISFactID AS "pyISFactID", "FACTTABLE".pyMaxBudget AS "pyMaxBudget", "FACTTABLE".pyTargetBudget AS "pyTargetBudget", "FACTTABLE".pyFulfilled AS "pyFulfilled", "FACTTABLE".pyWeight AS "pyWeight", "FACTTABLE".IPAddress AS "IPAddress", "FACTTABLE".Revenue AS "Revenue", "FACTTABLE".Cost AS "Cost", "FACTTABLE".ImpressionCounter AS "ImpressionCounter", "FACTTABLE".ContactEndDate AS "ContactEndDate", "FACTTABLE".CustImpressionCounter AS "CustImpressionCounter", "FACTTABLE".CustContactEndDate AS "CustContactEndDate", "FACTTABLE".JourneyImpressionCounter AS "JourneyImpressionCounter", "FACTTABLE".JourneyContactEndDate AS "JourneyContactEndDate", "FACTTABLE".OfferJourneyName AS "OfferJourneyName", "FACTTABLE".FulfilmentTRXID AS "FulfilmentTRXID", "FACTTABLE".FDOContractEndDate AS "FDOContractEndDate", "FACTTABLE".JourneyID AS "JourneyID", "FACTTABLE".AssessmentSeqNo AS "AssessmentSeqNo", "FACTTABLE".Amount AS "Amount", "FACTTABLE".TargetTopUpDate AS "TargetTopUpDate", "FACTTABLE".Benefit AS "Benefit", "FACTTABLE".Validity AS "Validity", "FACTTABLE".CustomerLifeCycle AS "CustomerLifeCycle", "FACTTABLE".AdjustmentRequestAmount AS "AdjustmentRequestAmount", "FACTTABLE".Benefit2 AS "Benefit2", "FACTTABLE".Benefit3 AS "Benefit3", "FACTTABLE".Benefit4 AS "Benefit4", "FACTTABLE".BenefitType AS "BenefitType", "FACTTABLE".BenefitType2 AS "BenefitType2", "FACTTABLE".BenefitType3 AS "BenefitType3", "FACTTABLE".BenefitType4 AS "BenefitType4", "FACTTABLE".ValidityUnit AS "ValidityUnit", "FACTTABLE".ChangeExpiry AS "ChangeExpiry", "FACTTABLE".CVS AS "CVS", "FACTTABLE".SelectedChannelModel AS "SelectedChannelModel", "FACTTABLE".SelectedPropModel AS "SelectedPropModel", "FACTTABLE".StrategyChangeReason AS "StrategyChangeReason", "FACTTABLE".NonGsmCampaignCode AS "NonGsmCampaignCode", "FACTTABLE".NonGsmCampaignId AS "NonGsmCampaignId", "pyAssociationStrength" AS "pyAssociationStrength", "pyAssociatedID" AS "pyAssociatedID" FROM

 (SELECT "FACTTABLE".pxFactID, "FACTTABLE".pxOutcomeTime, "FACTTABLE".pySubjectID, "FACTTABLE".pxInteractionID, "FACTTABLE".pzActionID, "FACTTABLE".pzApplicationID, "FACTTABLE".pzChannelID, "FACTTABLE".pzContextID, "FACTTABLE".pzCustomerID, "FACTTABLE".pzOperatorID, "FACTTABLE".pzOutcomeID, "FACTTABLE".pzJourneyID, "FACTTABLE".pxDecisionTime, "FACTTABLE".pxPriority, "FACTTABLE".pxRank, "FACTTABLE".pyExternalID, "FACTTABLE".pyGroupID, "FACTTABLE".pyLatitude, "FACTTABLE".pyLongitude, "FACTTABLE".pyPropensity, "FACTTABLE".pyPartitionKey, "FACTTABLE".pyRevenue, "FACTTABLE".pyISFactID, "FACTTABLE".pyMaxBudget, "FACTTABLE".pyTargetBudget, "FACTTABLE".pyFulfilled, "FACTTABLE".pyWeight, "FACTTABLE".IPAddress, "FACTTABLE".Revenue, "FACTTABLE".Cost, "FACTTABLE".ImpressionCounter, "FACTTABLE".ContactEndDate, "FACTTABLE".CustImpressionCounter, "FACTTABLE".CustContactEndDate, "FACTTABLE".JourneyImpressionCounter, "FACTTABLE".JourneyContactEndDate, "FACTTABLE".OfferJourneyName, "FACTTABLE".FulfilmentTRXID, "FACTTABLE".FDOContractEndDate, "FACTTABLE".JourneyID, "FACTTABLE".AssessmentSeqNo, "FACTTABLE".Amount, "FACTTABLE".TargetTopUpDate, "FACTTABLE".Benefit, "FACTTABLE".Validity, "FACTTABLE".CustomerLifeCycle, "FACTTABLE".AdjustmentRequestAmount, "FACTTABLE".Benefit2, "FACTTABLE".Benefit3, "FACTTABLE".Benefit4, "FACTTABLE".BenefitType, "FACTTABLE".BenefitType2, "FACTTABLE".BenefitType3, "FACTTABLE".BenefitType4, "FACTTABLE".ValidityUnit, "FACTTABLE".ChangeExpiry, "FACTTABLE".CVS, "FACTTABLE".SelectedChannelModel, "FACTTABLE".SelectedPropModel, "FACTTABLE".StrategyChangeReason, "FACTTABLE".NonGsmCampaignCode, "FACTTABLE".NonGsmCampaignId, null AS "pyAssociationStrength", null AS "pyAssociatedID" FROM PEGA_DATA_PROD.PR_DATA_IH_FACT "FACTTABLE"  WHERE ("FACTTABLE".pySubjectID IN ('905395452430'))  

UNION ALL SELECT "FACTTABLE".pxFactID, "FACTTABLE".pxOutcomeTime, "FACTTABLE".pySubjectID, "FACTTABLE".pxInteractionID, "FACTTABLE".pzActionID, "FACTTABLE".pzApplicationID, "FACTTABLE".pzChannelID, "FACTTABLE".pzContextID, "FACTTABLE".pzCustomerID, "FACTTABLE".pzOperatorID, "FACTTABLE".pzOutcomeID, "FACTTABLE".pzJourneyID, "FACTTABLE".pxDecisionTime, "FACTTABLE".pxPriority, "FACTTABLE".pxRank, "FACTTABLE".pyExternalID, "FACTTABLE".pyGroupID, "FACTTABLE".pyLatitude, "FACTTABLE".pyLongitude, "FACTTABLE".pyPropensity, "FACTTABLE".pyPartitionKey, "FACTTABLE".pyRevenue, "FACTTABLE".pyISFactID, "FACTTABLE".pyMaxBudget, "FACTTABLE".pyTargetBudget, "FACTTABLE".pyFulfilled, "FACTTABLE".pyWeight, "FACTTABLE".IPAddress, "FACTTABLE".Revenue, "FACTTABLE".Cost, "FACTTABLE".ImpressionCounter, "FACTTABLE".ContactEndDate, "FACTTABLE".CustImpressionCounter, "FACTTABLE".CustContactEndDate, "FACTTABLE".JourneyImpressionCounter, "FACTTABLE".JourneyContactEndDate, "FACTTABLE".OfferJourneyName, "FACTTABLE".FulfilmentTRXID, "FACTTABLE".FDOContractEndDate, "FACTTABLE".JourneyID, "FACTTABLE".AssessmentSeqNo, "FACTTABLE".Amount, "FACTTABLE".TargetTopUpDate, "FACTTABLE".Benefit, "FACTTABLE".Validity, "FACTTABLE".CustomerLifeCycle, "FACTTABLE".AdjustmentRequestAmount, "FACTTABLE".Benefit2, "FACTTABLE".Benefit3, "FACTTABLE".Benefit4, "FACTTABLE".BenefitType, "FACTTABLE".BenefitType2, "FACTTABLE".BenefitType3, "FACTTABLE".BenefitType4, "FACTTABLE".ValidityUnit, "FACTTABLE".ChangeExpiry, "FACTTABLE".CVS, "FACTTABLE".SelectedChannelModel, "FACTTABLE".SelectedPropModel, "FACTTABLE".StrategyChangeReason, "FACTTABLE".NonGsmCampaignCode, "FACTTABLE".NonGsmCampaignId, "ASSOCIATIONTABLE".pyAssociationStrength AS "pyAssociationStrength", "ASSOCIATIONTABLE".pySubjectID AS "pyAssociatedID" FROM PEGA_DATA_PROD.PR_DATA_IH_FACT "FACTTABLE"  

JOIN PEGA_DATA_PROD.PR_DATA_IH_ASSOCIATION "ASSOCIATIONTABLE" ON (("ASSOCIATIONTABLE".pyAssociatedID = "FACTTABLE".pySubjectID) )

WHERE ("ASSOCIATIONTABLE".pySubjectID IN ('905395452430')) )  "FACTTABLE"  

LEFT OUTER JOIN PEGA_DATA_PROD.PR_DATA_IH_DIM_OUTCOME "OUTCOMETABLE" ON (("FACTTABLE".pzOutcomeID = "OUTCOMETABLE".pzID) ) WHERE ("FACTTABLE".pxOutcomeTime >= ?) AND ( ( ("OUTCOMETABLE".IHImportFlag = '1') ) )  ORDER BY 2 DESC_


Steps to Reproduce



Run the Strartegy with an Interaction History (IH) component.


Root Cause



A defect in Pegasystems’ code or rules.

Interaction History caused a performance issues because pySubjectID was hardcoded in the query while the Clause values disabled query caching.
To resolve this, a Bind variable must be passed when using a single subject ID Select call to the FACT table in the Inbound queries.


Resolution



This feature is implemented in Pega Platform 8.3.2.

Tags:

Pega Platform 8.3 Pega Platform Platform Decision Management

Published March 27, 2020 - Updated December 2, 2021

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.

Visit the Collaboration Center

Did you find this content helpful?

Yes
No

Want to help us improve this content?
Suggest an edit

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