Optimization of database function PRJ_HPD_FACTOR_FCT

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

PRJ_HPD_FACTOR_FCT is a database function that is used to calculate hours or days in queries returning allocation and availability. The function is currently being called as part of selects generated. The function returns a single value depending on a system option. The value is either 1 or the number of hours in a day as defined in the site calendar. 

Reviewing the database reports showed that the function being called multiple times and consuming top CPU% 

For example, the actsum attribute of the team object uses it in it's read expression like this:

( CASE WHEN SRM_RESOURCES.RESOURCE_TYPE & lt;2 THEN (@[email protected]_sum_assignment_fct(prteam.prid,1) / 3600 / @[email protected]_HPD_FACTOR_FCT()ELSE @[email protected]_sum_assignment_fct(prteam.prid,1)END)

Expected Results: PRJ_HPD_FACTOR_FCT shouldn't be executed multiple times 

Actual Results: PRJ_HPD_FACTOR_FCT is executed multiple times causing CPU overhead 

Environment

Clarity Version 16.x, 16.1.x, 16.2.x 

Cause

DE80951

Resolution

The function PRJ_HPD_FACTOR_FCT is optimized via DE80951 and its fixed in 16.2.3 (targeted Aug 2024) and backported to 16.2.2 patch 1