November 8, 2009 at 8:02 am
Hi Techies
Have gone thru various articles on CROSS APPLY to find what is the advantage it brings on to the table as compared to Joins and Subqueries?
I am using the below example: To bring all the model names from Production.ProductModel table in AdventureWorks2008 database.
Using Left outer Join:
SELECT PPM.Name,PPM.ProductModelID,PP.ProductID,PP.Name AS Product_Name
FROM Production.ProductModel PPM
LEFT OUTER JOIN
Production.Product PP
ON PP.ProductModelID =PPM.ProductModelID;
Using Cross Apply:
SELECT PPM.Name,PPM.ProductModelID,S.Product_Name,S.ProductID
FROM Production.ProductModel PPM
OUTER APPLY(
SELECT PP.ProductID,PP.Name AS Product_Name
FROM Production.Product PP WHERE PP.ProductModelID =PPM.ProductModelID ) s;
I am not going to ask you which query is better but my question is more from understanding advantage it brings on to the table as compared to Joins and Subqueries?
your help is greatly appreciated.
Thanks -- Vijaya Kadiyala
November 9, 2009 at 2:32 am
Cross Apply's usage is for joining Table1 to the results set of a Table Valued user defined function that uses one or many values from the rows in Table1 as the parameters of the function.
You can not use a normal join for this
November 9, 2009 at 3:02 am
Wouldn't it depend on the usage?
But funny you should mention this though, I was just reading Karen Delaney's query tuning and optimization book, and she gives the example in chapter 3 of a simple inner join that causes a nested loop with a predicate, but without any correlated parameter. But then she gives an identical query using a cross apply that causes the optimizer to generate an index join.
Now she notes this may or may not be desirable, so I did a bit of research and I think I can see why.
If there are few rebinds on the inner side of the correlated parameter, or if there is the ability to do a rewind when a rebind occurs, then this is not a bad thing. However, if there are a lot of rebinds and few rewinds, then it becomes less efficient.
Why this is worse than a nested loop that uses a predicate, I have no idea. Perhaps someone can fill me in on this?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply