Join optimization for relationships

You can optimize joins by applying filters to them.

A dashboard might need a query that requires a relational join across multiple data sources. For example, a transaction database might be used to locate a set of customer details that are then joined to a corporate sales warehouse. You can create joins across different relational data sources through local query execution.

You can optimize how these joins are run by using filters. The query that is driving the join is run, and a set of key values is gathered and then added to the query that is run against the other data source. By extending the predicates (filter criteria) to the data source, you can reduce the amount of local data processing for the join. As a result, performance is improved significantly.

The following join filters are available:

No filtering

The optimization is turned off.

Unique values

Values from the table with one cardinality are used to filter values from the table with the many cardinality. The filter uses a single IN expression. For a 1-to-1 relationship, the filtering is applied to the second table.

Range of values

Values from the table with one cardinality are used to filter values from the table with the many cardinality. The filter uses a single BETWEEN expression with the minimum and maximum values. For a 1-to-1 relationship, the filtering is applied to the second table.

Unique values in a subquery

Values from the table with one cardinality are used to filter values from the table with the many cardinality. The filter is generated in a subquery. For a 1-to-1 relationship, the filtering is applied to the second table.

Unique or range of values

The cardinality is ignored, and values from the first table are used to filter values from the second table. This filter uses either IN or BETWEEN predicate. An error message is displayed if the optimization can't be applied.

If the data server types are different, ensure that the data types of the matched columns are compatible. Otherwise, you might need to edit the join expression to explicitly cast the data types.