-
Row count: For a DDL statement such as CREATE TABLE, displays Row count: 0 if the operation was successful. For a DML statement such as INSERT, UPDATE, or DELETE, displays the number of rows affected.
For a SELECT, displays the number of rows returned as a result set. Note that the number of rows returned is governed by the Max setting, which may be lower than the number of rows which could have been selected. For multiple result sets, the number of rows for each result set are listed, separated by the / character. A query that specifies one or more aggregate functions (and no selected fields) always displays Row count: 1 and returns the results of expressions, subqueries, and aggregate functions, even if the FROM clause table contains no rows. A query that specifies no aggregate functions and selects no rows always displays Row count: 0 and returns no results, even if the query specifies only expressions and subqueries that do not reference the FROM clause table. A query with no FROM clause always displays Row count: 1 and returns the results of expressions, subqueries, and aggregate functions.
-
Performance: measured in elapsed time (in fractional seconds), total number of global references, total number of commands executed, and disk read latency (in milliseconds). If a cached query exists for the query these performance metrics are for executing the cached query. Therefore, the first execution of a query will have substantially higher performance metrics than subsequent executions. If the specified query returns more than one result set, these performance metrics are totals for all of the queries.
To analyze these performance metrics in greater depth you can run MONLBL (the monitor line-by-line utility) and specify the Routine Name using the asterisk wildcard as %sqlcq*. Refer to Examining Routine Performance Using ^%SYS.MONLBL.
-
Cached Query: the automatically generated cached query class name. For example, %sqlcq.USER.cls2 indicating the second cached query in the USER namespace. Each new query is assigned a new cached query name with the next consecutive integer. By clicking this cached query name, you can display information about the cached query and further links to display its Show Plan or to Execute the cached query. (For a DDL statement, see SQL Commands That Are Not Cached.)
Closing the Management Portal or stopping InterSystems IRIS does not delete cached queries or reset cached query numbering. To purge cached queries from the current namespace, invoke the %SYSTEM.SQL.Purge()Opens in a new tab method.
Not all SQL statements result in a cached query. A query that is the same as an existing cached query, except for literal substitution values (such as the TOP clause value and predicate literals) does not create a new cached query. Some SQL statements are not cached, including DDL statements and privilege assignment statements. Non-query SQL statements, such as CREATE TABLE, also display a cached query name. However, this cached query name is created then immediately deleted; the next SQL statement (query or non-query) reuses the same cached query name.
-
Frozen state: if a query plan is frozen, the frozen plan state (in parentheses) is displayed after the cached query class name. For example (Frozen/Explicit). If the query plan is not frozen, nothing is displayed here.
-
Last update: the date and time that the last Execute Query (or other SQL operation) was performed. This timestamp is reset each time the query is executed, even when repeatedly executing the identical query.
-
Successful execution also provides a Print link that displays the Print Query window, which gives you the options to either print or export to a file the query text and/or the query result set. The clickable Query and Result toggles enable you to display or hide the query text or the query result set. The displayed query result set includes the namespace name, the result set data and row count, a timestamp, and the cached query name. (Note that the timestamp is the time when the Print Query window was invoked, not the time when the query was executed.) The Print Query window Print button prints a screenshot of the Print Query window. The Export to File check box displays options to specify an export file format (xml, hdml, pdf, txt, csv) and an export file pathname. The Export option ignores the Query and Result toggles and always exports only the result set data (by default to: exportQuery.pdf) and the row count (by default to: exportQueryMessages.pdf); the query text, namespace, timestamp, and cached query name are not included.
