Calculation testing
Calculations are essentially queries running on the database. When any tuning changes are made to calculations, the database undergoes preparatory operations to identify the most optimal way of collecting and fulfilling that query. The result of these operations is called the execution plan, and it is cached in memory to avoid incurring the same overhead time on subsequent runs. Due to the generation of the execution plan, the first run time after a calculation change is typically longer than the second or third runs, where the plan is cached.
Recommendations for testing tuned calculations
Run multiple times: It is recommended to run the tuned calculation multiple times rather than concluding if the changes were successful or unsuccessful based on the first run. This approach accounts for the initial overhead due to execution plan generation.
Mimic production environment: Ideally, import data changes to mimic the Production environment when testing the tuned calculations. This provides a more accurate measure of performance improvements.
Handling data for test runs: Due to the challenge of obtaining incremental files for multiple test runs, non-incremental calculations are often executed. Make sure that these non-incremental runs are designed to capture the performance gains accurately.