Support Article
BIX extract query for work objects inefficient
SA-29200
Summary
Developers have created an extract rule at the History-CS-BD-MyQ. In the extract rule they indicated that they want to filter by pxObjClass including: History-CS-BD-MyQ-Acct, History-CS-BD-MyQ-AO, History-CS-BD-MyQ-DC, History-CS-BD-MyQ-TOA. They also filter by message key. The extracts are executed via scripts on a separate server. The sql generated by the engine is:
SELECT count(pzInsKey) FROM MYQ_DATA.myq_history_work WHERE pzInsKey like ? AND pxTimeCreated >= ? AND pxTimeCreated < ? AND (((pxObjClass = ? ) OR (pxObjClass = ? ) OR (pxObjClass = ? ) OR (pxObjClass = ? ) OR (pxObjClass = ? )) AND ((pyMessageKey not like ? ) OR (pyMessageKey is null)))*
SELECT pzPVStream , pxCommitDateTime AS "pxCommitDateTime" FROM MYQ_DATA.myq_history_work WHERE pzInsKey like ? AND pxTimeCreated >= ? AND pxTimeCreated < ? AND (((pxObjClass = ? ) OR (pxObjClass = ? ) OR (pxObjClass = ? ) OR (pxObjClass = ? ) OR (pxObjClass = ? )) AND ((pyMessageKey not like ? ) OR (pyMessageKey is null))) ORDER BY pzInsKey*
Developers do not think they need the WHERE pzInsKey like ? portion of the sql since they are passing in the pxObjClass in the filters and would like to have this dropped.
This is causing the counts and the extracts to be slow.
Error Messages
Not applicable.
Steps to Reproduce
- Create an extract rule at top level of History class.
- Include filter for implementation layer pxObjClass instances.
- Test generated SQL.
Root Cause
A defect in Pegasystems’ code or rules.
Resolution
A Check box will be added on the Rule-Admin-Extract rule form in Pega 7.2.2, Skip Standard Filters.
If this is enabled, it will not add the filter condition on pzInskey/pxObjClass columns to the where condition during extraction process.
Published October 25, 2016 - 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.