Sunday, August 4, 2013

CROSS APPLY

The “CROSS APPLY” is a very important key word that can be used to simplify complex queries. It is not only simple to write, but also better in performance. To have a clear idea, let’s take two data sets as below.

CustomerIDCustomerNameLocation
1Customer1Colombo
2Customer2Kandy
3Customer3Colombo
4Customer4Galle
5Customer5Kandy
6Customer6Jaffna

This record set is from a table named “Customer” and it contains six records for six customers. Below records were taken from a table called “Order” it has different orders for the above customers.


OrderIDCustomerIDOrderDateOrderAmount
117/1/2013120
217/2/2013110
317/3/2013100
417/4/2013120
527/1/201320
627/2/201330
727/3/201345
837/1/201322
937/2/201365
1037/3/201366
1147/1/201332
1247/2/201378
1357/1/201336
1457/2/201394
1557/3/201343
1667/1/201383
1767/2/201353
1867/3/201354
1967/4/201342

Now I will execute below two queries to compare the results and execution plan. One was written with an INNER JOIN and the other was written with an CROSS APPLY key words.

SELECT *
FROM dbo.[Order] a CROSS APPLY
    dbo.Customer b
WHERE a.CustomerID = b.CustomerID

SELECT *
FROM dbo.[Order] a INNER JOIN
    dbo.Customer b ON a.CustomerID = b.CustomerID

Both the queries looks same and also not only the results, but also the execution plan is same. The execution plan can be seen below.



According to the image above, all the operators and the cost of each operator are identical. Then what is the difference?
Let’s have a look at the below query. In general terms, this will return orders with highest amount for each customer.

SELECT        a.CustomerID, CustomerName, Location, c.*
FROM            dbo.Customer AS a
    CROSS APPLY ( SELECT TOP 1 OrderID, CustomerID, OrderDate, OrderAmount
                    FROM dbo.[Order] AS b
                    WHERE a.CustomerID = b.CustomerID
                    ORDER BY OrderAmount DESC ) c

If this written, without the “CROSS APPLY” key word, we have to do little more work since the line “WHERE a.CustomerID = b.CustomerID” in above query is not supported with “INNER JOIN”.
The result of the above query looks as below which can’t be achieved with “INNER JOIN” simply.

CustomerIDCustomerNameLocationOrderIDCustomerIDOrderDateOrderAmount
1Customer1Colombo117/1/2013120
2Customer2Kandy727/3/201345
3Customer3Colombo1037/3/201366
4Customer4Galle1247/2/201378
5Customer5Kandy1457/2/201394
6Customer6Jaffna1667/1/201383


In summary, with the “CROSS APPLY”, the right table is evaluated for each row in left table and it is not the case with “INNER JOIN”.

No comments:

Post a Comment