Better performance Win - Break up to reference calcs from system
Imagine I have a long IF THEN ELSE formula that has Item(List member) showing up often.
Would I have a better performance if….
- Item(List member) was referenced from system module
or
2. IF THEN ELSE was broken up
Best Answer
-
Break your nested If Else statements to multiple line items is always a better performance win.
1
Answers
-
Thank you!
0 -
What if we have a text function, let's say, FIND vs. IF statements?
For example, we use FIND(line_item, "ABC", 1) eight times and also have four nested IF statements. I believe it’s best to improve your model in two steps:- First, create a line item that calculates FIND(line_item, "ABC", 1) just once.
- After that, simplify the three nested IF statements to make your model easier to understand and faster to run.
In real life, I would first create the line item that repeats too many times, and then work on the IF statements.
What do you think?Additionally, what if we change the usage to FIND(line_item, "ABC", 1) ten times but reduce the nested IF statements to three, would we still prioritize simplifying the IF statements?
Regarding the trade-off between a text function like FIND() and an IF statement, how do we determine which one is less memory efficient when the number of repetitions changes?
0 -
@rob_marshall Any insight on @seymatas1? I talked to a few people and people give different answer. And only 1 can be the right answer.
0 -
@JaredDolich We need an expert help here!
1 -
@seymatas1 What an awesome hypothetical to work out. As you know, this would require knowledge about one of two methods: empirical or knowledge of how the calculation engine works. Of course, @rob_marshall knows both, but definitely the second way.
As for my limited, and probably, uninformed answer to this, I would say any text function is going to be a burden on performance. If you can perform any of this outside of Anaplan, do it and import to the data hub. But what I can tell you for sure is that you do not want to nest IF statements beyond two. Thing PLANS, think supportability and readability.
You want a real life example? Check this formula out. This unfortunately is from a real implementation I had to optimize. So, if you want the best reason not to nest IF statements, consider how you, as a new modeler to this implementation, would support something like this? Think of the emotional impact, the stress, the disparaging remarks customers make about Anaplan. What other issues do you see in this formula besides nested IF statements?
Please don't nest IF statements beyond two. And reference as often as you can - best case for system modules, and candidly, the best way to ensure top performance. Also, avoid chaining and exit IF statements based on the most likely outcome first.
1 -
It really is hard work with these hypotheticals, but generally TEXT is evil and can cause performance issues. Additionally, having deep IF THE ELSE statements can be problematic as well, not only from performance issues, but as @JaredDolich stated, maintainability. I would suggest reviewing these two articles for more guidance.
2 -
Yeah this hypothetical is very difficult to figure out which one is the right answer given Anaplan resources.
Either
1) Reference system modules and calculate once
OR
2) Break up IF then ELSE and break up large formulas
is the correct answer.
Unfortunately, in an exam, this requires only 1 right answer and is not multi select, which feels like an unfair and flawed question. I wish there was a way to review and discuss unfair and vague questions….
I still do not know which is the right answer and I do not know how to be 100%. Anaplan does to give users simple calculation performance metrics, so I cannot test this myself with real data… :(
0 -
Yeah, feel free to ask me about the exam in a DM as I wrote the vast majority of it. Not sure how the exam got brought into this as the OP was just asking about text performance, but to answer your question you should calculate once, reference many times (reduces the total number calculations in the model). As for the long IF THEN statements, it is not to break them up, but to attempt early exits and do the same logic dimensionally, instead of the long statement (see what I attached above).
1
