I have a query at work with a CROSS APPLY that I found hard to read and incorrectly assumed was under performing. I tried rewriting it using an inner join to a table variable and realized I had decreased the performance four fold. My miscalculation came from my confusing CROSS APPLY with CROSS JOIN. I found a nice definition of the two provided by Pandian Sathappan.
CROSS JOIN
1.A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join.
2.The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table (N x M)
CROSS APPLY
1.The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.
2.The table-valued function acts as the right input and the outer table expression acts as the left input.
3.The right input is evaluated for each row from the left input and the rows produced are combined for the final output.
Reference:
Pandian Sathappan
CROSS JOIN
1.A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join.
2.The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table (N x M)
CROSS APPLY
1.The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.
2.The table-valued function acts as the right input and the outer table expression acts as the left input.
3.The right input is evaluated for each row from the left input and the rows produced are combined for the final output.
Reference:
Pandian Sathappan
www.SQLServerbuddy.blogspot.com
No comments:
Post a Comment