Chapter 1 Introduction to SQL Performance Optimization

InterSystems SQL supports several features to optimize THE SQL performance of the InterSystems IRIS® data platform.

Table definition optimization

SQL performance is fundamentally dependent on good data architecture. Partitioning data into multiple tables and establishing relationships between these tables is essential for efficient SQL.

Describes the operations defined by the following tuning table. These operations require the table to be defined, but not populated with data:

  • Data storage policy: Optional%Storage.Persistent,%Storage.SQLOr custom storage to store data.
  • Global variable naming policy: AvailableUSEEXTENTSETParameter specifies shorter, more efficient hash global names for data and index lookup operations.
  • Indexes: Indexes can be defined for a table field or a group of fields. Several different types of indexes can be defined: standard indexes, bitmap indexes, bitmap indexes, and bitmap range indexes. SQL optimization uses defined indexes rather than data values themselves to access specific records for query, update, or delete operations.

Table data optimization

Based on analysis of typical data in a table, you can optimize table access by:

  • Tune Table: Check typical Table data and generate itExtentSize(number of rows), selectivity (percentage of rows with a particular value), andBlockCountMetadata. The query optimizer uses this information to determine the most efficient query execution plan.
  • Selectivity and outlier selectivity: Determines the percentage of rows that a field has a particular value, and whether a value is an outlier that is significantly more common than other values of the field.

Query optimization

In almost all cases, queries written in embedded SQL execute faster than queries written in dynamic SQL. Also note that because of cached queries, re-execution of queries is much faster than initial execution for both embedded and dynamic SQL.

You can do the following to optimize the execution of a particular query. These query optimizations use existing table definitions and table data optimizations:

  • Runtime statistics: Used to measure the performance of query execution on the system.
  • Display Plan Displays the query execution plan.
  • Cached queries and text replacement: Maintains a cache of recent dynamic queries, allowing the query to be re-executed without repeating the overhead of preparing the query.
  • SQL statements and freeze plans allow query execution plans to remain, allowing tables to be changed without degrading the performance of existing queries.
  • Index configuration and usage: Used to specify how to use existing indexes.
  • Index optimization tips:%ALLINDEX,%IGNOREINDEX
  • Tips for connection optimization:%FIRSTTABLE,%FULL,%INORDER,%STARTTABLE
  • Subquery optimization tips:%NOFLATTEN,%NOMERGE,%NOREDUCE,%NOSVSO
  • Parallel query execution:%Parallel
  • Joint optimization:UNION %PARALLEL.UNION/OR

You can also improve query performance against large database tables by using data sharding.

Configuration optimization

By default, memory and startup Settings are automatically configured, and the maximum memory per process is 262144 KB by default. To optimize SQL running on a production system, change the default to manual configuration and increase the maximum memory setting per process.

shard

Sharding is the partitioning of data and its associated caches across multiple systems. A sharded cluster partitions large database tables horizontally (that is, row by row) across multiple InterSystems IRIS instances (called shard data servers), while allowing applications to access these tables transparently through a single instance (called a shard master data server).

Tables must be defined as shards. Shard tables can only be used in the shard environment; Non-sharded tables can be used in sharded or non-sharded environments. Not all tables are suitable for sharding. Optimal performance in a sharding environment is usually achieved by using a combination of sharded tables (usually very large tables) and non-sharded tables

Rapid command

InterSystems SQL supports fast selection, fast insertion, and fast truncation of tables. “Fast” means that standard calls to these SQL commands are performed using efficient internal code. These quick actions “are work”; No special syntax is used and no optimization options are provided.

Fast selection is supported via ODBC or JDBC SELECT queries. Insert operations on JDBC support fast insert. Fast table truncation is supported for truncation operations that do not involve referential integrity.

Not all tables support fast operations, and not all command syntax can be executed using fast execution. InterSystems SQL performs fast execution when possible; If quick execution is not possible, InterSystems SQL performs standard execution of the specified command.