Question

1
Replies
259
Views
Kondal Member since 2018 11 posts
LTI
Posted: 11 months ago
Last activity: 11 months 2 weeks ago

Slow SQL on table PEGARULES.PR_SYS_APPCACHE_DEP

Hello,

Is the below query(long running query) which occurs more frequently for us is related to anything with the cache settings or some configurable variable.

DELETE FROM pegarules.pr_sys_appcache_shortcut WHERE pzinskey IN (SELECT e.pzinskey FROM pegarules.pr_sys_appcache_dep d, pegarules.pr_sys_appcache_shortcut e, pegarules.pr_sys_app_hierarchy_flat h, pegarules.pr_sys_app_hierarchy_flat h2, pegarules.pr_sys_app_ruleset_index i, pegarules.pr_sys_app_ruleset_index i2, pegarules.pr_sys_class_ancestors c, pegarules.pr_sys_class_ancestors c2 WHERE d.pzrulename = ? AND d.pzruletype = ? AND d.pzcacheconfigid = ? AND d.pzexplicitexcludecombined < 'literal' AND d.pzimplementationkey = e.pzimplementationkey AND e.pzapphash = h2.pztopapphash AND h2.pzapphash = i2.pzapphash AND i2.pzrulesetname = ? AND (i2.pzrulesetversionmajor = -'literal' OR (i2.pzrulesetversionmajor = ? AND (i2.pzrulesetversionminor = -'literal' OR i2.pzrulesetversionminor > ? OR (i2.pzrulesetversionminor = ? AND (i2.pzrulesetversionpatch = -'literal' OR i2.pzrulesetversionpatch >= ?))))) AND h.pztopapphash = h2.pztopapphash AND h.pzapphash = i.pzapphash AND i.pzrulesetname = d.pzrulesetname AND c.pxleafclassname = e.pzprimarypageclassname AND c.pxucclassname = d.pzappliestoclassname AND c2.pxleafclassname = e.pzprimarypageclassname AND c2.pxclassname = ? AND (c.pxheight > c2.pxheight OR (c.pxheight = c2.pxheight AND ((d.pzrulesetname = ? AND (d.pzrulesetversionmajor = -'literal' OR (d.pzrulesetversionmajor = ? AND (d.pzrulesetversionminor = -'literal' OR (d.pzrulesetversionminor < ? OR (d.pzrulesetversionminor = ? AND d.pzrulesetversionpatch = -'literal' OR d.pzrulesetversionpatch <= ?))))) OR (d.pzrulesetname != ? AND (h.pzappheight > h2.pzappheight OR (h.pzappheight = h2.pzappheight AND i.pzrulesetindex > i2.pzrulesetindex))))))) UNION ALL SELECT e.pzinskey FROM pegarules.pr_sys_appcache_dep d, pegarules.pr_sys_appcache_shortcut e, pegarules.pr_sys_class_ancestors cDep WHERE d.pzrulename = ? AND d.pzruletype = ? AND d.pzcacheconfigid = ? AND d.pzexplicitexcludecombined >= 'literal' AND d.pzimplementationkey = e.pzimplementationkey AND cDep.pxleafclassname = ? AND cDep.pxclassname = d.pzassemblytimeclass UNION ALL SELECT e.pzinskey FROM pegarules.pr_sys_appcache_dep d, pegarules.pr_sys_appcache_shortcut e, pegarules.pr_sys_class_ancestors cMod, pegarules.pr_sys_class_ancestors cDep WHERE d.pzrulename = ? AND d.pzruletype = ? AND d.pzcacheconfigid = ? AND d.pzexplicitexcludecombined >= 'literal' AND d.pzimplementationkey = e.pzimplementationkey AND cMod.pxleafclassname = d.pzassemblytimeclass AND cMod.pxclassname = ? AND cDep.pxleafclassname = d.pzassemblytimeclass AND cDep.pxucclassname = d.pzappliesToClassName AND cMod.pxheight <= cDep.pxheight)

Regards,

Kondal

***Moderator Edit-Vidyaranjan: Updated Platform Capability***

System Administration
Share this page LinkedIn