About parallel queries (%PARALLEL)

InterSystems FAQ rubric

One way to optimize query performance is to use query parallelism on a per-query or system-wide basis (a standard feature).

This is a technique for dividing the execution of a particular query among processors on a multi-processor system. The query optimizer will execute parallel processing only if there is a possibility of benefiting from parallel processing. Parallel processing is only applicable to SELECT statements.

Note that the number of parallel processes cannot be specified, as it is automatically adjusted according to the number of CPUs. You can check the current number of processors in your system with the following command.

USER>write $SYSTEM.Util.NumberOfCPUs()
8

Previously, parallel processing was enabled by adding the %PARALLEL keyword to a query, but from IRIS2019.1 onwards, the default is "always enabled".

Management Portal:
  System Administration > Configuration > SQL and Object Settings > SQL
    Execute queries in a single process
    *Check this box to disable parallel processing (default is unchecked)

To disable parallelism on a per-query basis, specify the %NOPARALLEL keyword.

SELECT * FROM %NOPARALLEL Sample.Person WHERE ...

Note:
Specifying %PARALLEL may degrade performance for some queries. For example, specifying %PARALLEL when executing a query on a system with multiple concurrent users may result in overall performance degradation. In this case, use %NOPARALLEL to disable parallel processing for each query, or set the system-wide setting to "Execute queries in a single process."

For more information, see the following document: Query Parallelism