3305 Main Street Ste 019 Vancouver, WA 98663

SQL Monitor Window

The SQL Monitor window provides real-time feedback that describes the recordsets needed to evaluate Office Connector workbook functions.  Use the Preferences window to show the SQL Monitor.  Turning this feature on will not cause the window to be displayed until a workbook function is evaluated.  Similarly, turning this feature off will not cause the window to immediately disappear - you will have to close it yourself, or it will unload automatically if Excel is closed.

frmsqlmonitor

The SQL Monitor window.

N Loaded Recordsets

The top section lists all of the recordsets currently loaded in memory.  Each entry in the list displays the SQL prefixed by the number of milliseconds that were required to load the data.  There are 1000 miliseconds in a second, so a load time of 1500 would represent one and a half seconds.  If the SQL uses * for the column list, i.e. SELECT * FROM TABLE, then the workbook can benefit from the selective column optimization introduced in version 2.01.0038.

N Table SQL Statements

The bottom section displays the optimization history for each table used by the workbook.  The current SQL is shown first with older versions trailing down.  Use the recordset history to understand how and when optimizations are applied.  Note that when you use the Optimize Workbook Functions toolbar button (on the tech support toolbar), the SQL Monitor window is not immediately updated.  The SQL Monitor window is only updated after a workbook function is evaluated.

Note that using Where technology with workbook functions does not cause a recordset to be loaded.  If you see a recordset loaded for a table that you intended to optimize using Where technology, it indicates that at least one workbook function is referencing the table without using Where technology and you're losing most of the benefit of using Where technology.