It’s that time of the month again… again.  Time for T-SQL Tuesday!  This month’s event is being hosted by Matt Velic (Blog | @mvelic).  The topic that he chose for this month is the APPLY operator.

This is a great topic.   I like it because, often, when I am presenting I ask the question, “who knows about the APPLY operator?”  On a good day, I have a hand or two raised in the crowd.  And frankly, more people need to know about how where to use this operator and how it works.

Rather than contrive a new scenario, I’m going to expand on last month’s Aggregating With Correlated Sub-Queries post.  In that post, I used an inline sub-query to improve the performance of a feisty query.  To get caught up to where this post starts, please read the previous post.

APPLY Solution

You are able to accomplish the same results as before by using the APPLY operator.  Instead of writing the sub-query in the SELECT, it can be included through a APPLY.  Re-writing the query in this manner would change it as below.

<br />
SELECT  ps.Name<br />
,p.ProductID<br />
,p.Name<br />
,x.DetailCount<br />
FROM    Production.ProductCategory pc<br />
INNER JOIN Production.ProductSubcategory ps<br />
ON pc.ProductCategoryID = ps.ProductCategoryID<br />
INNER JOIN Production.Product p<br />
ON ps.ProductSubcategoryID = p.ProductSubcategoryID<br />
CROSS APPLY (<br />
SELECT COUNT(*) AS DetailCount<br />
FROM   SalesOrderDetail sad<br />
WHERE  p.ProductID = sad.ProductID) x<br />
WHERE   p.Name = 'Water Bottle - 30 oz.'<br />
ORDER BY p.Name<br />

The only real change in re-writing this query – is the sub-query is moved from the SELECT statement to the FROM statement.  Add in a CROSS APPLY and you are virtually done.  A couple changes and the same performance as before.

But you aren’t going to just believe me.  The proof is in the pudding, er… execution plan.  The plan below is what we get when executing the re-written query.

As you can see, correlated sub-queries in the SELECT can also be written through the use of the APPLY operator.  This moves the sub-query into the FROM statement; which some folks are more comfortable with.

Not Just A Sub-Query

Using APPLY in this manner is not the same as using a plan old sub-query or common table expression.  In both of those other cases, SQL Server needs to obtain all of the results for the sub-query before joining to the result of the results.  Consider if you re-wrote this to use either of these two other methods.

<br />
SELECT  ps.Name<br />
,p.ProductID<br />
,p.Name<br />
,x.DetailCount<br />
FROM    Production.ProductCategory pc<br />
INNER JOIN Production.ProductSubcategory ps<br />
ON pc.ProductCategoryID = ps.ProductCategoryID<br />
INNER JOIN Production.Product p<br />
ON ps.ProductSubcategoryID = p.ProductSubcategoryID<br />
INNER JOIN (<br />
SELECT sad.ProductID, COUNT(*) AS DetailCount<br />
FROM   SalesOrderDetail sad<br />
GROUP BY sad.ProductID) x ON x.ProductID = p.ProductID<br />
WHERE   p.Name = 'Water Bottle - 30 oz.'<br />
ORDER BY p.Name;</p>
<p>WITH cDetailCount<br />
AS (<br />
SELECT sad.ProductID, COUNT(*) AS DetailCount<br />
FROM   SalesOrderDetail sad<br />
GROUP BY sad.ProductID)<br />
SELECT  ps.Name<br />
,p.ProductID<br />
,p.Name<br />
,x.DetailCount<br />
FROM    Production.ProductCategory pc<br />
INNER JOIN Production.ProductSubcategory ps<br />
ON pc.ProductCategoryID = ps.ProductCategoryID<br />
INNER JOIN Production.Product p<br />
ON ps.ProductSubcategoryID = p.ProductSubcategoryID<br />
INNER JOIN cDetailCount x ON x.ProductID = p.ProductID<br />
WHERE   p.Name = 'Water Bottle - 30 oz.'<br />
ORDER BY p.Name<br />

The resulting execution plans for both of these would be the following:

As you can see, we go backwards from what we want and end up with a plan similar to what we saw in the query from the first post that utilized a LEFT OUTER JOIN.

Hopefully this helps show how the APPLY operator can be utilized in queries you are already writing.  It provides a great method for limiting results and improving performance.  Of course, it isn’t the solution for everything but adding it to your tool belt will open up options that you may not have previously considered.

Related posts:

1. Aggregating With Correlated Sub-Queries #tsql2sday
2. Can You Dig It? – Parallelism in the Plan Cache
3. TSQL Tuesday #005 – Wait Stat Report – #TSQL2sDay