These server configuration parameters control what the TDV query engine considers a query that can benefit from semijoin optimization.

The cardinality of both sides of a potential semijoin are evaluated, and the side with smaller estimated cardinality is loaded into memory as the LHS. When the cardinality is small enough, one IN clause or an OR expression is created containing all the values in the join criteria from the LHS, which is then added to the SQL sent to the RHS.

Semijoin is limited for databases whose vendors restrict how large a SQL statement or IN/OR clause can be. If the cardinality exceeds specific data source limitations on the size of the IN clause or the OR expression, the query engine creates an execution plan that attempts a partitioned semijoin. The partition breaks the IN list into chunks of 100 or fewer unique values, and multiple queries are executed against the RHS source. If the cardinality is still too large, the system uses the HASH algorithm.

Another restriction is set to keep the LHS from inordinately burdening the join. You can configure the LHS cardinality to determine the row count that trigger an automatic semijoin.

For example, if Max Source Side Cardinality Estimate is 200 and Ratio is 10, a query where source cardinality is estimated at 50 and target at 600 triggers use of the semijoin automatically; a query with source estimate 100 and target estimate 900 does not.

If the TDV query engine can estimate the source side but not the target side, it assumes that the target-side cardinality is large and sets up a semijoin using the value of Max Source Side Cardinality Estimate.