Table Cache Optimization

This information applies to Jet Reports versions 2015 and higher

Overview

Table Cache optimization benefits users by improving the report performance of many reports - especially for Web Services data source connections.

The purpose of this optimization is to retrieve (at one time) as much of the data that will be required by the replicating functions within a report as practical.  Jet functions can then use this "cached" data during the rest of the report run, rather than accessing the database directly.

How it Works

When a report is run, right before any row replicators are expanded, Table Caching is performed. Unlike the batch function optimization (which is run each time a replicating function is evaluated), Table Caching is performed only once before any row replicators are expanded.  Table Caching only applies to Jet functions within row replicators.

Phases

There are 2 distinct phases to Table Caching: "Function Analysis" and "Data Caching".  A function may be excluded from Table Caching during the analysis phase for various reasons.  If it is excluded, that function will simply query the database, as normal, for the rest of the report.

Function Analysis Phase

During the function analysis phase, Jet functions within row replicators are calculated.  Jet compiles the data queries for these functions, by table, for each data source and company. Functions that are valid for Table Caching are NL, NF and Dynamics NAV GL functions.

Data Caching Phase

During the data caching phase, for each data source and company used in the report, the data queries that were valid for caching for each table are combined, eliminating or combining filters as appropriate.

After all functions have been combined for a particular table, a lookup query [similar to an NL(Table) function] is performed to retrieve a set of all the data needed to execute all the functions that were valid for Table Caching for that table.  Then the data retrieved is stored in an in-memory database table created specifically for that data source and company.

After all data has been cached, the report run is completed.  During the rest of the report run, if functions are valid for Table Caching, data will be retrieved from the in-memory database the Jet Excel add-in created, which can much faster than executing them against the regular database, which may exist on a different computer on the network or even across the Internet.

Results

The term "Latency" is used to describe the delay in getting data from one point to another. Generally the higher the latency between the client computer and the database, the bigger the difference users will see when the Table Caching Optimization is enabled.  Very fast connections would probably see no additional benefit from Table Caching.  NAV Web Service data sources tend to have particularly high latency, so the Table Caching optimization should have a bigger positive effect for users of those data sources.

In running a test bed of reports, we obtained the following results (compared to Jet Essentials 2013 Update 1) when used with a NAV 2013 Web Services connection:

How to Enable/Disable Table Cache Optimization

1. Enabling/Disabling for the Entire Application

   You can enable or disable Table Cache Optimization by selecting the Enable cache tables for NAV/Business Central Web Services data sources option from the Performance tab in App Settings. This application level setting will only apply to Dynamics NAV and Business Central Web Services data sources. If this feature is disabled, you can re-enable the feature on individual reports.

2. Enabling for the Entire Workbook

   Table Caching can be enabled for an entire report by including +CacheTables in cell A1:

   

3. Disabling for the Entire Workbook

   Table Cache Optimization can be disabled for an individual report by including +DisableCacheTables in cell A1.

   

4. Enabling on a Function-by-Function Basis

   You can also enable Table Caching for just selected functions.

   Enabled on a function-by-function basis

   =NL("Rows","Cust. Ledger Entry","Customer No.","Posting Date","1/1/2013..6/1/2013","CacheTables=","TRUE")

   Depending on the speed and structure of your data source, you may want to enable Table Caching for your overall report (using +CacheTables in cell A1), but disable it for a particular function.

   Disabled on a function-by-function basis

   =NL("Rows","Cust. Ledger Entry","Customer No.","Posting Date","1/1/2013..6/1/2013","CacheTables=","FALSE")

Recommendation

Depending on the style of report and the amount of data in each of your tables, Table Caching has the potential to improve – or worsen – the performance of a report. Identify reports which exhibit poor performance and test the Table Caching feature to ensure it provides measurable improvements. Due to their inherently high latency, Web Service data sources will be most apt to benefit from this feature.

Example: if your report includes the NL(Count) function on a replicated line, Table Cache Optimization can result in substantial performance improvement.

Deep-Dive

(details about when Table Caching is and is not applicable)

Anatomy of an NL function:

There are some functions that cannot be cached.  These include:

• NP functions
• GL functions for Dynamics GP
• Functions retrieving data from Cube data source
• NL functions with relative cell references for the 'What', 'Table', 'Field', or 'Filter Field' arguments
• NF functions with relative cell references for the 'Field' or 'Flow Filter' arguments
• NAV GL functions with relative cell references for the 'What' or 'View' arguments
• Functions with relative cell references for the 'Company' or 'Data Source' arguments
• Functions with Calc Fields, Calc Filter Fields, or Calc Filters
• NF functions which retrieve Flow Fields and have Flow Filters
• NL functions that have filters on FlowFilter fields
• NL or GL functions which filter for closing dates (either to specifically filter on a closing date or to exclude closing dates)

Unlike the Batch Function Optimization, Table Caching will generally still work even if Jet functions contain nested Excel functions or are nested inside Excel functions.  However, the relative cell reference rules also apply to Excel functions which are nested inside Jet function arguments.  So an Excel IF function that is nested in the Field argument of an NL cannot have a relative cell reference or else Table Caching will not be performed for that NL function.

In general, filters that are row-absolute cell references such as those to report options are combined (with an OR filter) whereas row-relative cell reference filters are removed.  So for example, suppose the following 2 NL functions existed in a replicator:

When these 2 functions are combined, the G/L Account No. filter is eliminated because it is a relative cell reference.  The Posting Date filters are combined (because they are row-absolute references) so the Posting Date filter ends up becoming "(1/1/2014..1/31/2014)|(2/1/2014..2/28/2014)".

The Jet Excel add-in tries to preserve whatever filters it can so that the data set retrieved is not too large.  In NAV data sources, filters cannot grow beyond a certain length.  If a filter gets too long, the Jet Excel add-in will try to find the first and last value being filtered for and use them to create a single range filter, if possible.  If this is not possible, then the entire filter will be eliminated and a larger data set will be retrieved.