A new feature is available starting in the MicroStrategy 9.3.x SQL engine: “Parallel Query Execution”. This is a new execution option/optimization for when executing 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 resource 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.
Previous to 9.3.0 all reports executed 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. AS YEAR_ID,
sum(a11.) AS Revenue
from a11
group by a11.
create table ZZOT01 (
YEAR_ID SHORT,
WJXBFS1 LONG)
insert into ZZOT01
select distinct a12. AS YEAR_ID,
a11. AS WJXBFS1
from a11,
a12
where a11. = a12.
create table ZZMD02 (
YEAR_ID SHORT,
WJXBFS1 LONG)
insert into ZZMD02
select pa11. AS YEAR_ID,
count(*) AS WJXBFS1
from pa11
group by pa11.
select pa11. AS YEAR_ID,
pa11. AS Revenue,
pa12. AS WJXBFS1
from pa11,
pa12
where pa11. = pa12.
drop table ZZMD00
drop table ZZOT01
drop table ZZMD02
Passes 1,2 and 3,4 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 and 4 sequentially in database connection 2.
4. Wait for both database connections to finish executing their SQL.
5. Run passes 5-10 sequentially through one of the available database connections.
In previous versions of MicroStrategy all 10 passes would be run sequentially through a single database connection.
TN44052: New feature in the MicroStrategy 9.3.x SQL engine: "Parallel Query Execution".