MDB state cache

The MDB state cache view of the PPA allows you to browse the cache in which MDB stores completed queries, along with the new database states that they engender, and performance information.

The queries in the cache reflect preprocessing that takes place before the database engine begins executing the query, and therefore queries may not appear in their original form, as submitted or embedded in an application. For example, a <link> to a worksheet is processed as two separate queries, one for the worksheet (inner query) and one for the query linking to it (outer query). The outer query will appear in the cache with a link to a temporary table, and the inner query will appear separately in the cache. Similarly, <merge> is decomposed into separate subqueries and the result is placed in a temp table, which may appear in the cache as the base for subsequent operations. Also, block code, and forms such as <loop>, <do>, and <dynamic> are processed before the database engine is invoked and therefore will not appear in the state cache, although queries run by these forms will.

The cache may be browsed in two modes. When Telescope queries is not selected, each section of a query that corresponds to a new database state will be displayed separately. For context, the previously-cached section(s), if any, for a given section will also be displayed, dimmed, before the section of interest. For example, a query consisting of only two selections and a tabulation would correspond to three separate entries in the cache: one with the <base> operation and the first <sel>, the second with the second <sel> (with the first two operations displayed dimmed), and the third with the <tabu> (with the first 3 operations displayed dimmed). Note that not every operation corresponds to an independent new state.

When Telescope queries is selected, all sections of a query are displayed at the same time, in separate boxes. In this mode, even if two separate queries were run, one of which was a identical to the beginning of the second, only the second (longer) query will be shown. This mode can reduce the number of cache entries that need to be reviewed. All data and performance information is available for every query section in either mode.

By default, cached queries based on in-memory temporary tables are included in the display. It is possible to eliminate these from the display by unchecking the checkbox. This will prevent, for example, operations subsequent to a <merge> operation from appearing. PPA automatically eliminates most, though not necessarily all, system queries (e.g. those issued by the GUI, by tools like the Object Manager, and by PPA itself) to avoid superfluous clutter.The displayed queries may be filtered on three criteria. The Find by base table drop-down list contains a list of all the paths that appear as base tables for the cached queries. Selecting one of these restricts the displayed queries to those based on the specified path. The Find by last cached op drop-down list contains a list of all "final" operations (e.g. sel, tabu, sort), i.e. those appearing at the end of the cached queries. Selecting an operation restricts the displayed queries to those that end with the specified operation.

Important note: When Telescope queries is selected, this will only find the longest queries ending in the specified operation, not shorter initial portions of those queries. Depending on your objective, you may want to turn off telescope mode before using Find by last cached op. It is also possible to search all queries by any arbitrary text string (operations, column names, pathnames, attributes, etc.) by entering the text in the Find by raw query text field. Again, remember that cached queries are preprocessed; you won't find them by entering, for example, block code or tags like <merge> that appear in your original submitted query. Instead, try using expressions, column names, tables, etc. from your query. Use the navigation buttons <<, <, >, and >> ("first," "previous," "next," and "last," respectively) to browse through all queries found with the given filter criteria.