I am working on an application that has realtime columns and static columns. The realtime columns are constantly updating on the grid every second or so, and the static columns provide basic information on the row (Name, Location, etc)
Our uses like to create lots of formula columns using the formula builder. Some of these formula columns are large nested if, and, or statements that use static column data to produce a result.
The issue that I am running into is that these columns, even when not visible, are causing other formula columns to take an extremely long time to calculate when either a sort or a group by action happens on said formula column.
For example, lets say i have 5 formula columns. 3 of those columns are basic RTcolumnA * RTcolumnB type columns. The last 2 columns (we'll call them formulaColumn1 and formulaColumn2) have nested if blocks that also use AND and OR funcitons in them. formulaColumn1 will also reference formulaColumn2 within its logic.
my questions are:
1.) Is there a way to stop a formula from recalculating when a different formula is being sorted/grouped?
2.) Should the grid be able to handle formulas like the ones i described above (formulaColumn1 and formulaColumn2) with reasonable performance (have sort/group action complete within 2-3 seconds with about 800 rows).
3.) Why is it that this only happens on sort/group actions with other formula columns? We are not performing any logic within the before/after sort or group handlers. I would think we would have the same performance issue on the initial calculation of the cells.
Thanks,
I figured out the issue.
It wasn't the nested if columns afterall. A user had add a fomrula that used the aggregate ([Amount()]) of a calculated column. Since the [Amount] columns was constantly updating (every second or so) due to the data that was coming in, I bet you the sort kept getting invalidated and starting over, which eventually turned into a infinite-loop.
When I took that column out and put the nested If's back in, inital sort would take about 6 seconds more then without the nested if's(only the initial sort). Same with grouping.
Thanks for the help/info.
Hi,
First, what version of the controls are you using? There were some known performance issues when sorting and grouping the grid with formulas. So my first suggestion would be to get the latest service release and see if that helps.
If it does not help, then we would need to track down why it's causing a performance problems.
Bear in mind, though, that the grid is optimized to calculate the visible rows first and that all Calculations in CalcManager are done asynchronously. So if you sort or group a column that is calculated by a formula, every cell in that column has to be calculated synchronously to make sure all of the values are up-to-date before it can be sorted. This means that all dependent formulas need to be calculated, as well. So if you are sorting by formulaColumn2, it means that every cell in formulaColumn2 and formulaColumn1 and any other dependencies that those formulas reference all have to be calculated at once before the sort can occur.
And if, during the sorting, any dependent values change, the entire sort becomes invalid and it has to start all over again.
I'm not sure if there is any way around this. You can disable calculations temporarily on the CalcManager by setting CalcFrequency to Manual. So you could set this in the BeforeSortChange of the grid. But then it gets a little tricky in terms of when to re-enable it. The sorting in the grid is also done asynchronously. So there's no real end-point. So what you would have to do is make the sorting synchronous. The good news is that there's a property for this in the BeforeSortChange event args. So you would use the same event to disable the calculations and make the sorting synchronous. Then you would use AfterSortChange to re-enable the CalcManager. The bad news is that making the sorting synchronous means it will lock up the UI while it is sorting, so this may be a catch-22 and cause another performance issue.