The 2022.2 release includes many enhancements to SQL.
SQL Process View
The SQL Process View offers a simple SQL-accessible view and corresponding page in the System Management Portal for consulting all the SQL statements that are currently running on the system. This enables administrators to quickly identify queries that may already have been running for an unusually long time and immediately compare this execution time to historical runtime statistics for that same query based on information captured in the Statement Index.
This change is fully integrated with our System Alerting and Monitoring (SAM)Opens in a new tab product, which leverages the same internal infrastructure to expose the corresponding metrics.
Distributing a Workload Across a Sharded Cluster
This release completes support for the object data model in sharded clusters. Where previously individual object-style access was already supported, such code, inherently procedural, would run on the node where it was invoked. With this release, we're introducing two easy-to-use API methods to invoke procedural code on all the nodes of a sharded cluster.
A Broadcast() method will simply invoke a routine or method once on each data and/or compute node, and can be used to kick off complex ObjectScript code across the cluster, for example to pull new data from a feed and insert it locally. A separate Map() method will invoke a routine or method once for every instance of a sharded class, on the data node where that instance is physically stored. These methods follow the same style of signatures as the Work Queue Manager, which distributes work across processes on a single server.
These two methods (in %SYSTEM.ShardWorkMgrOpens in a new tab) offer developers all the flexibility they need to take advantage of their sharded cluster.
Lateral JOIN Support
This release introduces support for lateral joins, a style of joining tables or subqueries where the different streams being joined together are not evaluated independently. A typical use case for this is when a subquery includes a reference to another table being joined, or a table-valued function taking a column value of another joined table as an input. LATERAL JOIN is a standard ANSI SQL construct.
Better schema management — This release introduces a number of convenience extensions to IRIS DDL such as the CREATE IF NOT EXISTS for tables and views and CREATE OR REPLACE for code artefacts such as procedures and functions. Also, the DDL export utility now covers more class definition features that can be expressed as DDL; see the ExportDDL() method of %SYSTEM.SQL.SchemaOpens in a new tab.
Projection of list collections — List of style collection properties can now be projected to a child table, similar to how Array of collection properties were projected before. This means the physical storage option and SQL projection are now entirely independent.
Performance improvements — When executing SQL queries, the use of JOIN, GROUP BY, ORDER BY and other constructs means a temporary data structure (referred to as "tempfile" internally) is built by one part of the query plan and then read by another part of the query plan. This change bundles a few changes to how internal constructs are used to store these temporary data structures to better leverage available memory and improve overall query performance. These changes take advantage of the new default for process-private memory (now defaulting to unlimited per the new default bbsiz setting).