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

  1. Create an extract rule at top level of History class.
  2. Include filter for implementation layer pxObjClass instances.
  3. 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 14, 2016 - Updated October 24, 2016

Have a question? Get answers now.

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