Support Article
Database query causes high CPU in Development
SA-12389
Summary
A long running query has been identified when trying to access the “Data Type” details from the Designer Studio. The screen hangs.
SELECT "PC0".PYMETADATAKEY AS "pyMetadataKey", COUNT("IPR".SRCOL1) AS "pySummaryCount(1)"
FROM pr_data_rule_appmetadata "PC0"
LEFT OUTER JOIN (SELECT DISTINCT "IPR".PXPAGENAME AS "SRCOL1", "IPR".PXPAGECLASS AS "SRCOL2",
"IPR".PXREFERENCINGINSNAME AS "PXREFERENCINGINSNAME"
FROM pr_reference_page "IPR"
WHERE ( (("IPR".PXREFERENCINGRULESETNAME||:1 ||"IPR".PXREFERENCINGRULESETVERSION LIKE :2
OR "IPR".PXREFERENCINGRULESETNAME||:3 ||"IPR".PXREFERENCINGRULESETVERSION LIKE :4
AND ("IPR".PXPAGENAME NOT IN (:139 ,:140 ,:141 ,:142 ,:143 ,:144 )) )
AND ("IPR".pxObjClass = :145 ) ) "IPR" ON (( ( ("IPR".SRCOL2 = "PC0".PYMETADATAKEY) )
AND ("PC0".PXOBJCLASS = :146 ) ) ) WHERE ( ("PC0".PYMETADATATYPE = :147 ) )
AND ("PC0".pxObjClass = :148 )
...
GROUP BY "PC0".PYMETADATAKEY
ORDER BY 1 ASC
Error Messages
Not Applicable
Steps to Reproduce
1. Log in as an Administrator.
2. Click the “Data Type” tab from the left panel. Screen hangs.
Root Cause
As stated above, the screen hangs because the Out-Of-The-Box query takes too long to come back. The table, PR_DATA_RULE_APPMETADATA contains more than 4000 instances.
Resolution
In Pega 7.1.5, whenever a data type (class that extends Data-) is created, the class is automatically associated with the application and is seen in the data explorer. A fix has been implemented in Pega 7.1.7.
A local-change has been implemented, open the Application, navigate to “Cases & Data” tab and remove all the data types that are not frequently used. This will remove these data types from the data explorer thus making it load faster.
Note that removing the data type from the “Cases & Data” type does not do anything apart from not showing this in Data Explorer. It is safe. If you want them to be seen in data explorer you can always add them back by clicking Add data type > Existing, from the Data explorer.
Published July 28, 2015 - 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.