A correlated query
is a SELECT expression in which a predicate within the query has a
relationship to a column that is defined in another scope. Business
and analytic intelligence tools often generate SQL queries that are
nested three or four layers deep. Queries with cross-nested relationships
consume significant processor resources and require more time to process.
Algorithms in the SQL Planner of the server implement techniques that
significantly improve the performance of correlated queries for patterns
that permit query rewrites or query decorrelation.
The SQL Planner improves
correlated query performance by changing complex rules about nested
relationships into a series of simple steps. The server can process
the simple steps much faster than it can process the complex rules
that apply to multiple levels of nesting. When a query with multiple
levels of nesting is submitted to the SQL Planner, the Planner examines
the relationships between nested and unnested sections of the query.
When the Planner finds a complex nested relationship, it restructures
or recodes the SQL query into a simpler form by using temporary server
tables.