Parallel Query Processing - (System-wide and Query-based)

Article

Parani.K · Mar 2 8m read

#CSP #Globals #Management Portal #Security #SQL #InterSystems IRIS

Parallel query hinting boosts certain query performances on multi-processor systems via parallel processing. The SQL optimizer determines when this is beneficial. On single-processor systems, this hint has no effect.

Parallel processing can be managed by:

Setting the auto parallel option system-wide.
Using the %PARALLEL keyword in the FROM clause of specific queries.

%PARALLEL is ignored when it applied to:

INSERT, UPDATE, and DELETE queries (Only SELECT queries benefit from this feature)
The queries involving process-specific functions or variables
A subquery correlated with an enclosing query.
A subquery containing complex predicates, such as FOR SOME and FOR SOME %ELEMENT predicates.

Here are some reasons why Parallel Query Processing might be ignored, in addition to the previously mentioned ones:

Some complex queries may not benefit from parallel processing, even if they appear to do so initially.
Certain database configurations and settings may not support %PARALLEL processing.
Dependencies and relationships within the data structure could prevent effective parallelization.

%PARALLEL will not perform parallel processing in these scenarios:

Query includes both TOP and ORDER BY clauses, optimizing for fastest time-to-first-row.
Query references a view and returns a view ID.
Query uses customized storage formats or GLOBAL TEMPORARY tables or tables with extended global reference storage.
Query accesses a table with row-level security.
Data is stored in a remote database.
Process-level NLS collation doesn't match the NLS collation of all globals involved.

For more detailed options, considerations, and restrictions, refer to Configure Parallel Query Processing (Interystems Documentation) and Specify Optimization Hints in Queries. This topic was recently explored on top of a discussion in InterSystems Developer Community (DC), which inspired this article on IRIS, Caché, and Ensemble.

InterSystems IRIS supports parallel processing for both embedded SQL, dynamic SQL and SQL in QueryMethods. When the %PARALLEL keyword is being used in the FROM clause of a query to suggest parallel processing. The SQL optimizer will determine if the query can benefit from parallel processing and apply it where appropriate.

To utilize %PARALLEL processing in InterSystems IRIS effectively, several factors and limitations are needs to be considered for both system-wide and query-level settings to extract the full benefit.

In case of trying to achieve %PARALLEL processing with help of System-Wide Parallel Query Processing and Adaptive Mode is off, you can enable system-wide parallel query processing through Management Portal or $SYSTEM.SQL.Util.SetOption()
Example

USER>w ##class(%SYSTEM.SQL.Util).GetOption("AutoParallel")
0
USER>d ##class(%SYSTEM.SQL.Util).SetOption("AutoParallel",1,.oldParVal)

USER>w ##class(%SYSTEM.SQL.Util).GetOption("AutoParallel")
1
USER>zw oldParVal
oldParVal=0

Other Important points to consider during the implementation of the %PARALLEL feature.

When AdaptiveMode is enabled, automatic parallel processing is applied to all SELECT queries, hinting them with %PARALLEL. However, not all queries may use parallel processing as the SQL Optimizer may decide otherwise.
When we are trying to utilize this %PARALLEL feature, we must consider AutoParallelThreshold as well (default value is 3200) and there is no use with this parameter in case AutoParallel is disabled.
The auto parallel threshold parameter affects whether a query runs in parallel, with higher values reducing the chance of parallel processing. The default value is 3200, adjustable via $SYSTEM.SQL.Util.SetOption("AutoParallelThreshold",n,.oldval).
In sharded environments, parallel processing is used for all queries regardless of the threshold when Adaptive Mode is on.
When AdaptiveMode Mode is enabled (set to 1) and AutoParallel is disabled, Adaptive Mode overrides the AutoParallel setting and activates parallel processing.

Example:
Sample class with populated 100,000 records

 Class SQLClass.MyTest Extends (%Persistent, %Populate)
  {

    Property Name As %String(MAXLEN = 255);

    Property Age As %Integer(MAXVAL = 100, MINVAL = 1);

    Property Address As %String(MAXLEN = 255);

    Property City As %String(MAXLEN = 255);

    Property State As %String(MAXLEN = 255);

    Property Zip As %String(MAXLEN = 255);

    Property Country As %String(MAXLEN = 255);

    Property Comment As %String(MAXLEN = 255);

    Property Hobby As %String(MAXLEN = 255);

    Property JobTitle As %String(MAXLEN = 255);

    Property Company As %String(MAXLEN = 255);

    Property PhoneNumber As %String(MAXLEN = 255);

    Property Email As %String(MAXLEN = 255);

    Property Gender As %String(MAXLEN = 1);

    Property Ethnicity As %String(MAXLEN = 255);

    Property Race As %String(MAXLEN = 255);

    Property Religion As %String(MAXLEN = 255);

    Property MaritalStatus As %String(MAXLEN = 255);

    Property Children As %Integer(MAXVAL = 10, MINVAL = 0);

    Property Income As %Integer(MAXVAL = 100000, MINVAL = 0);

    Property Occupation As %String(MAXLEN = 255);

    Property Education As %String(MAXLEN = 255);

    Property HomePhone As %String(MAXLEN = 255);

    Property MobilePhone As %String(MAXLEN = 255);

    Property WorkPhone As %String(MAXLEN = 255);

    Property WorkEmail As %String(MAXLEN = 255);

    Property HomeEmail As %String(MAXLEN = 255);

    Property HomeAddress As %String(MAXLEN = 255);

    Property HomeCity As %String(MAXLEN = 255);

    Property HomeState As %String(MAXLEN = 255);

    Property HomeZip As %String(MAXLEN = 255);

    Property HomeCountry As %String(MAXLEN = 255);

    Property WorkAddress As %String(MAXLEN = 255);

    Property WorkCity As %String(MAXLEN = 255);

    Property WorkState As %String(MAXLEN = 255);

    Property WorkZip As %String(MAXLEN = 255);

    Property WorkCountry As %String(MAXLEN = 255);

    Property WorkPhoneNumber As %String(MAXLEN = 255);

    Property WorkMobilePhone As %String(MAXLEN = 255);

    Property WorkFax As %String(MAXLEN = 255);

    Property WorkWebsite As %String(MAXLEN = 255);

    Property WorkComments As %String(MAXLEN = 255);


    Index IdxAge On Age;
}

Test # 1
Sample run without % PARALLEL (to display 10,000 records in SMP)

select * from SQLClass.MyTest where age>40

3.2069 seconds
10404 global references
3325407 commands executed

Sample run with %PARALLEL(to display 10,000 records in SMP)

select * from %PARALLEL SQLClass.MyTest where age>40

2.8681 seconds
10404 global references
3325407 commands executed

Test # 2 :
Sample run without % PARALLEL (to display 1 record in SMP)

select COUNT(Children),MAX(Children),MIN(Children),AVG(Children) from SQLClass.MyTest where age>10

0.4037 seconds
46559 global references
1459936 commands executed

Sample run with %PARALLEL (to display 1 record in SMP)

select COUNT(Children),MAX(Children),MIN(Children),AVG(Children) from %PARALLEL SQLClass.MyTest where age>10

0.0845 seconds
46560 global references
1460418 commands executed

Example with embedded SQL

ClassMethod embeddedSQL() As %Status
{
    // w ##Class(SQLClass.MyTest).embeddedSQL()
    Set sc = $$$OK
    DO ClearBuffers^|"%SYS"|GLOBUFF()
    set stime=$p($zts,",",2)
    &sql(select COUNT(Children),MAX(Children),MIN(Children),AVG(Children) from SQLClass.MyTest where age>10)
    w:'SQLCODE "Without %Parallel : ",($p($zts,",",2)-stime),!
    DO ClearBuffers^|"%SYS"|GLOBUFF()
    set stime=$p($zts,",",2)
    &sql(select COUNT(Children),MAX(Children),MIN(Children),AVG(Children) from %PARALLEL SQLClass.MyTest where age>10)
    w:'SQLCODE "With %Parallel : ",($p($zts,",",2)-stime),!
    Return sc
}

Results (embedded SQL) :
USER> D ##Class(SQLClass.MyTest).embeddedSQL()
Removed 5466 blocks
Without %Parallel : .355737
Removed 5217 blocks
With %Parallel : .3407056

USER>

Example with dynamic SQL

ClassMethod dynamicSQL() As %Status
{
     // w ##Class(SQLClass.MyTest).dynamicSQL()
    Set sc = $$$OK
    DO ClearBuffers^|"%SYS"|GLOBUFF()
    set stime=$p($zts,",",2), recCnt=0
    Set rs=##class(%ResultSet).%New()
    Set sc=rs.Prepare("select COUNT(Children),MAX(Children),MIN(Children),AVG(Children) from SQLClass.MyTest where age>10")
    Set sc=rs.Execute()
    While(rs.Next()) {
        w "COUNT(Children) : ",rs.GetData(1),"; MAX(Children) : ",rs.GetData(2),"; MIN(Children) : ",rs.GetData(3),"; AVG(Children) : ",rs.GetData(4),!
    }
    w "Without %Parallel : ",($p($zts,",",2)-stime),!!!
    DO ClearBuffers^|"%SYS"|GLOBUFF()
    set stime=$p($zts,",",2), recCnt=0
    Set sc=rs.Prepare("select COUNT(Children),MAX(Children),MIN(Children),AVG(Children) from SQLClass.MyTest where age>10")
    Set sc=rs.Execute()
    While(rs.Next()) {
        w "COUNT(Children) : ",rs.GetData(1),"; MAX(Children) : ",rs.GetData(2),"; MIN(Children) : ",rs.GetData(3),"; AVG(Children) : ",rs.GetData(4),!
    }
    w "With %Parallel : ",($p($zts,",",2)-stime),!
    Return sc
}

Result (Dynamic SQL):
USER>d ##Class(SQLClass.MyTest).dynamicSQL()
Removed 22163 blocks
COUNT(Children) : 89908; MAX(Children) : 10; MIN(Children) : 0; AVG(Children) : 5.021989144458780086
Without %Parallel : .4036913

Removed 5721 blocks
COUNT(Children) : 89908; MAX(Children) : 10; MIN(Children) : 0; AVG(Children) : 5.021989144458780086
With %Parallel : .3693442