KB44052: How does the feature in the MicroStrategy 9.3.x SQL engine: "Parallel Query Execution" work.

• Strategy

This article describes how the Parallel query execution feature works in MicroStrategy.

A feature is available in the Strategy SQL engine: "Parallel Query Execution". This is an execution option/optimization for when executing multi-pass SQL against a database. When enabled it allows independent passes of SQL to be executed simultaneously against a database instead of sequentially. The benefit to this is many databases/database servers have the resources to handle these queries simultaneously allowing for faster execution of reports overall.
The option is available in the Project Level VLDB properties under "Query Optimizations" as seen below.

Without this option all reports would execute their SQL pass by pass in a sequential order over a single database connection.
Example:
The SQL below is from a report that can use parallel execution.


create table ZZMD00 (
    YEAR_ID    SHORT, 
    Revenue    DOUBLE)


insert into ZZMD00 
select    a11.[YEAR_ID] AS YEAR_ID,
    sum(a11.[TOT_DOLLAR_SALES]) AS Revenue
from    [YR_CATEGORY_SLS]    a11
group by    a11.[YEAR_ID]

create table ZZMD01 (
    QUARTER_ID    SHORT, 
    WJXBFS1    DOUBLE)


insert into ZZMD01 
select    a11.[QUARTER_ID] AS QUARTER_ID,
    sum(a11.[TOT_COST]) AS WJXBFS1
from    [QTR_CATEGORY_SLS]    a11
group by    a11.[QUARTER_ID]

create table ZZMD02 (
    YEAR_ID    SHORT, 
    WJXBFS1    LONG)


insert into ZZMD02 
select    a12.[YEAR_ID] AS YEAR_ID,
    count(pa11.[WJXBFS1]) AS WJXBFS1
from    [ZZMD01]    pa11, 
    [LU_QUARTER]    a12
where    pa11.[QUARTER_ID] = a12.[QUARTER_ID]
group by    a12.[YEAR_ID]

select    pa11.[YEAR_ID] AS YEAR_ID,
    pa11.[Revenue] AS Revenue,
    pa12.[WJXBFS1] AS WJXBFS1
from    [ZZMD00]    pa11, 
    [ZZMD02]    pa12
where    pa11.[YEAR_ID] = pa12.[YEAR_ID]


drop table ZZMD00

drop table ZZMD01

drop table ZZMD02

The sets of passes 1,2 and 3,4,5,6 are independent from each other so will be executed simultaneously. The rest of the passes are not independent so cannot be executed simultaneously. The execution steps that will be followed for this report are listed below.

1. Open two database connections.
2. Run passes 1 and 2 sequentially in database connection 1.
3. Run passes 3, 4, 5, 6 sequentially in database connection 2.
4. Wait for both database connections to finish executing their SQL.
5. Run passes 7-10 sequentially through one of the available database connections.

Without this setting in Strategy all 10 passes would be run sequentially through a single database connection which introduces additional overhead time if the queries are small and the database has resources to spare.

Comment

0 comments

Details

Knowledge Article

Published:

April 7, 2017

Last Updated:

April 7, 2017

create table ZZMD00 ( YEAR_ID SHORT, Revenue DOUBLE) insert into ZZMD00 select a11.[YEAR_ID] AS YEAR_ID, sum(a11.[TOT_DOLLAR_SALES]) AS Revenue from [YR_CATEGORY_SLS] a11 group by a11.[YEAR_ID] create table ZZMD01 ( QUARTER_ID SHORT, WJXBFS1 DOUBLE) insert into ZZMD01 select a11.[QUARTER_ID] AS QUARTER_ID, sum(a11.[TOT_COST]) AS WJXBFS1 from [QTR_CATEGORY_SLS] a11 group by a11.[QUARTER_ID] create table ZZMD02 ( YEAR_ID SHORT, WJXBFS1 LONG) insert into ZZMD02 select a12.[YEAR_ID] AS YEAR_ID, count(pa11.[WJXBFS1]) AS WJXBFS1 from [ZZMD01] pa11, [LU_QUARTER] a12 where pa11.[QUARTER_ID] = a12.[QUARTER_ID] group by a12.[YEAR_ID] select pa11.[YEAR_ID] AS YEAR_ID, pa11.[Revenue] AS Revenue, pa12.[WJXBFS1] AS WJXBFS1 from [ZZMD00] pa11, [ZZMD02] pa12 where pa11.[YEAR_ID] = pa12.[YEAR_ID] drop table ZZMD00 drop table ZZMD01 drop table ZZMD02