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.
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.
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:
|44% - 77%
How to Enable/Disable it
Enabling for the Entire Workbook
Table Caching can be enabled for an entire report by including +CacheTables in cell A1:
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")
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.
(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.