SYMPTOM:
In Strategy SQL Generation Engine 9.x-10.x, when there are multiple join paths for two tables, the shortest path is not always selected by the engine.
For example, in a data warehouse, there are seven tables. Their structures are described in the following graphic:
There are two paths available to join FACT_TABLE_1 and LOOKUP_MANAGER. One path includes four intermediary tables:
FACT_TABLE_1 > RELATE_ACCOUNT_REGION > RELATE_REGION_BRAND > LOOKUP_BRAND > LOOKUP_CATEGORY > LOOKUP_MANAGER
The other path (indicated in red) includes only one relate table:
FACT_TABLE_1 > RELATE_ACCOUNT_MANAGER > LOOKUP_MANAGER
It can be observed that the second join path is much shorter than the first path. For performance reasons, users would like the engine to choose the second join path in generating Structured Query Language (SQL). However, the SQL statement generated by the Strategy SQL Generation Engine actually uses the longer join path. The following table contains the desired SQL from the shorter path and the longer SQL generated by the engine:
DB object: “Microsoft SQL Server 2012” | DB object: “Microsoft SQL Server 2012” |
select ... | select ... |
CAUSE:
The Strategy SQL Generation Engine doesn't select join paths based on the length of join paths. For a report, the engine always chooses one particular path but not necessarily the shortest one.
WORKAROUND:
To force the engine to use the relate table instead of four other tables, it is necessary to break the longer path in order to make it invalid.
For example, search for the attribute defined on ACCOUNT_ID. Edit this attribute by removing RELATE_ACCOUNT_REGION table from the available table list. In that case, the ACCOUNT_ID column of FACT_TABLE_1 can't be joined with BRAND_ID column of RELATE_REGION_BRAND table because the join path between those two end tables have been removed. Since there is only join path available for FACT_TABLE_1 and LOOKUP_MANAGER, the join between those tables will always use the shorter path (the one involving RELATE_ACCOUNT_MANAGER table). The relationship between tables now is changed as follows:
NOTE:
However, with the preceding action, a new issue arises. After breaking the two-table-join-path between FACT_TABLE_1and LOOKUP_BRAND table, the joining of these two tables will take a longer path involving the following tables:
FACT_TABLE_1 > RELATE_ACCOUNT_MANAGER > LOOKUP_MANAGER > LOOKUP_CATEGORY > LOOKUP_BRAND
Given this, the central question becomes which table join is more often used, FACT_TABLE_1 with LOOKUP_MANAGER or FACT_TABLE_1 with LOOKUP_BRAND? Users may sacrifice the less often used table join for the performance of the other. Since there are several joins in this long chain and breaking any join will serve the purpose, users can do a cost-and-benefit analysis for each option to see which join they want to break.
