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 :-) ) .       

Web or Intelligence Server - Identify where to start applying the optimization

Perfronamce Optimization

Invariably this is one area where every architect and developer will spend a lot of time identifying the bottleneck in the system or the whcih areas to apply optimization.  How do they do that ? We do not  know the internals of MSTR, and the only approach that we can take is to follow the suggestions provided by MSTR. And there are many. Before you start following the suggestions you must know which of these sugegstions to apply? Else you will end up in lot of trials than what is necessary and many times, tasks that can be completed quickly, could take longer time.

So how do i quickly identify where to apply the optimization ?

1. Is it my web server taking longer time to display the result?
2. Is it my Intelligence Server taking longer time to process the result ?
3. Is it my network causing the delay ?

these are the basic questions you could start asking and once you identify the source of delay, you can further use the suggestions from MicroStrategy.

Web Stats

MicroStrategy has an option in the Web Admin Page, called statistics. You can enable this and choose either screen / file / both. When you choose screen option, you will see some statistics on evey page displayed. Lets look at how to use these statistics.  For the purpose of the demo, I created a Dashboard with three reports. I have disabled report caching at project level, since I dont want the results to come from a cache while doing my testing.

On Executing the dashboard, you can see the below statistics displayed below the dashbaord content.

Total Web Server + IServer processing time : 3140 millisecondsWeb server processing time : 630 milliseconds (20%)
Transmission Time : 0 milliseconds
IServer processing Time : 2510 milliseconds (80%)
   IServer API time : 220 milliseconds (7%)
   IServer polling time : 2290 milliseconds (73%)
Number of Web API polling calls : 22
Number of Web API calls : 27
Bytes sent/received : 31504 63643
Start Time (millis) : 1367477655056
End Time (millis) : 1367477658196
 

Lets look at each of these lines.

1. the first line, tells me that the total time it took for the display of the dashbaord is approximately 3 sec
2. second line, shows the total time taken by web server, this is the renderind time web server takes to process the xml results from the IS  (630/3140) *100 = 20 %
3. third line, is the transmission time, since IS and web are on the same machine, it is zero
4. fourth line, shows the time taken by the Intelligence Server for processing of the results as well as the polling time. (2510/3140) * 100 = 80 %
5. fifth line, shows the time to execute XML API calls and retrieve the data.
6. sixth line , shows the amount of time the server spends polling the IS for data.
7. seventh line, show the number of polling calls made, polling is done every 110 milliseconds.
8. eight line, shows the number of API calls made by web, this included calls to IS and local calls to acccess cache, user rights etc
9. ninth lines, shows the data send and received in bytes, it cal vary each time because of the number of calls made.
10. tenth line, shows the start time
11. elevent line show the end time.

So in this example, you can easily say that 80% of the time is spend in Intelligence Server.  So where should I start applying the optimization ? Web server ? but that hardly took 20 % of the time.   I need to look at how to optimize that 80%  time taken by I server.  

The reports are not using any prompts, and hence I decided to enable caching for them. Lets look at the stats now.

Total Web Server + IServer processing time : 860 millisecondsWeb server processing time : 450 milliseconds (52%)
Transmission Time : 0 milliseconds
IServer processing Time : 410 milliseconds (48%)
   IServer API time : 110 milliseconds (13%)
   IServer polling time : 300 milliseconds (35%)
Number of Web API polling calls : 3
Number of Web API calls : 14
Bytes sent/received : 7646 71394
Start Time (millis) : 1367477719529
End Time (millis) : 1367477720389

See how the dashbaord is now displayed withing a second. From three seconds to one second, is my performance gain. And all of this is done quickly. Depending on your case, the time it takes to find a method for optimization might vary.  The critical factor is to identify where to apply the optimization and which areas needs optimization. Without knowing that you are going to take a longer route for an easy solution.