This is the first article in a series about improving the performance of SQL Server functions.

Functions add overhead. They have many benefits, including flexibility and maintainability, but they come at a cost of reduced performance. This is a simple truth everywhere in computer programming.

You’ll notice this performance problem with CROSS APPLY more than you do elsewhere for two reasons:

1. The overhead in SQL Server is larger than it is in other languages; and
2. In CROSS APPLY the function gets called over and over again, once per row.

Even the most trivial SQL Server function add significant delays.

CREATE FUNCTION [dbo].[Ufndouble]  (@in INT)returns @returntable TABLE ([doubled] INT )AS

BEGIN

INSERT @returntable

([doubled])

SELECT @in * 2

RETURNEND

This is an entirely trivial function, that runs no SQL of its own. It will still be very costly. We need a data set, here’s a simple one:

DECLARE @start INT = 1;

DECLARE @end INT = 1E5;

WITH numbers AS (SELECT @start AS number UNION ALL

SELECT number + 1

FROM   numbers

WHERE  number < @end)

SELECT number

INTO   numbertable

FROM   numbers

OPTION (maxrecursion 0);

Now we’ll run our function, and the equivalent statement without the function:

SELECT number, number * 2

FROM   numbertable

SELECT t.number, f.doubled

FROM   numbertable AS t

CROSS APPLY [dbo].Ufndouble(t.number) AS f

Finally, we ask the database how much work it did:

SELECT t.text, max_worker_time, max_logical_writes, max_logical_reads, max_elapsed_time

FROM sys.dm_exec_query_stats s

CROSS APPLY sys.Dm_exec_sql_text (s.sql_handle) t

WHERE  t.text LIKE  ‘SELECT%NUMBERTABLE%‘

AND t.text NOT LIKE ‘%DM_EXEC_QUERY_STATS%‘

ORDER  BY t.text

The function version was eight times slower and did 10,000 times more IO. Per call the function overhead was 10 logical IO and 38 milliseconds. I ran this on an idle server with lots of free RAM. In my experience, this test understates the delay incurred by even trivial functions.

So the first way to improve the performance of CROSS APPLY is not to use it or functions where performance is important. When performance is the priority try to take the logic in the function and write it either directly into your SQL or into a VIEW. If you are concerned about code control and you need to run the function’s logic on different data sets, then you’ll want to generate the VIEWs automatically.

The remaining articles in this series are techniques for tuning CROSS APPLY functions in circumstances where VIEWs are not practical.

Steven.

You can read Part 2 here.