Optimize Workbook Functions
NOTE - This feature was introduced in version 2.01.0034.
|2.01.0022 and earlier||These versions can not be automatically optimized because they lack square brackets around field names. If the workbook uses only queries (i.e. the workbook does not contain any Office Connector functions) then you can disregard the optimization message. Simply re-save the workbook and the prompts will no longer be displayed. You can suppress all further notices by unchecking Prompt when opening un-optimized workbooks under Advanced preferences.|
|2.01.0023 to 2.01.0033||These versions can be automatically optimized. Save the workbook after optimization is complete to save the changes and prevent future messages.|
|2.01.0034 and above||These versions are already optimized.|
Square Brackets in Filters
Versions of Office Connector prior to 2.01.0023 don't enclose field names in square brackets in filters. The column optimizations in the current version of Office Connector require the column names to be enclosed in square brackets. If any filters are encountered where the columns are not enclosed in square brackets, the entire table is loaded.
To improve performance, edit your workbook function filters to enclose all column names in square brackets. For example, square brackets were added to the column name TJOB in the example below:
=TSSUM("CURRENT_JCT_RECORD_1","TAMOUNT","TJOB='" & A1 & "'")
=TSSUM("CURRENT_JCT_RECORD_1","TAMOUNT","[TJOB]='" & A1 & "'")
Use the SQL Monitor window to determine if a workbook has been completely optimized. When you have addressed all filters for a given table, the SQL will no longer look like SELECT * FROM TABLE. Use the Preferences window to turn the SQL monitor on and off. The once turned on, the SQL Monitor window will not be displayed until a workbook function is evaluated.
NOTE - Partially fixing a filter by putting square brackets around some field names but not others will cause errors to be returned to the spreadsheet until all column enclosers are consistent. This will be a normal experience while optimizing older workbooks.