0
106
Question Michael Akselrod · Jan 28

How can I programmatically retrieve a SQL query execution plan and analyze its results?

#HealthShare

How can I programmatically retrieve a SQL query execution plan and analyze its results?

Product version: IRIS 2022.1
$ZV: HealthShare Unified Care Record 2023.1.0 Build: 1006 Adhoc: 23407 [HealthShare Modules: Core:25.0] - IRIS for Windows (x86-64) 2022.1.2 (Build 574_0_23407U)
Discussion (6)5
Log in or sign up to continue

Comments

DC AI Bot · Jan 28 Bot

You can retrieve and analyze SQL query execution plans programmatically and through interactive commands using InterSystems tools. Here are the approaches available:

  1. EXPLAIN Command:
    Use the EXPLAIN command in SQL to display the query execution plan for a SELECT query without actually executing the query. Example:

    SQL]USER>>EXPLAIN SELECT Name FROM Sample.MyTable WHERE Name='Fred Rogers'

    This command also allows for generating performance statistics with EXPLAIN STAT or alternate query plans with EXPLAIN ALT [1][2].

  2. SHOW PLAN Command:
    After executing your query, use the SHOW PLAN command in the SQL Shell to display its plan. Alternatively, you can set the execution mode to deferred to view the plan without running the query:

    SQL]USER>>SELECT Name FROM Sample.MyTable WHERE Name='Fred Rogers'
SQL]USER>>SHOW PLAN

    SHOW PLAN works for other statement types, including INSERT, UPDATE, and DELETE operations [1][2].

  3. SHOW PLANALT:
    The command SHOW PLANALT can display alternate execution plans for your query. This is useful for comparing different strategies proposed by the SQL optimizer [2].

  4. ObjectScript Method ($SYSTEM.SQL.Explain):
    Generate query execution plans programmatically using ObjectScript and the $SYSTEM.SQL.Explain() method. This can be integrated into your application workflows for automated analysis purposes [1][2].

These tools provide comprehensive ways to programmatically retrieve and analyze SQL query plans alongside their associated performance metrics, suppressing the need for SQL execution when desired and offering functionalities for debugging and optimization.

Sources:

0
Michael Akselrod  Jan 29 to Luis Angel Pérez Ramos

I did few attempts in Terminal like (having required SQL privileges):

HSACCESS 2e1>EXPLAIN SELECT * FROM Ens.MessageHeader 
EXPLAIN SELECT * FROM Ens.MessageHeader^
<SYNTAX>^%SYSTEM.SQL.1

Am I missing something? Thank you

0
Warlin Garcia  Jan 29 to Michael Akselrod

From your output it's not clear if you're in the SQL Shell. You need to be in the Shell to execute the EXPLAIN command

0
Stephen Canzano  Jan 29 to Warlin Garcia

you can quickly access the SQL shell witj

USER>:SQL
SQL Command Line Shell
----------------------------------------------------
 
The command prefix is currently set to: <<nothing>>.
Enter <command>, 'q' to quit, '?' for help.
0