Performance affect of "SELECT:" and "LOOKUP:" used together

Hi community!

 

I couldn't find any information on whether it's recommended to split SELECT and LOOKUP for better performance. This thread helped me to understand that SELECT works in a similar way as LOOKUP (thanks to @DavidSmith  for insight). So does it mean that SELECT and LOOKUP can be used together, unlike SELECT and SUM?

 

And what is the overall performance affect of SELECT compared to LOOKUP? Should it be avoided if possible? Or it has almost no affect on calculation time?

 

Thanks,

Haik

Answers

  • Hi @Hayk!

     

    Good question. I find this information in Planual:

     

    Exception:

    2.02-08a The LOOKUP is a constant: Where the LOOKUP line item is a constant, the formula will operate as a SELECT, so the performance implications are negated

     

    The main restrictions on the use SELECT are not related to performance, but to the ALM configuration and development rules (less hardcode).
    I have not heard of any negative consequences in combining formulas.

  • UPD: I find this video.

     

    But in my understanding, the problem is still not in performance, but in the cleanliness and correctness of development.

     

    Снимок экрана 2021-04-21 в 13.51.07.png

  • @Hayk 

     

    Technically you can use SELECT with any dimension but as per the best practice SELECT should be used only with Anaplan Native Versions ,Top Levels, YTD and YTG. Before I explain further I assume you have good understanding on PLANS methodology.

     

    When you use SELECT on any non Production List you are hard coding it  and Anaplan wants us to get away with the hard code as it will not follow Scalable/Sustainable (S) element of PLANS

    When you try it to use it on Production list Anaplan will either not allow you to use SELECT on Production list ( if you have already marked the list as production) or else Anaplan will not allow you to mark the list as Production list if SELECT has already been used on the list  - both times error message pops up.

     

    LOOKUP Modules or Constant Modules is better way of modelling as it doesn't breach S element of PLANS methodology. As far as performance is concerned I feel SELECT will be better than LOOKUP , although I don't  have data to back myself up.

     

    Avoid using these Compound Functions ( SUM & SELECT, LOOKUP & SELECT and SUM & LOOKUP) together in one line item. SUM & LOOKUP is the worst performing compound function - that's proven already and I believe LOOKUP and SELECT should be the second in line  ( Not Proven). 

     

    Misbah

  • @Hayk SELECT and LOOKUP work in a similar way and, in my opinion, there is no difference in terms of "calculation performance". 

    However there are other reasons that I would try to use LOOKUP over SELECT and the reason is not the performance, but the parametrization and flexibility of the formulas. 

     

    My personal interpretation is this: SELECT is the hard-coded version of LOOKUP. 

     

    The SELECT refers to a particular element from a list and needs to be hard-coded.

    If for some reason, the formula needs to refer to another element, the only way to solve this is to change the formula (structural modification)... if LOOKUP is used: just change the value of a cell (new mapping) and the formula will return the new desired value. 

     

    I try to use SELECT only:

    - to refer in a formula a particular Anaplan Version different from the "Current Version"

    - to refer Top-level elements from a List

     

    All reasons that do not have anything to do with the performance... 🙂

     

    Hope it helps

    Alex

  • Olek P
    edited June 2023

    I had first used SELECT for a list member. However after reading this I tried to replace the SELECT with a LOOKUP and the formula wouldn't work. So LOOKUP & SELECT aren't exactly the same thing? In my case I can SELECT the list member but can't look it up. Any thoughts?