I was working through a long running FX calculation and had pretty much exhausted all of the obvious touch points. After turning up SET MSG DETAIL I saw the following message pop into the log
The calculation is broken into three primary sections utilizing one for Net Income Accts that utilizes an Average FX Rate, a Balance Sheet section utilizing a Prior Period Closing FX Rate, and the problematic section which focused on a single Account, Retained Earnings, which used the January value for Prior Period Closing FX. In order to call out that single Account member, the @ISMBR function was used. I was running this calculation for a single Entity so I knew the performance should be relatively quick. After a little research on the interwebs, it became apparent that the @ISMBR function was causing the calculation to run in Cell mode resulting in a total run time that looked like this:
Over 32 mins for FX calculation is not going to cut it. I switched out the @ISMBR reference to utilize a newly created UDA instead of the function:
The performance increase?
91% decrease in processing time due to eliminating the Cell mode processing with the same results! Pretty simple workaround for such a decrease in processing time.
The calculation is broken into three primary sections utilizing one for Net Income Accts that utilizes an Average FX Rate, a Balance Sheet section utilizing a Prior Period Closing FX Rate, and the problematic section which focused on a single Account, Retained Earnings, which used the January value for Prior Period Closing FX. In order to call out that single Account member, the @ISMBR function was used. I was running this calculation for a single Entity so I knew the performance should be relatively quick. After a little research on the interwebs, it became apparent that the @ISMBR function was causing the calculation to run in Cell mode resulting in a total run time that looked like this:
Over 32 mins for FX calculation is not going to cut it. I switched out the @ISMBR reference to utilize a newly created UDA instead of the function:
The performance increase?
91% decrease in processing time due to eliminating the Cell mode processing with the same results! Pretty simple workaround for such a decrease in processing time.
Comments
Post a Comment