When joining information objects built from different data sources, the Actuate SQL compiler chooses a join algorithm. If you have a good understanding of the size and distribution of the data, however, you can specify the join algorithm. Choosing the correct join algorithm can significantly reduce information object query execution time. Actuate SQL supports three join algorithms:
When you join information objects that are built from the same data source, specifying a join algorithm has no effect. The join is processed by the data source.
|
The left side of the join statement is executed, retrieving all the results. The results are then processed one at a time (pipelined).
|
|
|
For each left side result, the right side of the join is executed, parameterized by the values provided by the current left side row.
|
A dependent join is advantageous when the cardinality of the left side is small, and the selectivity of the join criteria is both high and can be delegated to the data source. When the cardinality of the left side is high, a dependent join is relatively slow because it repeatedly executes the right side of the join. A dependent join can be used for any join criteria. Only join expressions that can be delegated to the right side’s data source result in improved selectivity performance.
|
|
The left side of the join statement is executed, retrieving all the results sorted by the left side data source. The results are then processed one at a time (pipelined).
|
|
|
The right side of the join statement is executed, retrieving all the results sorted by the right side data source. The results are then processed one at a time (pipelined).
|
A merge join supports only an equijoin. A merge join has much lower memory requirements than a nested loop join and can be much faster. A merge join is especially efficient if the data sources sort the rows.
|
|
The left side of the join statement is executed, retrieving all the results. The results are then processed one at a time (pipelined).
|
|
|
The right side of the join statement is executed. The results are materialized in memory. For each row on the left side, the materialized results are scanned to find matches for the join criteria.
|
A nested loop join is advantageous when the cardinality of the right side is small. A nested loop join performs well when the join expression cannot be delegated to the data source. A nested loop join supports any join criteria, not just an equijoin.
A nested loop join is a poor choice when the cardinality of the right side is large or unknown, because it may encounter memory limitations. Increasing the memory available to the Integration service removes this limitation. The Integration service parameter Max memory per query specifies the maximum amount of memory to use for an Integration service query. For more information about this parameter, see Configuring BIRT iServer.
