Calculation Dependencies – Performance Speed

4.99K viewsGeneral Discussion
0

I just finished building a large model in Quantrix and have been running performance testing to see how it handles large volumes of data across multiple matrices. The purpose of the model is to forecast SKU/week sales, inventory and other metrics for different products.

My primary observation is that the number of items in a category has a direct impact on calculation speed. If I have only one product in the model, response times are about 1 second. When I have ten products, single calculations last about 4 seconds.

This is a bit surprising, since I had hoped that if I am changing inputs on just one product at a time, the model would figure out that the other products aren’t impacted, and calculation speeds would remain low. However, it seems that the calculation dependencies are bleeding over into all the other products, or that the size of the model requires more time to figure out dependencies.

I wonder if there is a way I could reorganize my model to keep each product in a silo, reducing the calculation time?

Here were my model statistics:

[b:1xeazkqr][u:1xeazkqr]For 1 product[/u:1xeazkqr][/b:1xeazkqr]
Matrices: 14
Charts: 0
Categories: 21
Items: 315
Formulae: 129
Cells:
Total: 30,423
Calculated: 23,113
Input: 7,310

[b:1xeazkqr][u:1xeazkqr]For 10 products[/u:1xeazkqr][/b:1xeazkqr]
Matrices: 14
Charts: 0
Categories: 21
Items: 324
Formulae: 129
Cells:
Total: 262,398
Calculated: 229,807
Input: 32,591

0

Mike, you mentioned SELECT as one function that requires more than the usual resources for recalculation. That sparked some curiosity on my part to determine which of the array-like functions are fastest for sums.

I modeled out an example where I want to sum a number between two dates. I could use one of four functions:

1. SELECTBETWEEN
2. SUM
3. SUMIF
4. SUMPRODUCT

All four functions come up with the same answer, but the question is: which is most efficient?

I tested each function in a model with about 500,000 cells. There was no noticable difference in the calculation time (around a second for each function).

I wonder if using a SUM or SUMPRODUCT is more efficient than USING AS for sums with multiple criteria. I’ve always liked SUMPRODUCT (replaced with SUMIFS in Excel 2007) because you can add as many criteria as you like in a fairly straightforward format. SUM in Quantrix works like SUMPRODUCT in Excel because it treats the dimensions like arrays.

I’ve attached a model with the four functions I mentioned.

You are viewing 1 out of 5 answers, click here to view all answers.

Latest Questions