Why is CROSS APPLY so slow? Part 1: Function Call Overhead

by | Dec 5, 2013

 

 

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.numberAS 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

Query Result Set

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 and all of Steven’s blogs here

We run regular Agile courses with a business intelligence slant in both Wellington and Auckland. Find out more here.

4 Comments
  1. Chris Belakhoua

    You can inline your function and end up with the same performance:
    Create FUNCTION [dbo].[Ufndouble] (@in INT)
    returns TABLE AS
    RETURN (
    SELECT @in * 2 as doubled
    );

    Reply
  2. Steven Ensslen

    Chris is absolutely correct. Your first choice for putting code into your database should be views. When views fail you, which should be unusual, inline table valued functions, like the one Chris provided, are the next choice. There are however, algorithms that can’t be specified in this fashion. These are the ones that cause the biggest performance impact.

    Reply
  3. Richard Lionheart

    The syntax highlighting used here is bad. All the different fonts and sizes makes the sql code hard to read 🙁

    Reply
    • Bronwyn Scott

      Hi Richard. Thanks for letting us know! You’ll see that this blog was written back in 2013 so needed an update, hopefully the code is a bit easier to read now. Cheers, Bronnie.

      Reply
Submit a Comment

Your email address will not be published. Required fields are marked *