Subquery Optimizations
TDV can analyze your subqueries and rewrite them as joins, if they meet the requirements for this subquery optimization. Often better performance can be achieved with joins as opposed to correlated subqueries.
Requirements
• Subquery starts with IN or =.
• Subquery starts with ‘=’ and includes an aggregation.
• The subquery can contain only one selectable of type COLUMN, LITERAL, FUNCTION, or AGGREGATE FUNCTION.
• The subquery can include SELECT, DISTINCT, AGGREGATE, FROM, RELATION and WHERE.
• Co-relation in the subquery can occur only in the WHERE clause for this feature. If co-relation occurs anywhere else, rewrite to join won't happen.
• Subquery operator of type SELECT, DISTINCT, AGGREGATE, FROM and WHERE clause. Or a sub-query contains a WHERE clause with a co-related predicate with a HAVING clause.
• Only co-related predicates is supported for the subquery WHERE clause.
• The co-related columns in the queries must come directly from the parent. Columns from a grandparent level are not supported.
• Type mismatches disqualify the subquery for rewriting.
To enable the subquery optimization
1. In Studio, navigate to Administration > Configuration.
2. Expand Server > SQL Engine > Optimizations.
3. Locate the Rewrite IN Sub-Queries as Joins configuration parameter.
4. Validate or change the value to true.
5. Locate the Use Semi-Join While Rewriting IN Sub-Queries as Joins configuration parameter.
6. Review the value of the configuration parameter and determine if you want to change it.
7. Leave the value or make a change.
8. Select Apply or OK.
The change in the parameter takes effect immediately. All queries that begin processing after the configuration parameter change are analyzed to see if they can take advantage of the optimization. There is no need to restart the server.