Performance tuning of a report
Performance Tuning Techniques
Performance tuning techniques are used to improve the performance of an MSTR report. When we are running a report, if it is taking a long time to execute, we tune the Report SQL so that the Report executes considerably faster.<?xml:namespace prefix = o ns = “urn:schemas-microsoft-com:office:office” />
Tuning of a report can be done in two ways:-
1. Database level
2 .MSTR level
DATA BASE LEVEL:
Indexes: By creating indexes on top of table columns, retrieval of data will be fast and hence execution time will be less.
Partions: By Creating partitions on top of ware house tables data retrieval will be fast which in turn improve the performance of a report.
Aggregate tables: Aggregate tables store pre-summarized totals at a higher level of aggregation than the most granular fact table. They allow reports to be generated from small, rather than large, tables therefore, performance is enhanced. A successful aggregation strategy seeks to choose aggregate tables that will have the most impact while taking the least amount of space.
MSTR LEVEL: whenever we try to run a report and if it is taking long time to execute then we try to tune the SQL part by using VLDB Properties. Some of them are listed below
Sub query type:
By checking if there exist any sub query in SQL ,if yes then we try to tune that by using a VLDB Property called ‘Query Optimization’. In Query Optimization we have option called sub query type which eliminates the sub queries.
SQL global optimization
Check if there exists any unnecessary case statements, if yes then by using VLDB property called SQL global optimization (level)we can eliminate the case statements, unused duplicate SQL passes.
Level 0: No optimization
Level 1: Remove Unused and Duplicate Passes
Level 2: Level 1 + Merge Passes with Different SELECT
In MicroStrategy SQL Generation Engine 9.0, two more levels of optimization exist:
Level 3: Level 2 + Merge Passes, which only hit DB tables, with Different WHERE
Level 4: Level 2 + Merge Passes with Different WHERE
WHERE clause driving table
WHERE clause driving table is used to control which table the engine should use to apply the filter (WHERE clause). This setting is used as an optimization for some databases which perform better when columns coming from smaller tables are used in the where clause.
By default for all databases, the MicroStrategy Engine applies the filter to the fact table. We can apply filter at look up table so that data retrieval will be fast.
JOIN type:
We can change join types from Join89 to 92 and vice versa.
Join 89 was the earlier standard wherein the joins are performed in the WHERE clause.
Join 92 is the later standard wherein the joins are performed in the FROM clause.
Intermediate Tables:
Controls the type of intermediate tables that are created during report execution.
This VLDB property specifies the type of intermediate storage structures the SQL Engine creates. If the database supports true temporary tables, then the true temporary tables option is the default setting. If not, the permanent tables option is the default setting.
The possible values for this VLDB property are:
• Permanent table
• Derived table
• Common table expression
• True temporary table
• Temporary view
