T3.3: Uses SQL with InterSystems IRIS

Overview

Embedded SQL and Dynamic SQL represent two fundamental approaches to executing SQL in InterSystems IRIS.

Embedded SQL

• Syntax: Uses the &sql() syntax
• Compilation: Compiled at class compilation time, generating in-line ObjectScript code for maximum execution efficiency
• Validation: Compiler validates syntax and checks for table existence at compile time, allowing early error detection
• Privilege Checking: Does NOT perform privilege checking; assumes applications handle authorization before executing queries

Dynamic SQL

• Implementation: Through the %SQL.Statement class, prepares and executes queries at runtime
• Similar To: ODBC or JDBC programming but executes within the same process context as the database engine
• Compile-Time Validation: Cannot validate at compile time; preprocessor macros cannot be used within them
• Advantage: Flexibility to construct specialized queries based on user input or runtime conditions

Performance Comparison

• Cached Queries: Both approaches support cached queries for improved re-execution performance
• Initial Execution: Dynamic SQL slightly less efficient due to lack of in-line code generation
• Subsequent Executions: Both benefit from query caching

Privilege Checking Behavior

• Dynamic SQL: Enforces SQL privilege checking for ODBC, JDBC, and SQL Shell interfaces
• Embedded SQL: Bypasses privilege checks entirely

Parameter Handling

• Dynamic SQL: Accepts literal input values through ? placeholders and :var host variables
• Embedded SQL: Uses input and output host variables exclusively (:var syntax)

When to Use Each Approach

• Embedded SQL: When queries are known at compile time
• Dynamic SQL: When queries need runtime construction

Internal Usage

Dynamic SQL is used internally by the SQL Shell, Management Portal Execute Query interface, and data import/export utilities.

Overview

InterSystems IRIS provides several SQL extensions beyond SQL-92 standards that leverage its object-oriented architecture and multidimensional database engine.

Arrow Syntax (->)

• Purpose: Enables implicit joins by navigating object references directly in SQL queries
• Example: "SELECT Name, Company->Name FROM Sample.Employee" retrieves employee names and their company names without explicit JOIN
• Compound Navigation: Supports chained references like Film->Category->CategoryName
• Child Tables: Also works with child table references
• Privilege Requirements: Requires SELECT privileges on referenced data in both tables, including column-level privileges on the ID field of referenced tables

TOP Clause

• Purpose: Controls result set size and provides query optimization benefits
• Optimization Change: When combined with ORDER BY, changes optimization from "fastest time to return all data" to "fastest time to return first row"
• Ideal For: Paginated displays
• Accepted Values: Integer literals, Dynamic SQL input parameters (?), or Embedded SQL host variables (:var)
• TOP ALL: Allows ORDER BY in subqueries and CREATE VIEW statements without restricting row count
• Caching Behavior: TOP values without parentheses are cached as parameter variables, allowing query reuse with different limits without re-preparation

Object Technology Integration

InterSystems IRIS integrates SQL tightly with its object technology, allowing relational and object access without sacrificing performance.

Additional IRIS-Specific Features

• Bitmap Indexing: Optimized for data warehouse applications, providing exceptional performance for complex analytical queries
• %CHECKPRIV Command: Enables programmatic privilege verification before executing queries

```sql -- %CHECKPRIV Example: SELECT %CHECKPRIV('SELECT') FROM Sample.Person -- Returns 1 if user has SELECT privilege, 0 otherwise ```

• TUNE TABLE: Gathers table statistics (selectivity, extent size, map size) for the query optimizer
• No Table Compression: Unlike traditional relational databases, IRIS applications do not require periodic table compression in deployed environments

Overview

Query plans are essential tools for understanding how InterSystems IRIS executes SQL statements and identifying optimization opportunities.

Show Plan Feature

• Purpose: Displays the query execution strategy without actually running the query
• Usage: In Management Portal SQL interface, input a query and click "Show Plan"
• Validation: Validates syntax, checks entity existence, and views the planned execution strategy
• Dynamic SQL: Set executemode=deferred, issue the query, then use SHOW STATEMENT to display the prepared statement and execution plan without consuming execution resources

Information Revealed by Query Plans

• Table Access Methods: Full table scan vs. index scan
• Index Selection Decisions: Which indexes the optimizer chose
• Join Strategies: Nested loop, hash join, merge join
• Estimated Row Counts: At each step of the plan
• Order of Operations: Which tables are accessed first and how intermediate results are processed

Selectivity Values

• Definition: Percentage of rows expected to satisfy each condition
• Interpretation: Lower selectivity values indicate more selective conditions that filter more data
• Source: The optimizer uses table statistics gathered by TUNE TABLE to estimate these values accurately

Interpreting Key Indicators

• Full Table Scans: Appear when no suitable index exists or when scanning is more efficient than index lookups for high-selectivity conditions
• Index Usage: Shows as "Index Used: indexname" in the plan
• Cost Estimates: Help compare alternative query formulations

Embedded SQL Validation

• Syntax Checking: Show Plan checks syntax and verifies referenced entities exist
• Error Reporting: Issues appropriate SQLCODE errors if problems are detected
• INTO Clause: Show Plan will not error on missing INTO clauses in Embedded SQL since they may appear in FETCH statements

SHOW STATEMENT Command

The %SYSTEM.SQL.Shell() interface provides the SHOW STATEMENT command to display prepared statements along with their execution plans, enabling iterative query refinement and optimization.

Overview

SQL Statement Index statistics provide visibility into query execution patterns and performance characteristics across the system.

SHOW STATEMENT Command

• Purpose: Displays information about prepared SQL statements in the SQL Shell
• Information Shown: Statement text, preparation status, and execution metadata
• Value: Particularly valuable for identifying performance bottlenecks and understanding which queries consume the most resources
• Usage: Execute a query, then issue SHOW STATEMENT to view detailed information without re-executing
• Deferred Mode: Setting executemode=deferred allows preparing queries and examining statements without incurring execution costs

Performance Metrics Tracked

• Execution Frequency: How many times a query has been executed
• Total Execution Time: Cumulative time across all executions
• Average Execution Time: Per-run execution time
• Row Counts: Number of rows returned

Index Utilization Analysis

• Index Usage Visibility: Statistics reveal which indexes are being utilized during query execution
• Unused Index Identification: Find indexes that waste storage and maintenance overhead
• Missing Index Detection: Identify where new indexes could improve query performance

Ongoing Performance Tuning

• Performance Degradation Detection: Analyze execution patterns over time
• Data Growth Impact: Identify queries affected by data growth
• Optimization Validation: Validate the effectiveness of optimization efforts

Cached Query Analysis

• Mechanism: InterSystems IRIS stores prepared statements for reuse
• Cache Utilization: Statement statistics help understand cache utilization
• Optimization Opportunities: Queries prepared repeatedly that could share cached statements

Data-Driven Decisions

Understanding statement statistics allows developers to make informed decisions about:

• Index Creation: Where to add new indexes
• Query Reformulation: How to rewrite inefficient queries
• Schema Design: Changes to improve overall system performance

Table statistics are fundamental to query optimization in InterSystems IRIS. The TUNE TABLE command gathers critical statistics based on current table data, calculating map size, extent size, and selectivity for each field. These statistics must be based on representative data that reflects the distribution expected in production. TUNE TABLE analyzes the data and sets values that the query optimizer uses to estimate costs and choose optimal execution plans. The optimizer relies heavily on selectivity values to determine which indexes to use, which table to access first in joins, and whether to use full table scans or index lookups.

When TUNE TABLE executes, it updates both the SQL table definition and typically the corresponding persistent class definition, allowing gathered statistics to be used without requiring class recompilation. For deployed classes, TUNE TABLE updates only the SQL table definition, and the optimizer accesses statistics indirectly. If statistics change, TUNE TABLE automatically recompiles all cached queries that reference the table, ensuring they use updated values. However, if running TUNE TABLE does not change any values (for example, when data distribution is unchanged), cached queries are not purged and classes are not flagged for recompilation, avoiding unnecessary overhead.

TUNE TABLE offers several options for fine-tuning the gathering process. The %SAMPLE_PERCENT option specifies what percentage of table rows to sample, useful when outlier values are not evenly distributed. For tables with fewer than 1000 rows, TUNE TABLE always samples the entire extent regardless of this setting. The %CLEAR_VALUES option removes existing statistics from class and table definitions. TUNE TABLE requires %ALTER_TABLE administrative privilege and %ALTER privilege on the specific table. Table owners automatically have %ALTER privilege. The command can be executed via SQL (TUNE TABLE tablename), the Management Portal SQL interface Actions menu, or programmatically using $SYSTEM.SQL.Stats.Table.GatherTableStats(). Best practices include running TUNE TABLE after significant data loads, when query performance degrades unexpectedly, after schema changes that affect data distribution, and periodically in production environments to maintain accurate statistics as data evolves.

InterSystems IRIS implements comprehensive SQL security that complements system-level security with additional granular protections. SQL privileges provide table-level and column-level access control beyond database-level protections. A critical distinction is that SQL privileges can be granted directly to users or to roles, while system-level privileges are assigned only to roles. Holding an SQL privilege implicitly grants related system privileges required to perform the SQL action, but the reverse is not true: system-level privileges do not imply table-level privileges. This separation allows precise control over data access patterns.

SQL privilege checking is enforced for ODBC connections, JDBC connections, Dynamic SQL execution, and SQL Shell operations. However, Embedded SQL statements do not perform privilege checking under the assumption that applications using Embedded SQL implement their own authorization logic before executing queries. Similarly, direct invocation of class queries that do not involve %SQL.Statement objects is considered application access and bypasses SQL privilege checks. This design allows trusted applications to operate efficiently while enforcing security for external connections and ad-hoc queries.

SQL privileges operate at multiple levels of granularity. Table-level privileges (SELECT, INSERT, UPDATE, DELETE, ALTER, REFERENCES) control operations on entire tables or views. Column-level privileges provide finer control, allowing SELECT privilege on specific columns rather than the entire table. This is particularly important for arrow syntax queries, which require SELECT privilege on the ID of referenced tables plus the referenced columns. Administrative privileges like %ALTER_TABLE enable schema modification operations such as TUNE TABLE. Users who create tables automatically receive owner privileges on those tables. The %CHECKPRIV command allows programmatic verification of privileges before executing operations, enabling applications to provide appropriate feedback when users lack necessary permissions. The GRANT and REVOKE commands manage privilege assignments, and privileges can be granted WITH GRANT OPTION to allow recipients to grant those privileges to others. Roles can combine both SQL and system-level privileges, providing flexible security models. Best practices include following the principle of least privilege, using roles for privilege management, implementing column-level privileges for sensitive data, and auditing privilege usage through SQL audit features.