9.3 Parallel Query Execution

9.3 Supports parallel SQL execution from reports. 

There is another setting with database instance manager where you can set the maximum number of parallel execution of sql, this is for sql from different reports. you will need this while executing a document.  We are here looking at executing the sql from a single report in parallel.

You can enable / disable this through the VLDB settings. Go to VLDB - Tools - Show Advanced Options.  In Query optimization, you can find the below two properties.

The second one can help you get an estimation on the time that can be saved with parallel execution. This information will be shown in the report SQL.

for example.

WITHOUT PARALLEL EXECUTION
Query Generation Time: 0:00:00.26

Total Elapsed Time in Query Engine: 0:04:17.49
Sum of Query Execution Time: 0:04:13.07
Sum of Data Fetching and Processing Time: 0:00:00.05
Sum of Data Transfer from Datasource(s) Time: 0:00:00.05
Sum of Analytical Processing Time: 0:00:00.00
Sum of Other Processing Time: 0:00:04.37
Projected execution time improvement (%) if executed in Parallel Execution mode: 97%

This report that took about four minutes to run, can be improved upto 97% using PSE. (Parallel SQL Execution)   WITH PARALLEL EXECUTION Query Generation Time: 0:00:00.30
Total Elapsed Time in Query Engine*: 0:02:42.19
Sum of Query Execution Time: 0:05:12.80
Sum of Data Fetching and Processing Time: 0:00:00.01
Sum of Data Transfer from Datasource(s) Time: 0:00:00.01
Sum of Analytical Processing Time: 0:00:00.00
Sum of Other Processing Time: 0:01:47.28

* This report has some passes that have been executed in parallel. Individual time components may not add up to Total Elapsed Time in Query Engine.

I can see that this report not runs at 63% of the time it took earlier withouit PSE. But why did not I get 97% improvement ? You can configure the number of PSE for your report from Project Configuration - Advanced - VLDB setting - Query Optimization.  By default it is 2. Ie two sql pass will run in parallel. Try playing with this number and see what is the best value for your report.   In my case, I had about 206 SQL pass, and when set PSE to 3, I started getting errors, may be the SQL pass was fired too fast, even before the DB commited the table from the previous passes.   This is a project wide setting. In your project, if you have 5 reports and each of them have a different optimal number for PSE, you need to choose the lowest PSE for your project. By optimal PSE settig I am refereing to the max PSE you can choose, before the report starts failing. (too fast - just like some movie heros shoot the enemy faster than their own shadows :-) ) .