MicroStrategy ONE

SQL Global Optimization

The SQL Global Optimization property provides access to level options you can use to determine whether and how SQL queries are optimized.

In some cases, the SQL Engine generates duplicate or redundant passes, generates SQL passes that can be combined into one pass, or creates unnecessary temporary tables. Such SQL queries can have an adverse effect on performance.

The default option for this VLDB property has changed in 9.0.0. For information on this change, see SQL Global Optimization.

You can set the following SQL Global Optimization options to determine the extent to which SQL queries are optimized:

  • Level 0: No optimization: SQL queries are not optimized.
  • Level 1: Remove Unused and Duplicate Passes: Redundant, identical, and equivalent SQL passes are removed from queries during SQL generation.
  • Level 2: Level 1 + Merge Passes with different SELECT: Level 1 optimization takes place as described above, and SQL passes from different SELECT statements are consolidated when it is appropriate to do so.
  • Level 3: Level 2 + Merge Passes, which only hit DB Tables, with different WHERE: Level 2 optimization takes place as described above, and SQL passes which access database tables with different WHERE clauses are consolidated when it is appropriate to do so.
  • Level 4: Level 2 + Merge All Passes with Different WHERE: This is the default level. Level 2 optimization takes place as described above, and all SQL passes with different WHERE clauses are consolidated when it is appropriate to do so. While Level 3 only consolidates SQL statements that access database tables, this option also considers SQL statements that access temporary tables, derived tables, and common table expressions.
  • Level 5: Level 2 + Merge All Passes, which hit the same warehouse fact tables: Level 2 optimization takes place as described above, and when multiple passes hit the same fact table, a compiled table is created from the lookup tables of the multiple passes. This compiled table hits the warehouse fact table only once.

Additionally, if you use either Level 3 or Level 4 SQL Global Optimization, SQL passes can also be combined for the SQL that is generated for separate custom group elements.

The SQL optimization available with Level 3 or Level 4 can be applied for SQL passes that use the functions Plus (+), Minus (-), Times (*), Divide (/), Unary minus (U-), Sum, Count, Avg (average), Min, and Max. To ensure that valid SQL is returned, if the SQL passes that are generated use any other functions, the SQL passes are not combined.

Example: Redundant SQL Pass

This example demonstrates how some SQL passes are redundant and therefore removed when the Level 1 or Level 2 SQL Global Optimization option is selected.

Suppose the following appear on the report template:

  • Year attribute
  • Region attribute
  • Sum(Profit) {~+, Category%} metric (calculates profit for each Category, ignoring any filtering on Category)

The report generates the following SQL:

  • SQL Pass 1: Retrieves the set of categories that satisfy the metric qualification

    Copy 
        SELECT a11.CATEGORY_ID  CATEGORY_ID
        into #ZZTRH02012JMQ000
        FROM YR_CATEGORY_SLS a11
        GROUP BY a11.CATEGORY_ID
         HAVING sum(a11.TOT_DOLLAR_SALES) > 1000000.0

  • SQL Pass 2: Final pass that selects the related report data, but does not use the results of the first SQL pass:

    Copy 
        SELECT a13.YEAR_ID  YEAR_ID,
        a12.REGION_ID  REGION_ID,
        max(a14.REGION_NAME)  REGION_NAME,
        sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST))
        WJXBFS1
        FROM DAY_CTR_SLS a11
        join LU_CALL_CTR a12
        on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
        join LU_DAY a13
        on (a11.DAY_DATE = a13.DAY_DATE)
        join LU_REGION a14
        on (a12.REGION_ID = a14.REGION_ID)
        GROUP BY a13.YEAR_ID,a12.REGION_ID

SQL Pass 1 is redundant because it creates and populates a temporary table, #ZZTRH02012JMQ000, that is not accessed again and is unnecessary to generating the intended SQL result.

If you select either the Level 1: Remove Unused and Duplicate Passes or Level 2: Level 1 + Merge Passes with different SELECT option, only one SQL pass—the second SQL pass described above—is generated because it is sufficient to satisfy the query on its own. By selecting either option, you reduce the number of SQL passes from two to one, which can potentially decrease query time.

Example: Combinable SQL Passes

Sometimes, two or more passes contain SQL that can be consolidated into a single SQL pass, as shown in the example below. In such cases, you can select the Level 2: Level 1 + Merge Passes with different SELECT option to combine multiple passes from different SELECT statements.

Suppose the following appear on the report template:

  • Region attribute
  • Metric 1 = Sum(Revenue) {Region+} (calculates the total revenue for each region)
  • Metric 2 = Count<FactID=Revenue>(Call Center) {Region+} (calculates the number of call centers for each region)
  • Metric 3 = Metric 1/Metric 2 (Average Revenue = Total Revenue/Number of Call Centers)

The report generates the following SQL:

  • SQL Pass 1: Calculates Metric 1 = Sum(Revenue) {Region+}

    Copy 
        SELECT a12.[REGION_ID] AS REGION_ID,
    sum(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1
        into [ZZTI10200U2MD000]
        FROM [CITY_CTR_SLS] a11,
        [LU_CALL_CTR] a12
        WHERE a11.[CALL_CTR_ID] = a12.[CALL_CTR_ID]
        GROUP BY a12.[REGION_ID]

  • SQL Pass 2: Calculates Metric 2 = Count<FactID=Revenue>(Call Center) {Region+}

    Copy 
        SELECT a12.[REGION_ID] AS REGION_ID,
    count(a11.[CALL_CTR_ID]) AS WJXBFS1
        into [ZZTI10200U2MD001]
        FROM [CITY_CTR_SLS] a11,
        [LU_CALL_CTR] a12
        WHERE a11.[CALL_CTR_ID] = a12.[CALL_CTR_ID]
        GROUP BY a12.[REGION_ID]

  • SQL Pass 3: Final pass that calculates Metric 3 = Metric 1/Metric 2 and displays the result:

    Copy 
        SELECT pa11.[REGION_ID] AS REGION_ID,
        a13.[REGION_NAME] AS REGION_NAME,
        pa11.[WJXBFS1] AS WJXBFS1,
        IIF(ISNULL((pa11.[WJXBFS1] / IIF(pa12.[WJXBFS1] 
        = 0, NULL,
        pa12.[WJXBFS1]))), 0,
        (pa11.[WJXBFS1] / IIF(pa12.[WJXBFS1] = 0, 
        NULL,pa12.[WJXBFS1]))) AS WJXBFS2
        FROM [ZZTI10200U2MD000] pa11, 
        [ZZTI10200U2MD001] pa12, 
        [LU_REGION] a13
        WHERE pa11.[REGION_ID] = pa12.[REGION_ID] and
        pa11.[REGION_ID] = a13.[REGION_ID]

Because SQL passes 1 and 2 contain almost exactly the same code, they can be consolidated into one SQL pass. Notice the italicized SQL in Pass 1 and Pass 2. These are the only unique characteristics of each pass; therefore, Pass 1 and 2 can be combined into just one pass. Pass 3 remains as it is.

You can achieve this type of optimization by selecting the Level 2: Level 1 + Merge Passes with different SELECT option. The SQL that results from this level of SQL optimization is as follows:

Pass 1:

Copy 
SELECT a12.[REGION_ID] AS REGION_ID,
count(a11.[CALL_CTR_ID]) AS WJXBFS1
sum(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1
into [ZZTI10200U2MD001]
FROM [CITY_CTR_SLS] a11,
[LU_CALL_CTR] a12
WHERE a11.[CALL_CTR_ID] = a12.[CALL_CTR_ID]
GROUP BY a12.[REGION_ID]

Pass 2:

Copy 
SELECT pa11.[REGION_ID] AS REGION_ID,
a13.[REGION_NAME] AS REGION_NAME,
pa11.[WJXBFS1] AS WJXBFS1,
IIF(ISNULL((pa11.[WJXBFS1] / IIF(pa12.[WJXBFS1] = 0, NULL,
pa12.[WJXBFS1]))), 0,
(pa11.[WJXBFS1] / IIF(pa12.[WJXBFS1] = 0, NULL,
pa12.[WJXBFS1]))) AS WJXBFS2
FROM [ZZTI10200U2MD000] pa11, 
[ZZTI10200U2MD001] pa12, 
[LU_REGION] a13
WHERE pa11.[REGION_ID] = pa12.[REGION_ID] and
pa11.[REGION_ID] = a13.[REGION_ID]

Example: Combinable SQL Passes, with Different WHERE Clauses

Sometimes, two or more passes contain SQL with different where clauses that can be consolidated into a single SQL pass, as shown in the example below. In such cases, you can select the Level 3: Level 2 + Merge Passes, which only hit DB Tables, with different WHERE option or the Level 4: Level 2 + Merge All Passes with Different WHERE option to combine multiple passes with different WHERE clauses.

Suppose the following appear on the report template:

  • Quarter attribute
  • Metric 1 = Web Sales (Calculates sales for the web call center)
  • Metric 2 = Non-Web Sales (Calculates sales for all non-web call centers)

The report generates the following SQL

Pass 1:

Copy 
create table ZZMD00 (
    QUARTER_ID    SHORT, 
    WJXBFS1    DOUBLE)

Pass 2:

Copy 
insert into ZZMD00 
select     a12.[QUARTER_ID] AS QUARTER_ID,
    sum(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1
from     [DAY_CTR_SLS]    a11, 
    [LU_DAY]    a12
where     a11.[DAY_DATE] = a12.[DAY_DATE]
 and     a11.[CALL_CTR_ID] in (18)
group by    a12.[QUARTER_ID]

Pass 3:

Copy 
create table ZZMD01 (
    QUARTER_ID    SHORT, 
    WJXBFS1    DOUBLE)

Pass 4:

Copy 
insert into ZZMD01 
select     a12.[QUARTER_ID] AS QUARTER_ID,
    sum(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1
from     [DAY_CTR_SLS]    a11, 
    [LU_DAY]    a12
where     a11.[DAY_DATE] = a12.[DAY_DATE]
 and     a11.[CALL_CTR_ID] not in (18)
group by    a12.[QUARTER_ID]

Pass 5:

Copy 
select     pa11.[QUARTER_ID] AS QUARTER_ID,
    a13.[QUARTER_DESC] AS QUARTER_DESC0,
    pa11.[WJXBFS1] AS WJXBFS1,
    pa12.[WJXBFS1] AS WJXBFS2
from     [ZZMD00]    pa11, 
    [ZZMD01]    pa12, 
    [LU_QUARTER]    a13
where     pa11.[QUARTER_ID] = pa12.[QUARTER_ID] and 
    pa11.[QUARTER_ID] = a13.[QUARTER_ID]

Pass 2 calculates the Web Sales and Pass 4 calculates all non-Web Sales. Because SQL passes 2 and 4 contain almost exactly the same SQL, they can be consolidated into one SQL pass. Notice the highlighted SQL in Pass 2 and Pass 4. These are the only unique characteristics of each pass; therefore, Pass 2 and 4 can be combined into just one pass.

You can achieve this type of optimization by selecting the Level 3: Level 2 + Merge Passes, which only hit DB Tables, with different WHERE option or the Level 4: Level 2 + Merge All Passes with Different WHERE option. The SQL that results from this level of SQL optimization is as follows:

Pass 1:

Copy 
create table ZZT6C00009GMD000 (
    QUARTER_ID    SHORT, 
    WJXBFS1    DOUBLE, 
    GODWFLAG1_1    LONG, 
    WJXBFS2    DOUBLE, 
    GODWFLAG2_1    LONG)

Pass 2:

Copy 
insert into ZZT6C00009GMD000 
select    a12.[QUARTER_ID] AS QUARTER_ID,
    sum(iif(a11.[CALL_CTR_ID] in (18), 
 a11.[TOT_DOLLAR_SALES], NULL)) 
 AS WJXBFS1,
    max(iif(a11.[CALL_CTR_ID] in (18), 1, 0)) 
 AS GODWFLAG1_1,
    sum(iif(a11.[CALL_CTR_ID] not in (18), 
 a11.[TOT_DOLLAR_SALES], NULL)) 
 AS WJXBFS2,
    max(iif(a11.[CALL_CTR_ID] not in (18), 1, 0)) 
 AS GODWFLAG2_1
from     [DAY_CTR_SLS]    a11, 
    [LU_DAY]    a12
where     a11.[DAY_DATE] = a12.[DAY_DATE]
 and    (a11.[CALL_CTR_ID] in (18)
 or a11.[CALL_CTR_ID] not in (18))
group by     a12.[QUARTER_ID]

Pass 3:

Copy 
select     pa12.[QUARTER_ID] AS QUARTER_ID,
    a13.[QUARTER_DESC] AS QUARTER_DESC0,
    pa12.[WJXBFS1] AS WJXBFS1,
    pa12.[WJXBFS2] AS WJXBFS2
from     [ZZT6C00009GMD000]    pa12, 
    [LU_QUARTER]    a13
where     pa12.[QUARTER_ID] = a13.[QUARTER_ID]
 and    (pa12.[GODWFLAG1_1] = 1
 and pa12.[GODWFLAG2_1] = 1)