Nested IFs (early exits) vs. OR/AND?

I'm curious what others' thoughts are, or if there's been any testing done regarding the performance difference between nested IFs vs. OR/AND. I found a very old thread on this topic from 2014, but I expect the thinking & research has progressed a lot since then.

 

Let's say I have multiple criteria I need to check in order to produce a final determination. Using careful construction of nested IFs, I can allow early exits from the formula, which supports the P (Performance) rule of PLANS, however the resulting formula can be very long and convoluted. Using OR/AND results in a much simpler formula (L - Logical and A - Auditable in PLANS) but does not allow for early exits. In situations where one case is much more common than other(s), I imagine this could make a significant difference, especially over long timescales, many dimensions, etc.

 

What do you prefer to use?

Best Answer

Answers

  • @rob_marshall To be clear, when you say break it up, do you mean into separate line items? I agree and definitely try to do that where space allows. However, I am more interested in the difference in constructing the final formula.

     

    For instance, if I have conditions with the following results:

    example.png

    and I want a 1 where any of the 3 are true, I can do:

    "if A OR B OR C then 1", or

    "if A then 1 else if B then 1 else if C then 1"

     

    Is there a performance difference between these?  My gut reaction is that the first would be slower since it has to evaluate all 3 conditions every time, but maybe that's not the case.

     

    @nathan_rudman Thanks for the response! I'm not sure what the real performance difference is (just my guess as above, which might be wrong) and find it very difficult to nail down, particularly in a general case like this where it's less about optimizing 1 terrible formula and more making sure I take the right approach because small differences add up. If you have any tips for measuring performance difference, I would love to hear them!

  • This is really useful to know!
  • Thank you @ben_speight; this is super handy to know and exactly what I was looking for.

     

    Thanks all for the thoughtful replies! I had posted a follow-up on Friday but it looks like it was deleted somehow; not sure why that happened.

  • @helennie ,

     

    in your example, I believe "if A OR B OR C then 1" will be faster than "if A then 1 else if B then 1 else if C then 1" because the 2nd one has to go through the three different IF statements whereas the first does not.

     

    Rob