Table Cache – Improving report performance when using the NAV Web Services Connector

Sunday, July 24, 2016 - By Jon Gardner

Table Cache improves the performance of many reports, especially when using the web services data connection. The functionality of Table Cache is to retrieve (one table at a time) as much of the data that will be required by the replicating functions within a report as practical. Jet Essentials/Professional functions can then use this cached data for the report, rather than accessing the database on multiple occasions or as required.

This functionality applies to Jet Essentials 2015 and Jet Professional only.

The issue with web services is ‘latency’, the time it takes to get data from the database to the desktop and Excel.  In general terms the greater the latency between the database and the client computer the bigger the difference users will see when Table Cache is enabled.  NAV web services tend to have particularly high latency, so the use of Table Cache should have a bigger positive effect – indeed Jet themselves quote a 44 – 77% improvement, averaging at 61%.

Table Cache can be enabled in a number of ways – either for an entire report or on a function-by-function basis –

• To enable the entire workbook go to Cell A1 of the first page of the report and enter “Auto+Hide+CacheTables”
• To enable Table Cache for just selected functions, enter Filter Field as “CacheTables” and the Filter as “True”

Depending on the style of the report and the amount of data in each of your tables, Table Cache has the potential to improve significantly the performance of a report. We would suggest that you should identify reports which exhibit poor performance and test the Table Cache functionality to ensure that it does indeed provide measurable improvement; particularly if you use the NAV web services connector with its inherently high latency.  A classic example where Table Cache shows significant improvement is if the report includes the NL(Count) function on a replicated line.

It is worth pointing out that Table Cache does not work in a number of situation and if invoked could potentially worsen performance –

• NP functions
• NL functions with relative cell references for “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 “DataSource” arguments
• NF functions which retrieve Flow Fields and have Flow Filters
• NL functions that have filters on Flow Filter fields
• NL or GL functions which filter for closing dates

If you wish to learn more about Table Cache and its potential to improve your reports please contact us here – Contact us