Temporary table cache

The Temporary table cache view of the PPA allows you to browse the cache in which MDB stores temporary in-memory tables representing, for example, worksheets in links, merged worksheets, and the results of various operations such as <table>, <transpose>, and <loop> (for some modes).

Temporary tables are designated in the system with a "path" (more accurately a "handle," as they may not be referenced directly from user-submitted queries) in the form .db.temp_tables.tn for integer n≥0. They may appear in cached MDB operations as the base table of a query or, for example, in the table2= attribute of a <link> operation. To search for queries in the MDB state cache which reference a temporary table displayed in the current view, click the Search state cache for references button. Conversely, temporary table handles are highlighted throughout the PPA (in the MDB state cache view and in the Temporary table cache view itself). To look up a handle, simply click the highlighted link.

If invoked via a link, the Temporary table cache view will show the operations for a specific temp table, along with the total time used to create the table and its size (rows, columns, and actual memory used in MB). The Find specific temp table drop-down list contains the handles of cached temp tables and can be used to select a different table. In addition, if the drop-down list is reset to (All), then two additional filtering options are available. The Find by temp table type drop-down list will filter the tables by type: worksheet for tables generated as the worksheet inside, e.g., a <link>, and other types which in general indicate the operation that created the temp table (e.g. merge, table, loop, transpose, etc). Furthermore, the Find by query text field may be used to search for all temp tables whose query contains arbitrary text. When the view is being used in all tables/filter mode, you can use the navigation buttons <<, <, >, and >> ("first," "previous," "next," and "last," respectively) to browse the set of matching tables.

For some temporary tables (e.g. worksheets in links) that were generated directly from MDB ops, a button Show ops in state cache appears in the temp table display. This will show the cached operations in the MDB state cache view that were run to generate the temporary table, and performance of these operations can be further investigated from that view. It's important to understand that the query in the state cache may not reflect the full sequence of operations that generated the temporary table, because ordinarily only operations that create new states (e.g. selections and tabulations) yield a new cache state. So, for example, for a worksheet that consists of a selection and a tabulation followed by some computed columns, the query shown in the state cache view may only show the selection and the tabulation. Also, the time to create a temporary table will in general be strictly longer than the time associated with the cached operations, both because of these potential additional, uncached operations, and because it may include the time to get the data from the subprocesses if the worksheet did not include a tabulation or similar operation.

Figure 1. Temporary table cache

Find specific temp table
Select a base table from the drop-down list.
Find by temp table type
Select the type of temporary table from the drop-down list.
Find by query text
Enter text that is in the query.
Arrow keys
Browse through the temporary tables.
Temp table
This section shows details about the temporary table cache, including the following information.
  • Temp table name
  • Click Search state cache for references to find references to the temporary table.
  • Click Show data to view the data of the temporary table. You can then view the data in TRS, if desired.