Note: the information in this article applies to Jet Reports versions 2015 and higherOverview
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
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 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%||61%|
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.
=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.
=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 results 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: