Chapter 5 Optimizing Query Performance (1)

InterSystems SQL automatically uses the query optimizer to create query plans that provide the best query performance in most cases. The optimizer improves query performance in a number of ways, including determining which indexes to use, determining the order of evaluation of multiple AND conditions, determining the order of tables when performing multiple joins, AND many other optimization operations. You can provide a “hint” to the optimizer in the FROM clause of the query. This chapter describes the tools you can use to evaluate query plans and modify how InterSystems SQL will optimize specific queries.

The InterSystems IRIS®Data Platform supports the following tools for optimizing SQL queries:

  • SQL Runtime StatisticsUsed to generate runtime performance statistics for query execution
  • Indexer, a variety of indexer reports for displaying all queries in the current namespace. This shows how InterSystems SQL will execute the query, giving you a complete picture of how the index is used. This index analysis may indicate that one or more indexes should be added to improve performance.
  • Query execution Plan: Displays the best (default) execution plan for an SQL query (query plan), with optional alternate query plans and statistics for that SQL query. Tools for displaying query plans include SQLEXPLAINThe command,$SYSTEM.SQL.ExPlan()Methods as well as in the administrative portal and SQL ShellShow PlanTool. The query plan and statistics are generated when the query is prepared and do not require query execution.

The following options can be used to guide the query optimizer by setting config defaults or coding the optimizer “hints” in the query code:

  • The index optimization option provided in the clause option for managing all conditions, or before a single condition%NOINDEX.
  • The comment option specified in the SQL code causes the optimizer to override the system-wide compilation option for the query.
  • Parallel query processing available on a per-query or system-wide basis allows multi-processor systems to divide query execution between processors.

The following SQL query performance tools are described in other sections of this manual:

  • Caching queries enables dynamic SQL queries to be re-run without the overhead of preparing queries each time a query is executed.
  • SQL statement to retain the latest compiled embedded SQL query. In the chapter “SQL Statements and freezing plans.”
  • Freeze plans to preserve specific compilation of embedded SQL queries. Use this compilation instead of a newer one. In the chapter “SQL Statements and freezing plans.”

The following tools are used to optimize table data so that they can have a significant impact on all queries run against the table:

  • Defining an index can significantly speed up access to data in a particular index field.
  • ExtentSize,SelectiveandBlockCountUsed to specify table data estimation before populating the table with data; This metadata is used to optimize future queries.
  • Tune TableUsed to analyze representative table data in populated tables; The generated metadata is used to optimize future queries.

This chapter also describes how to write query optimization plans to files and how to generate SQL troubleshooting reports for submission to InterSystems WRC.

Management portal SQL performance tool

The IRIS management portal provides access to the following SQL performance tools. There are two ways to access these tools from the ADMIN Portal System Explorer option:

  • Select tools, then select SQL Performance Tools.
  • Select SQL, then select the Tools drop – down menu.

From either interface you can choose one of the following SQL performance tools:

  • SQL runtime statistics to generate performance statistics for query execution.
  • Indexer, a variety of indexer reports for displaying all queries in the current namespace. This shows how InterSystems SQL will execute the query, giving you a complete picture of how the index is used. This index analysis may indicate that one or more indexes should be added to improve performance.
  • Alternate display plan: Displays available alternate query plans and statistics for SQL queries.
  • Generate reports to submit SQL query performance reports to InterSystems Worldwide Response Center(WRC) Customer support. To use this reporting tool, you must first obtain a WRC tracking number from the WRC.
  • The import report allows you to view SQL query performance reports.

SQL runtime statistics

You can use SQL runtime statistics to measure the performance of SQL queries running on your system. SQL runtime statistics measure the performance of SELECT, INSERT, UPDATE, and DELETE operations, collectively known as query operations. SQL runtime statistics (SQL Stat) are collected when a query operation is prepared. See Using SQL runtime statistics tools.

By default, collection of SQL runtime statistics is turned off. Statistics collection must be enabled. It is strongly recommended to specify a timeout to end statistics collection. After statistics collection is enabled, existing dynamic SQL queries must be recompiled (prepared) and classes and routines containing embedded SQL must be recompiled.

Performance statistics include ModuleName, ModuleCount(the number of times the module is called), RowCount(the number of rows returned), TimeSpent(execution performance, In seconds), GlobalRefs(number of global references), LinesOfCode(number of rows executed), and ReadLatency(disk read access time in milliseconds).

You can explicitly clear SQL Stats data. Clearing the cache query deletes all associated SQL statistics. Deleting a table or view deletes all associated SQL Stats data.

Note: System tasks are automatically run hourly across all namespaces to aggregate process-specific SQL query statistics into global statistics. Therefore, global statistics may not reflect statistics collected within an hour. You can use the administrative portal to monitor this hourly aggregation or force it to happen immediately. To see when this task was last completed and when it will be scheduled next, select System Operations, Task Manager, Task Scheduling, and then view the update SQL query statistics task. You can click a task name to view details about the task. In the Task Details display, you can use the Run button to force the Task to be executed immediately.

Use the SQL runtime statistics tool

Performance statistics for system-wide SQL queries can be displayed from the management portal using either of the following methods:

  • Select System Explorer, Select Tools, select SQL Performance Tools, and then select SQL Runtime Statistics.

  • Select System Explorer, select SQL, and then select SQL Runtime Statistics from the Tools drop down menu.

Settings

The Settings TAB displays the current system-wide SQL runtime statistics setting and the expiration time of this setting.

The Change Settings button allows you to set the following statistics collection options:

  • Collection options: Statistics collection options can be set to 0, 1, 2, or 3.0. 0= Close statistics code generation; 1= Turn on statistics code generation for all queries, but do not collect statistics; 2= Only query statistics for external loops (statistics collected when the main module is turned on and off); 3= Records the module-level statistics of all queried modules.
    • From 0 to 1: After changing the SQL Stats option, routines and classes containing SQL need to be compiled to perform statistical code generation. For xDBC and dynamic SQL, the cache query must be cleared to force code regeneration.
    • To change from 1 to 2: Simply change the SQL Stats option to start collecting statistics. This enables SQL performance analysis to be enabled in a running production environment with minimal outages.
    • From 1 to 3(or from 2 to 3) : After changing the SQL Stats option, routines and classes containing SQL need to be compiled to record all module-level statistics. For xDBC and dynamic SQL, the cache query must be cleared to force code regeneration. Option 3 is typically only used for poor performance queries that have been identified in non-production environments.
    • From 1, 2, or 3 to 0: To turn off statistical code generation, there is no need to clear cached queries.
  • Timeout option: If the collection option is 2 or 3, you can specify timeout by time spent (hours or minutes) or by completion date and time. Running time can be specified in minutes or hours and minutes; The tool converts the specified minute values into hours and minutes (100 minutes =1 hour 40 minutes). The default value is 50 minutes. The date and time options default to before midnight (23:59). It is highly recommended that you specify a timeout option.
  • Reset option: If the collection option is 2 or 3, you can specify the collection option to reset to when the timeout value expires. The options are 0 and 1.

Query test

The Query test TAB allows you to enter SQL query text (or retrieve it from history) and then display SQL statistics and query plans for that query. The query test includes all module-level SQL statistics for the query, regardless of collection option Settings.

Enter an SQL query text, or use the Show History button to retrieve one. You can clear the query text field by clicking the circular “X” circle on the right.

Execute using the Show Plan With SQL Stats button.

By default, “Run the Show Plan process” in the background check box is not selected, which is the preferred setting for most queries. Select this check box only for long, slow-running queries. When this check box is checked, you will see a progress bar that says “Please wait…” The news. When running a long query, the Show Plan with SQL Stats and Show History buttons disappears and a View Process button is displayed. Clicking View Process opens the Process details page in a new TAB. On the process details page, you can view the process and pause, resume, or terminate the process. The status of the process should be reflected on the display schedule page. When the process is complete, the display plan displays the results. The View Process button disappears, and the Show Plan and Show History buttons with SQL Stats reappear.

The statement text displayed using the query test includes comments and does not perform text substitution.

Viewing Statistics

The View Stats TAB provides an overall View of the runtime statistics collected on this system.

You can click on any View Stats column header to sort query statistics. You can then click the SQL statement text to view the detailed query statistics and query plan for the selected query.

The statement text displayed with this tool includes comments and does not perform text substitution. The statement text displayed by ExportStatsSQL() and Show Plan removes comments and performs literal substitution.

Clear statistics button

Clear Statistics button Clears all accumulated statistics for all queries in the current namespace. It displays a message on the SQL runtime statistics page. If successful, a message is displayed indicating the number of statistics that have been cleared. If there are no statistics, there are no messages to clear. If the cleanup is unsuccessful, an error message is displayed.

Runtime statistics and display plans

The SQL Runtime statistics tool can be used to display a display plan for queries that contain runtime statistics.

You can use the Alternate Show Plans tool to compare display Plans with statistics to display queried runtime statistics. The alternate display plan tool displays estimated statistics for the query in its display plan options. If collecting runtime statistics is enabled, its Compare Show Plans with Stats option will Show the actual runtime statistics; This option displays estimated statistics if run time statistics are not active.