8 Replies Latest reply on May 29, 2018 2:01 PM by TimG

    Horrendeously slow running business rule

    BeginnersMind

      Hi,

       

       

      I have a business rule which runs in seconds when I run it for a single combination of sparse / dense members, i.e. with prompts for cost centre, entity and division - all sparse members

       

      When I try to take the prompts off the script runs for over a day, with no other jobs running and after a restart.

       

      My pain point is that I need to avoid changing data in closed periods, but it is not as simple as just fixing on open periods because I have some Accounts -> Start Month, End Month, Salary Increase Month which I need to compare to the month being processed to see if I need to clear values from that month.

       

      So excerpts of my current script which run like a dog; -

       

       

      fix (@DESCENDANTS("new employees uk"), @relative("Cost Centre",0), @relative("Division",0), @relative("Entity",0), "no_product", "no_project", &curryear, "working", &fcstmonths, "local", "salary", "car allowance amount", "employee status", "new employee notes", "regular headcount", "regular fte", "salary increase", "bonus", "commissions", "employers ni", "pension employers contribution", "home worker allowance costs", "phi costs", "health care costs", "life assurance costs", "shift allowance", "overtime", "other compensation", "employers ni", "non-standard pension contributions")

         

         &currforc (

              IF ("start month"->"BegBalance" == 2 AND @ISMBR("Jan"))

                  &currforc = #missing;

              ELSEIF ("start month"->"BegBalance" == 3 AND @ISMBR("Jan"))

                  &currforc = #missing;

         ELSEIF ("start month"->"BegBalance" == 4 AND @ISMBR("Jan"))

                  &currforc = #missing;

         ELSEIF ("start month"->"BegBalance" == 5 AND @ISMBR("Jan"))

                  &currforc = #missing;

         ELSEIF ("start month"->"BegBalance" == 6 AND @ISMBR("Jan"))

                  &currforc = #missing;

         ELSEIF ("start month"->"BegBalance" == 7 AND @ISMBR("Jan"))

             &currforc = #missing;

         ELSEIF ("start month"->"BegBalance" == 9 AND @ISMBR("Jan"))

                  &currforc = #missing;

         ELSEIF ("start month"->"BegBalance" == 10 AND @ISMBR("Jan"))

                  &currforc = #missing;

         ELSEIF ("start month"->"BegBalance" == 11 AND @ISMBR("Jan"))

             &currforc = #missing;

         ELSEIF ("start month"->"BegBalance" == 12 AND @ISMBR("Jan"))

                  &currforc = #missing;

         ENDIF

          )

          ;

       

       

      --and repeat for the other twelve months.....

       

      --and repeat for other similar but non-identical fix statements

       

      In the above Period (BegBalance, Jan) are dense, Account (start month) is dense and &currforc is a prompt relating to a scenario. &fcstmonths is currently Jun:Dec, but moves as the year moves.

       

      Each forecast has different closed periods, and I really don't want to create separate scripts for each, I want one size fits all.

       

      My system is PBCS, but I am posting here as the knowledge on this site tends to be greater on business rules...

       

       

      Any advice hugely appreciated.

        • 1. Re: Horrendeously slow running business rule
          Larry's Friday Girl

          Hi BM

           

          as a minimum consolidate your IF statements, from what you say you have one of the IF blocks for each month, well why not or the months like this; -

           

           

          &currforc (

                  IF ("start month"->"BegBalance" == 2 AND @ISMBR("Jan"))

                      &currforc = #missing;

                  ELSEIF ("start month"->"BegBalance" == 3 AND (@ISMBR("Jan") or @ISMBR("Feb")))

                      &currforc = #missing;

             ELSEIF ("start month"->"BegBalance" == 4 AND (@ISMBR("Jan") or @ISMBR("Feb") or @ISMBR("Mar")))

                      &currforc = #missing;

           

          etc

           

          Also, whatever is true most often, put that to the top of your statement so it exits the IF fastest.

          1 person found this helpful
          • 2. Re: Horrendeously slow running business rule
            TimG

            Good advice above. Another good way to make scripts that use lots of conditional logic run faster is to find a way to replace the conditional logic with a non-conditional mathematical or logical operation. It does have the downside of making code less easily comprehensible.

             

            That snippet sets Jan to #Missing if start month is anything but 1. I am guessing (only guessing, because I don't know what the whole script looks like) that what you are doing is setting every period to missing except the one indicated by the start month driver.

             

            I think you could potentially write that like this (not tested):

             

            "Jan" = "Jan" /  @MAX(-@ABS("Start Month"->"Beg Balance" - 1) + 1, 0)

            "Feb" = "Feb" /  @MAX(-@ABS("Start Month"->"Beg Balance" - 2) + 1, 0)

            "Mar" = "Mar" /  @MAX(-@ABS("Start Month"->"Beg Balance" - 3) + 1, 0)

            ...

            "Dec" = "Dec" /  @MAX(-@ABS("Start Month"->"Beg Balance" - 12) + 1, 0)

             

            The @MAX...  section is setup to return 1 when "Start Month" 'matches' and 0 otherwise, so you get no change* (divide by 1) for the month you want, and #Missing (the result of divide by zero) for the others.

             

            *There may be a tiny change way out in the decimal places due to some floating point math shenanigans that I don't understand properly, but immaterial in most applications.

            1 person found this helpful
            • 3. Re: Horrendeously slow running business rule
              BeginnersMind

              Hi TimG

               

               

              yes, the logic is; -

               

              IF Start Month > Month Being Examined then set value to #missing

               

              IF Leave Month < Month Being Examined then set value to #missing

               

              IF Salary Increase Month > Month Being Examined then set value to #missing

               

               

              Think I get the gist of your formula, I have seen a similar trick using decode in sql, I will try it out, many thanks for your input!!

               

               

              Btw - I have read that IF works better (faster) on dense than sparse, but I wind up with the logic in question using a sparse member because I needed to apply the logic on so many accounts, in your learned opinion, no sarcasm intended or implied, would using the dense account in the IF make a big difference?

              • 4. Re: Horrendeously slow running business rule
                TimG

                The "IF on dense, FIX on sparse" thing is for when you have a choice of implementing logic via multiple FIX statements or IF statements. E.g. if you want to do one thing to "Jan" and another to "Feb", it is better to do this:

                 

                FIX("2018")

                  IF(@ISMBR("Jan"))

                      "Account" = 123;

                  ELSEIF(@ISMBR("Feb")

                      "Account" = 234;

                  ENDIF

                ENDFIX

                 

                ...than this...

                 

                FIX("2018", "Jan")

                  "Account" = 123;

                ENDFIX

                 

                FIX("2018", "Feb")

                  "Account" = 234;

                ENDFIX

                 

                ...because it results in fewer passes through the blocks (which are expensive, since they may require disk I/O)

                 

                With data-driven logic like yours that rationale doesn't really apply.

                 

                You could still try refactoring this to place &CurrForc in the FIX and applying the entire formula to just one dense member (e.g. "Jan"). There is nothing to stop the content of a formula that is notionally on "Jan" also writing to "Feb", "Mar" and so on. I'm not sure that it will get you much but no harm in testing it.

                 

                Guessing from the reference to NI that you might also be British so I understand the need to explain when you're not being sarcastic.

                1 person found this helpful
                • 5. Re: Horrendeously slow running business rule
                  BeginnersMind

                  Thank you again for the very detailed and comprehensive explanation.

                   

                  And ok, ouch, you got us on the sarcasm... we really are the world leaders on that one.

                  • 6. Re: Horrendeously slow running business rule
                    TimG

                    You're welcome (BTW I'm British too, though I live in the US... ...that wasn't a dig )

                    • 7. Re: Horrendeously slow running business rule
                      BeginnersMind

                      US?

                       

                      Maybe post Brexit that is a really wise move.

                       

                      Though I guess you have Trump, maybe I'll stay put ;-)

                      • 8. Re: Horrendeously slow running business rule
                        TimG

                        Glad this solution worked.

                         

                        And yes, frying pan / fire at the moment....