Performance Considerations using APPLY

In the previous post we looked at how to use the CROSS APPLY and OUTER APPLY operators as alternative to a JOIN in queries with some examples. As we wrapped that up I also mentioned that there can be drawbacks to using APPLY in our queries, particularly when we’re working with large data sets.

The issue is due to the way that the right hand side of the APPLY statement may be ran for each record in our dataset. When we’re using larger sets of data this naturally means that the processing required for those records will be amplified.

The problem

We’re going to use a simple example for this by looking at the number of columns present in tables. Below is the query we’ll start with:

SELECT
	TOP 20
	TableName = t.[name],
	c.NumberOfColumns
FROM
	sys.tables t
	CROSS APPLY (
		SELECT NumberOfColumns = COUNT(1)
		FROM sys.columns c
		WHERE c.[object_id] = t.[object_id]
		) c
ORDER BY
	ISNULL(c.NumberOfColumns, 0) DESC;

This query is very straightforward and simply returns the table name and number of columns present in the table. When we look at the query plan however we can see the impact of our APPLY being used:

We’re now checking the number of columns for every record – which we’ll need to sort – and this is creating an index spool for each of the 145 iterations. The query totals 4,279 logical reads, of which 4,172 are used by the sysobjvalues, syscolpars, and a worktable for the spools.

That’s over 97% of the reads, and bear in mind we’re only dealing with 145 records here.

What are the alternatives

We want to look at alternatives for dealing with this calculation to get the number of columns. Unfortunately that will mean unpicking the APPLY and wrapping it up elsewhere. Let’s take a look at two options for that: a sub-query and a common table expression (CTE).

Let’s start with the sub-query, and we’ll take exactly the same logic and use it in a JOIN statement but with the matching done via the join statement rather than inside the APPLY. That would look as follows:

SELECT
	TOP 20
	TableName = t.[name],
	c.NumberOfColumns
FROM
	sys.tables t
	LEFT JOIN (
		SELECT c.object_id, NumberOfColumns = COUNT(1)
		FROM sys.columns c
		GROUP BY c.object_id
	) c ON t.object_id = c.object_id
ORDER BY
	ISNULL(c.NumberOfColumns, 0) DESC;

As you can see this looks very similar to how we constructed the APPLY with the difference being where the matching between two sets of data is done.

Another alternative is a common table expression (CTE) where we declare a different set of data in advance and then reference it within our main query. This would look something like this:

WITH ColumnCounts AS (
	SELECT c.object_id, NumberOfColumns = COUNT(1)
	FROM sys.columns c
	GROUP BY c.object_id
)
SELECT
	TOP 20
	TableName = t.[name],
	c.NumberOfColumns
FROM
	sys.tables t
	LEFT JOIN ColumnCounts c ON t.object_id = c.object_id
ORDER BY
	ISNULL(c.NumberOfColumns, 0) DESC;

When wrapping the logic in a CTE we can reference that like a regular table so this type of layout can help segregate the logic in a way which can be easier to digest when someone looks at it with fresh eyes.

These two examples are simple due to the contents of our APPLY statement. If it were being used to produce a TOP (1) for example then these types of separation may take a little more consideration before refactoring.

Performance benefits

We’ve got our alternatives in place so how does the performance of these queries look now?

Exactly the same, check out our 3 queries ran back-to-back:

Minus the Compute Scalar these plans are identical – the same weighting, record counts, row estimates, etc. If we look at our logical reads too we’d see that they’re all using the same 4,279 that we started with.

You see, SQL Server will try to optimise our queries as best it can, so it knows what we’re trying to do and thinks that this is the best way to get our data in this fashion. Unfortunately linking the data together this was isn’t going to resolve our performance issue.

A better fix

There is another way which we can look to refactor our query to get the same data with lower reads – we split up our queries into two statements and store our results in between. This will segregate the logic to make sure the sections are executed independently and avoid SQL combining them in the same plan. Let’s try the below on for size:

DECLARE @ObjectColumns TABLE (
	[object_id] INT,
	NumberOfColumns INT
);

INSERT INTO @ObjectColumns
SELECT c.object_id, NumberOfColumns = COUNT(1)
FROM sys.columns c
GROUP BY c.object_id;

SELECT
	TOP 20
	TableName = t.[name],
	c.NumberOfColumns
FROM
	sys.tables t
	LEFT JOIN @ObjectColumns c ON t.object_id = c.object_id
ORDER BY
	ISNULL(c.NumberOfColumns, 0) DESC;

This is also a very readable way to look at what we’re trying to accomplish. We’re using a table variable to store our counts and joining them into our table data like any other data source. However our query plan is now split between the two sets of data:

We’ll see the same tables being used to get the column details initially and then that data is joined back in to the second query (towards the top right) but using a single table scan rather than iterated for every row as we’ve seen previously.

How do the reads look? – that’ll be just 509, over 88% saving from where we started. Also worth noting here too is that most of those reads (401) were from building up our table variable, our main query is so much more efficient.

This can be a great way to remove a bottleneck by physically splitting a single query into multiple and isolating the problem areas. The key with this approach is choosing the right points to make that separation.

Wrap up

As we said last time, using APPLY to join data into your queries can provide additional flexibility and be a great tool. When we work with larger data sets the advantages which they provide can be diminished by their performance. In these instances we’ll want to look at refactoring our queries to not require an APPLY and use methods like we’ve discussed here.

However we look at adjusting our code it’s key to look at relevant performance metrics and the execution plan as SQL Server may not be approaching the challenge as we’d expect so further changes may be needed – in this trivial case we found that splitting the query into two solved our problem.

I don’t tend to find myself using APPLY too often but when there’s a specific use case where it works well and doesn’t degrade performance it can be a wonderful tool. How do you find it?