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
    	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
    	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+}
    	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+} 
    	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

    	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: 

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: 

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: 

create table ZZMD00 (
	QUARTER_ID	SHORT, 
	WJXBFS1	DOUBLE)

Pass 2: 

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: 

create table ZZMD01 (
	QUARTER_ID	SHORT, 
	WJXBFS1	DOUBLE)

Pass 4: 

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: 

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: 

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

Pass 2: 

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: 

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)