I was going through a best practice video which mentioned that the order of lists in the module can help improve the performance if the lists are ordered similar to their order in General Lists section (because indexing becomes easier for the engine).
I was wondering if there is a similar best practice when it comes to the formulas itself?
Is there an order in which we should be putting the SUM/LOOKUP functions (assuming the formula has 2 lookups together)?
If you are multiplying 2 different numbers say an assumption % (from a small assumptions module) and a total number (from a module of same size), does the order of the selection matter in terms of performance? E.g. Is "Assumption%[Lookup:X] * Total" quicker than "Total * Assumption%[Lookup:X]" ?
Looking forward to the response from the community.
I believe the answer is no. The formula is broken down into the various parts and the SUMs are always completed first., then the LOOKUPs, then the remainder. Also if you have multiple SUMs or multiple LOOKUPs, the order doesn't matter. However, for readability, and consistency, I advocate keeping the order the same. So if your dimension order is Customer, Product, Time and you have a double lookup, I would have something like:
...[LOOUKP:Mapping.Customer, LOOKUP:Mapping.Product] each time you need to use it
It is important though to ensure that the same SUM or LOOKUP is not repeated within the line item formula. If it is, then it should be split in it's own line item; that way the formula is only performed once, not many times.
Also, I'm sure you know that you should avoid SUM and LOOKUP in the same , as per Planual 2.02-08