Support Article
Report Query "include descendant classes" executes 120K times
SA-39645
Summary
This query will execute when the "include all descendant classes" is set in the report definition. This is the default setting.
With app_rulesets(pzRuleSetName) as (SELECT "PC0AR"."pzRuleSetName" AS "pzRuleSetName" FROM ( SELECT "PC0AR".pzRuleSetName AS "pzRuleSetName" FROM RULES.pr_sys_app_hierarchy_flat "PC0AH" INNER JOIN RULES.pr_sys_app_ruleset_index "PC0AR" ON ( ( "PC0AH".pzAppHash = "PC0AR".pzAppHash ) ) WHERE ( "PC0AH".pzTopAppHash = :1 ) ) "PC0AR" ) ,app_classes(pxClassName) as (SELECT DISTINCT "CA1".pxClassName AS "pxClassName" FROM RULES.pr_sys_class_ancestors "CA1" INNER JOIN RULES.pr_sys_class_ancestors "CA2" ON ( ( "CA1".pxHeight <= "CA2".pxHeight AND "CA1".pxHashKey = "CA2".pxHashKey ) ) WHERE ( "CA1".pxHashKey IN (SELECT "PC0CA".pxHashKey AS "Field1" FROM RULES.pr_sys_class_ancestors "PC0CA" WHERE ( "PC0CA".pxClassName = :2 ) ) AND "CA2".pxClassName = :3 ) ) SELECT "AC".pxClassName AS "pxClassName" FROM RULES.pr4_base "r" INNER JOIN app_classes "AC" ON ( ( "r".pyClassName = "AC".pxClassName ) ) INNER JOIN app_rulesets "AR" ON ( ( "r".pyRuleSet = "AR".pzRuleSetName ) )
This is processed 120K times during a load test and consumes a significant amount of CPU on the database.
Error Messages
Not Applicable
Steps to Reproduce
Not Applicable
Root Cause
A defect or configuration issue in the operating environment.
During the load test reports are being run with the "Include all descendant classes" flag set.
The execution of the query is a 1 for 1 reflection on how many reports with this setting are run.
Resolution
Make the following change to the operating environment:
Remove the "Include all descendant classes" setting from report definitions unless it is explicitly needed for the report.
Published August 11, 2017 - 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.