SAP HANA Best Practices for Performance

Associate Scrum Master • Strategy

This article describes the best practices for using SAP HANA in MicroStrategy.

This article covers best practices for the following topics:
1. Query Optimization
2. Query Generation
3. SAP HANA Query Execution

Query Optimization

SQL Global Optimization

This setting can substantially reduce the number of SQL passes generated by Strategy. In Strategy, SQL Global Optimization reduces the total number of SQL passes with the following optimizations:

Eliminates unused SQL passes, e.g. a temp table is created but not referenced in a later pass
Reuses redundant SQL passes
E.g. exact same temp table is created multiple times single temp table is created
Combines SQL passes where the SELECT list is different

E.g. two temp tables have same FROM clause, same JOINs, same WHERE clause, same GROUP BY SELECT lists are combined into single SELECT statement

Combines SQL passes where the WHERE clause is different

E.g. two temp tables have same SELECT list, same FROM clause, same JOINs, same GROUP BY predicates from the WHERE clause are moved into CASE statements in the SELECT list.

The default setting for SAP HANA is to enable SQL Global Optimization at its highest level. If your Database Instance is configured as an earlier version of SAP HANA, you may have to enable this setting manually. For more information, see the System Administration Guide.

Set Operator Optimization

This setting is used to combine multiple subqueries into a single subquery using set operators (i.e. UNION, INTERSECT, EXCEPT). The default setting for SAP HANA is to enable Set Operator Optimization.

Bulk Inserts

Parameterized inserts are supported by SAP HANA. Strategy Developers must configure the Strategy Database Connection to enable the use of parameterized inserts into SAP HANA.

Default VLDB Settings for SAP HANA

VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)
VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)
VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)
VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)
VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)
VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)
VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)
VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)
VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)
VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)
VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)

Additional VLDB Settings
Many of the VLDB properties control string syntax using in SQL queries generated by the Strategy SQL engine. Strategy application developers can further optimize SQL for their specific SAP HANA environment using these string insertion settings. Possible locations for VLDB optimizations in the query structure are listed below.

VLDB String Insertion Settings when using Temporary Tables

[Report Pre Statements]

[Table Pre Statements]

create table <table_name>
as
select   <column_expressions>
from     <tables_and_joins>
where    <filter_expressions>
group by     <column_expressions>
having   <column_expressions> [Insert Post String]

create [Index Qualifier] index [Index Prefix]<index_name> on <table_name>(column_name) [Index Post String]

[Table Post Statements]

select   <column_expressions>
from     <tables_and_joins>
where    <filter_expressions>
group by     <column_expressions>[Select Post String][Select Statement Post String]

[Report Post Statements]

drop table <table_name>

[Cleanup Post Statements]

VLDB String Insertion Settings when using Permanent Tables
When Intermediate Table Type is set to Permanent Tables, some additional string-valued settings are enabled so that the user can customize the syntax of CREATE TABLE statement. The SQL below shows the position of these VLDB settings.

[Report Pre Statements]
[Table Pre Statements]
create [Table Qualifier] table [Table Descriptor][Table Prefix]<table_name> [Table Option]
[Table Space] as
select <column_expressions>
from <tables_and_joins>
where <filter_expressions>
group by <column_expressions>
having <column_expressions>[Insert Post String]

CREATE INDEX ON <table_name>

Full Outer Join

SAP HANA 1.0 SP2 supports FULL OUTER JOIN syntax between tables in the FROM clause. By default, Strategy will use FULL OUTER JOIN when needed because in SAP HANA, queries that perform full outer joins between several tables perform better than when the Full Outer Join VLDB setting is set to No Support where the SQL Engine will generate SQL that simulates full outer joins by using UNION and left outer joins.

Full Outer Join = Support
select coalesce(pa11.CUSTOMER_ID, pa12.CUSTOMER_ID) CUSTOMER_ID,
   a13.CUST_LAST_NAME CUST_LAST_NAME,
   a13.CUST_FIRST_NAME CUST_FIRST_NAME,
   coalesce(pa11.MONTH_ID, pa12.MONTH_ID) MONTH_ID,
   a14.MONTH_DESC MONTH_DESC,
   pa11.WJXBFS1 WJXBFS1,
   pa12.WJXBFS1 WJXBFS2
from ZZMD00 pa11
   full outer join ZZMD01 pa12
     on (pa11.CUSTOMER_ID = pa12.CUSTOMER_ID and
   pa11.MONTH_ID = pa12.MONTH_ID)
   join LU_CUSTOMER a13
     on (coalesce(pa11.CUSTOMER_ID, pa12.CUSTOMER_ID) = a13.CUSTOMER_ID)
   join LU_MONTH a14
   on (coalesce(pa11.MONTH_ID, pa12.MONTH_ID) = a14.MONTH_ID)) pa11
full outer join LU_YEAR a12
on (pa11.YEAR_ID = a12.YEAR_ID)

Full Outer Join = No Support
create table ZZOJ02 as (
select pa11.MONTH_ID MONTH_ID,
       pa11.CUSTOMER_ID CUSTOMER_ID
from   ZZMD00 pa11 ) no logging;

insert into ZZOJ02
select pa11.MONTH_ID MONTH_ID,
       pa11.CUSTOMER_ID CUSTOMER_ID
from   ZZMD01 pa11;

create table ZZOD03 as (
select distinct pa11.MONTH_ID MONTH_ID,
       pa11.CUSTOMER_ID CUSTOMER_ID
from   ZZOJ02 pa11) no logging ;

select pa11.CUSTOMER_ID CUSTOMER_ID,
       a14.CUST_LAST_NAME CUST_LAST_NAME,
       a14.CUST_FIRST_NAME CUST_FIRST_NAME,
       pa11.MONTH_ID MONTH_ID,
       a15.MONTH_DESC MONTH_DESC,
       pa12.WJXBFS1 WJXBFS1,
       pa13.WJXBFS1 WJXBFS2
from   ZZOD03 pa11
       left outer join ZZMD00 pa12
         on (pa11.CUSTOMER_ID = pa12.CUSTOMER_ID and
       pa11.MONTH_ID = pa12.MONTH_ID)
       left outer join ZZMD01 pa13
         on (pa11.CUSTOMER_ID = pa13.CUSTOMER_ID and
       pa11.MONTH_ID = pa13.MONTH_ID)
       join LU_CUSTOMER a14
         on (pa11.CUSTOMER_ID = a14.CUSTOMER_ID)
       join LU_MONTH a15
         on (pa11.MONTH_ID = a15.MONTH_ID)

Query Generation

Sub Query Type

There are cases in which the SQL Engine generates subqueries (i.e. query blocks in the WHERE clause):

Reports that use Relationship Filters
Reports that use “NOT IN” set qualification, e.g. AND NOT or AND NOT
Reports that use Attribute qualification with M-M relationships, e.g. show Revenue by Category, filter on Catalog
Reports that “raise the level” of a filter, e.g. dimensional metric at Region level, but qualify on Store
Reports that use non-aggregatable metrics, e.g. inventory metrics
Reports that use Dimensional extensions
Reports that use Attribute to attribute comparison in the filter

The default setting for Sub Query Type for SAP HANA 1.0 SP2 is Option 2 – “Where col1 in (select s1.col1…) falling back to EXISTS (select * …) for multiple columns IN.” In most cases, the subquery expression will be generated with IN syntax.

select a11.REGION_ID REGION_ID,
a13.REGION_NAME REGION_NAME,sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from "STATE_SUBCATEG_REGION_SLS" a11 join "LU_SUBCATEG" a12
     on (a11.SUBCAT_ID= a12.SUBCAT_ID)
     join "LU_REGION" a13
     on (a11.REGION_ID= a13.REGION_ID)
where ((a12.CATEGORY_ID)in (select ps21.CATEGORY_ID from #ZZMQ01 ps21))
group by a11.REGION_ID, a13.REGION_NAME

If the subquery logically requires more than one column, then an expression using EXISTS syntax is generated.

select      a11.REGION_ID REGION_ID,
   a13.REGION_NAME REGION_NAME,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from "EAT_WH2"."STATE_SUBCATEG_REGION_SLS"    a11 join   "EAT_WH2"."LU_SUBCATEG"a12
on    (a11.SUBCAT_ID= a12.SUBCAT_ID) join            "EAT_WH2"."LU_REGION"    a13 on      (a11.REGION_ID = a13.REGION_ID)
where (exists (select   *
   from #ZZTI3D3KLFMMQ001 ps21
      where ps21.CATEGORY_ID=   a12.CATEGORY_ID))
group by a11.REGION_ID,
a13.REGION_NAME

The other settings are not likely to be advantageous with SAP HANA:

Option 0 – “WHERE EXISTS (select * …)”
IN performs better than EXISTS. This setting is useful for RDBMS platforms that do not support any of the other syntax.
Option 1 – “WHERE EXISTS (select col1, col2 …)”
IN performs better than EXISTS. This setting is useful for RDBMS platforms that do not support other syntax and for which selecting column names performs better than select *.
Option 3 – “Where (col1, col2) in (Select s1.col1, s1.col2…)”
SAP HANA supports multiple columns in an IN subquery, but the performance does not show much improvement.
Option 4 – “Use temporary table falling back to [Option 0]
for correlated subqueries” IN performs better than EXISTS for. Option 6 is better than Option 4 for SAP HANA
Option 5 – “WHERE col1 IN (select s1.col1), falling back to [Option 1] for multiple columns” EXISTS (select * …) performs better than EXISTS (select col1, col2), so Option 2 is a better choice than Option 5.

Parallel SQL Execution

Parallel SQL Execution (PSE) is the ability to run some passes of a multi-pass SQL in parallel, with the idea of improving the overall report execution time. Each report that is executed in sequential mode may record projected improvement in execution time if run in parallel mode. This information can be used to turn on PSE Mode. PSE is typically useful for reports that source from multiple databases as the report SQL passes tend to be distributed across sources and there is not an increased query load on a single database due to passes being run in parallel. In addition to multisource reports, PSE can be considered for any critical or high visibility reports or for scheduled reports/cubes that are executed during off-peak hours when the load on the database tends to be low. It is important to consider the load on the database system, before turning on PSE for a large number of reports.

PSE VLDB Settings:

VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)
VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)
VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)
VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)	VLDB CategoryVLDB Property SettingValueTablesIntermediate Table TypeTrue temporary tableTablesTable Creation Typeexplicit tableIndexingIntermediate Table IndexDon’t create an indexJoinsFull Outer Join SupportSupportedSelect/InsertUNION multiple INSERTDo NOT USE UNIONSelect/InsertDistinct / Group By OptionUse DISTINCTSelect/InsertGROUP BY Non-ID AttributeUse Group byQuery OptimizationsSub Query Type2: WHERE COL1 IN (SELECT s1.COL1) falling back to EXISTS ..Query OptimizationsSQL Global OptimizationLevel 4: Level2 + Merge All Passes with Different WhereQuery OptimizationsSet Operator OptimizationEnable set operator optimizationLevelCategorySettingValueProject, ReportQuery OptimizationsParallel Query Execution- Disable (default) - EnableProject, ReportQuery OptimizationsParallel Query Execution Improvement Estimate in SQL View- Disable (default) - Enable for multiple data source reports only - Enable for all reports that support itProjectQuery OptimizationsMaximum Parallel Queries Per Report- 2 (default)

SAP HANA Query Execution (and Pre\Post)

Queries that are submitted directly to a SAP HANA cluster (via ODBCExecDirect function call: https://msdn.microsoft.com/en-us/library/ms131703.aspx) are executed solely on the master node. The SAP HANA master node will generate distributed execution plans for queries submitted as prepared statement (https://msdn.microsoft.com/en-us/library/ms131667.aspx) which typically has highly beneficial impact on system resources and query execution times.

By default Strategy submits all queries directly because of a small but real overhead introduced by preparing statements. To better support clustered deployments of SAP HANA, Strategy Intelligence Server will submit all queries as prepared statements if the following registry key is created on the Intelligence Server machine:

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Strategy\DSS Suite\ConnectionLimit]
"UsePrepareStatement"=dword:00000001