AnsweredAssumed Answered

Rate matrix extracton performance tuning

Question asked by urmas on Jul 16, 2014
Latest reply on Aug 5, 2014 by urmas

Does anybody have practical experience on Rate matrix extraction job performance tuning?

 

TEC439334 Prepare Rate Matrix Data and Update Rate Matrix Data

says that new parameters have been added.

'Prepare Rate Matrix Data' and 'Update Rate Matrix Data'


Prior to adding the new parameters when the job ran the NBI_PROJ_RES_RATES_AND_COSTS table was truncated, then rebuilt with the most current rate information.

 

For large datasets the rebuilding of the table could take several minutes to hours. During this time projects did not have access to the information which lead to resources having a rate of zero.

 

We added the two new parameters so the NBI_PROJ_RES_RATES_AND_COSTS table will always have data available for use by the schedulers.


Rate Matrix Extraction job runs SQL Stored Procedure CMN_JOB_RATE_MATRIX_SP

which further executes

NBI_PROJ_RATES_AND_COSTS_SP

which

INSERT INTO NBI_PROJ_RES_RATES_AND_COSTSCP

(PROJECT_ID, RESOURCE_ID, TASK_ID, TEAM_ID, CHARGE_CODE, PROJECT_CURR_CODE, FROM_DATE, TO_DATE, LAST_MODIFIED_DATE, EXCHANGE_RATE_TYPE )


when looking at the code once NBI_PROJ_RES_RATES_AND_COSTSCP is updated NBI_PROJ_RES_RATES_AND_COST is truncated and there does not seem to be any option based whether of not Update Rate Matrix Data option was selected.


Looking at the timing with the prepare option only the job seems to take as long as it takes when also the update option is selected.

Thus it appears that if you choose to run the job twice the total run time is longer than if you run it only once with both options selected.

Any experience on that?

The difference is that if you run the job twice the data is there most of the time and not being modified or displaying as zero rates.

The performance on the system then is affected by the job longer if you do the rate matrix extraction in separate prepare and update steps.

Any experience on that?

Outcomes