Forum Discussion

FabioG's avatar
FabioG
New Contributor III
2 months ago

SQL Optimization

Hi all,

just finished to see the "Mining for Gold: SQL Parametrization", very informative episode. At the end, Chris Loran explained how to leverage on caching system offered by "globals", to avoid multiple executions of the same query against the DB. 

I do have a couple of questions about globals, to better understand how they work:

Is globals object exclusively dedicated to caching data ? (AFAIK: Yes, it is)

I haven't found a "duration" of the stored key: it's me or it doesn't exists ? I mean, once I put something in the globals, when it will be invalidated ? (AFAIK: No timed duration of the cached value)

Deleting keys: looks like I can only delete ALL keys, not specific keys. Am I right ? (AFAIK: I am right)

I already added answers to my questions, thanks in advance for those who will confirm (or not) them.

FabioG

Data Sources
  • ChrisLoran's avatar
    ChrisLoran
    Valued Contributor
    2 months ago

    So, yes the globals is used for caching data in memory, particularly where you want subsequent passes of a business rule to 'remember' some information that a previous pass has done.  Examples are:
    - in a Finance BR, where you want to cache information say about legal ownership read from a register table. During consolidation, as it moves between different Cons members, entities and time periods, you can share cached information between the calculation of each data unit.  So reading a table once is faster/more efficient than having each data unit query the table each time.
    - in a DynamicCalc, where the DynamicCalc formula is excuted (in parallel) for each cell that is about to be displayed in the cube view. You certainly wouldn't want to query tables on every cell.  I like the pub analogy ; it's more efficient for one person to buy a round of drinks for 10 people, as opposed to each person having their own beer tab.

    In terms of persistence, the globals object persists (stays valid) in these cases
    - between Finance calculations on each data unit
    - between DynamicCalc executions on each cell in a cube view 
    - some other cases
    but there are some cases, where annoyingly you cannot use globals to pass data to/from another BR pass. One example is if you have an extender rule , which initiates a Finance BR , using BRApi.Utilities.ExecuteCustomFinanceBusinessRule( ).   Unfortunately this API ( ExecuteCustomFinanceBusinessRule ) supplies a null value ( nothing ) to the finance BR.  Which means you cannot use globals to share values between an Extender rule and a Finance rule.  That is a shame.

    After consolidation process has completed, I don't know whether the globals is explicitly disposed of , or whether the framework relies on the .NET garbage collector to do it.  Whatever the case,  don't store huge amounts of information in globals. e.g. a Data Table or (better) a Dictionary of 1000 items willl likely be fine, but not several million items.

    As for the question on deleting all keys, vs specific keys, what are you referring to?  Data Table Manager?

  • FabioG's avatar
    FabioG
    New Contributor III
    2 months ago

    Hi Chris,

    thanks a lot for you (very) quick answer. 

    I agree with You, never put too much data in cache, it may be inefficient.

    I'm doing some experiment with globals, I'm trying to understand how they works. They are somehow managed by the OS's objects lifecycle, I guess. So there's no need to keep keys alive for a bunch of time (like Redis does), and there's no need to delete single stored keys (the Clear method removes ALL the keys, a sorto of cleanup)

    While I'm learning OS, I'm trying to figure out if a Cache Server like Redis would help, just an idea.

    Thanks again for Your time.

    Best regards

    FabioG