October 15, 2018 at 3:41 pm
I have seen developers or DBA' s using Cross apply operator?What case it needs to be used and when should be used to get the best desired result.
Appreciate all of your help
Thanks in advance!!
October 16, 2018 at 5:52 am
The basic principle of CROSS APPLY can be summed up as, for each row in my data set (defined by the table or tables you're using the CROSS APPLY against) perform this function or query. It's a little bit like a qualified, and well performing, cursor. You don't use it in place of traditional JOINs. If something is easily defined by JOIN TableX as x ON a.ID = x.ID, use the JOIN. Instead, it becomes useful when you have a function that uses values from the other table. A good example is querying the DMVs:
SELECT *
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) as deqp
The function, sys.dm_exec_query_plan, will execute for each of the rows in the system view, sys.dm_exec_query_stats, using the plan_handle to find appropriately matching rows. You can substitute queries for functions when you have a more complex query than can be simply defined by a simple join.
There isn't a hard and fast, you must use this when, rule. Generally, I just use JOIN except for functions, unless I spot that complex scenario and then I substitute the CROSS APPLY for the JOIN. However, even there, I experiment and measure to get the performance right.
The function, sys.dm_exec_query_plan, will execute for each of the rows in the system view, sys.dm_exec_query_stats, using the plan_handle to find appropriately matching rows. You can substitute queries for functions when you have a more complex query than can be simply defined by a simple join.There isn't a hard and fast, you must use this when, rule. Generally, I just use JOIN except for functions, unless I spot that complex scenario and then I substitute the CROSS APPLY for the JOIN. However, even there, I experiment and measure to get the performance right.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 16, 2018 at 5:11 pm
One thing to note about APPLY is that it will always* use a Nested Loops operator to handle joining the rows, so it's generally a bad idea if you're expecting a very large number of rows to come out of the APPLY. It's very handy, however, if you want to re-use a calculation involving multiple columns or if you need to call a TVF for each row.
*Well sometimes SQL Server is smart enough to spot it can replace an APPLY with a JOIN, at which point all the options come back into play but it's best not to rely upon that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply