Excessive calls to Stored Procedure PPA_GETRATE_WITHROLE causes slow performance with Create New Cost Plan from Task Assignment
For projects spanning multiple years and containing several assignments, the CA PPM application makes a high number of calls to a stored procedure PPA_GETRATE_WITHROLE, slowing down the creation of new cost plan using "New From Task Assignment" option. In some cases where project has assignments in the thousands, it takes 10+ minutes to create a cost plan that has 1 grouping level and, around 2 year duration.
Steps to Reproduce:
- Create a project with duration of around 2 years
- Staff a project with around 100 resources who have financial properties
- Create around 600 tasks, then assign resources to create around 2000 assignments
- Project properties, Financial page: fill in Department and Location field, as well as rate matrix fields
- Go to Financial Plans tab, [Actions] menu, click 'New from Task Assignment' link
- Use Transaction Class as the only grouping attribute (or any other attribute, just keep it simple by using just one grouping level), select Monthly period type or any pre-defined, active fiscal time period type
- Click 'Submit' button
Expected Result: Cost plan is created
Actual Result: Cost plan may take 10+ minutes to create. There have been reported cases where the creation takes over 30 minutes.
CLRT-70474 : Closed. Reference Performance Improvement CLRT-73339 for which a partial fix has been implemented in 14.1.
Performance improved in all the scenarios except when rate matrix is defined using charge code, resource and when every task is having its own charge code.
To fully improve performance in all scenarios, the code will need to be rewritten. This idea is currently under review.
Break up the project into smaller projects, if possible; with each project covering a smaller time frame. For example, if the project is a software development project, one project could be for Analysis, another project for design, another project for coding, etc. This allows the tasks to be spread over different projects instead of all the tasks in one project. With less tasks in a project, this will reduce the number of calls to the stored procedure PPA_GETRATE_WITHROLE. If tasks have posted actuals, the tasks cannot be removed from the existing project.