InterSystems SQL Optimization Guide
Introduction to SQL Performance Optimization
InterSystems SQL supports several features that optimize SQL performance for the InterSystems IRIS Data Platform™.
SQL performance is fundamentally dependent upon good data architecture. Dividing your data into multiple tables and establishing relationships between those tables is essential to efficient SQL. How to define tables and their relations to each other is beyond the scope of this book.
This book describes the following operations to optimize a table definition. These operations require that the table be defined, but do not require the table to be populated with data:
: you can define an index for a table field or group of fields. You can define several different type of indices: standard, bitmap, bitslice, and bitmap extent. SQL optimization uses the defined indices, rather than the data values themselves, to access specific records for a query, update, or delete operation.
: you can freeze Data Definition execution plans. (Frozen Plans are also used to freeze query plans).
You can perform the following operations to optimize table access based on analysis of typical data in the table:
: examines typical table data and generates ExtentSize (number of rows), Selectivity (percent of rows having a specific value), and BlockCount metadata. The query optimizer uses this information to determine the most efficient query execution plan.
Selectivity and Outlier Selectivity
: determines the percent of rows having a specific value for a field, and whether one value is an outlier value, a value significantly more common than the other values for that field.
You can perform the follow operations to optimize the execution of a specific query. These query optimizations use existing table definition and table data optimizations:
You can also improve query performance against large database tables by using data sharding
Sharding is the partitioning of data and its associated cache across a number of systems. A sharded cluster partitions large database tables horizontally that is, by row across multiple InterSystems IRIS instances, called shard data servers, while allowing applications to transparently access these tables through a single instance, called the shard master data server. For further details on deploying a sharded cluster, creating sharded tables, and querying using sharding, refer to Horizontally Scaling InterSystems IRIS for Data Volume with Sharding
in the Scalability Guide