Why a Table-Valued function could be killing your performance

SQL Server – Why a Table-Valued function could be killing your performance

In this blog I’m going to walk you through a serious SQL Server performance problem I had in a customer’s production environment. Turned out that although the results were good, a misbehaving table-valued function in one query was executing thousands of times more than necessary, and killing the performance.

The code, as I found it, looked like this. Nothing unusual, right? So let’s lift up the bonnet and see what’s leaking underneath:

SELECT C.CustomerID, O.OrderDate, TF.[Address]
FROM   #First100Customers AS C
       INNER JOIN dbo.Orders AS O ON C.CustomerID = O.CustomerID
       CROSS APPLY ( SELECT * FROM dbo.tfnGetRecentAddress(O.CustomerID)) AS TF

Note – For this example we’re using SQL Server 2012 (or earlier). The behaviour is different in later versions, which we’ll come on to.

Here’s the data script so you can follow along:

IF EXISTS (SELECT * FROM Sys.objects WHERE Name = 'Orders' AND type = 'U')
    DROP TABLE dbo.Orders
GO

IF EXISTS (SELECT * FROM Sys.objects WHERE Name = 'CustomerAddress' AND type = 'U')
    DROP TABLE dbo.CustomerAddress
GO

IF EXISTS (SELECT * FROM Sys.objects WHERE Name = 'Customers' AND type = 'U')
    DROP TABLE dbo.Customers
GO

CREATE TABLE dbo.Customers (CustomerID int IDENTITY(1,1) PRIMARY KEY, Name nvarchar(200))
CREATE TABLE dbo.Orders (OrderID int IDENTITY(1,1) PRIMARY KEY, OrderDate datetime, CustomerID int REFERENCES Customers(CustomerID))
CREATE TABLE dbo.CustomerAddress (ID int IDENTITY(1,1), CustomerID int REFERENCES dbo.Customers(CustomerID), [Address] nvarchar(500), 
    CreatedDate datetime DEFAULT getdate())

Now let’s populate our tables with some dummy data. Note that the script is creating 1,000 customers, each with 2 addresses and 10 orders:

DECLARE @Cnt smallint = 0, @oCnt int, @CustomerID int
WHILE @Cnt < 1000
BEGIN
    -- Create customer
    INSERT INTO Customers (Name) 
    VALUES ('aaa')
    
    SET @CustomerID = SCOPE_IDENTITY() -- Create 2 addresses per customer
    
    INSERT INTO CustomerAddress (CustomerID, [Address]) 
    VALUES (@CustomerID,'Dummy Address 1')

    INSERT INTO CustomerAddress (CustomerID, [Address]) 
    VALUES (@CustomerID,'Dummy Address 2') -- Create 10 orders per customer

    SET @oCnt = 0
    WHILE @oCnt < 10
    BEGIN
        INSERT INTO Orders (OrderDate, CustomerID) VALUES (getdate(), @CustomerID)
        SET @oCnt = @oCnt + 1
    END
    SET @Cnt = @Cnt + 1
END

And finally, let’s create our Table-valued Function. what the TVF does is irrelevant to the problem I’m highlighting, but it’s nice to use a real-world example, so this one is picking up the most recent address of the customer (and yes you’re right – this should be an inline TVF!):

CREATE FUNCTION dbo.tfnGetRecentAddress(@ID int)
RETURNS @tblAddress TABLE
(
    [Address] varchar(500) NOT NULL
)
AS
BEGIN
    INSERT INTO @tblAddress ([Address])
    SELECT TOP 1 [Address]
    FROM   dbo.CustomerAddress
    WHERE  ID = @ID
    ORDER  BY CreatedDate desc
    RETURN
END
GO

Right, we’ve got all our objects in place, now we can move onto the problem in hand. Let’s say we write a query that returns the first 100 customers from the Customers table. We want it to output the CustomerID, their order date history, and their most recent address (using our TVF). To help the example, we’ll cache these customers in a temp table:

CREATE TABLE #First100Customers (CustomerID int PRIMARY KEY)

INSERT INTO #First100Customers
SELECT CustomerID
FROM   dbo.Customers
WHERE  CustomerID <= 100

SELECT C.CustomerID, O.OrderDate, TF.[Address]
FROM  #First100Customers AS C
      INNER JOIN dbo.Orders AS O ON C.CustomerID = O.CustomerID
      CROSS APPLY ( SELECT *
                    FROM dbo.tfnGetRecentAddress(O.CustomerID)) AS TF

fig_1

Nothing special about that. We get back 1,000 rows (100 customers x 10 orders), just as we’d expect. So job done, let’s go home, right?

Well… not quite. Take a look at the execution plan for our query, and in particularly, that TVF!

Wow! 10,000 executions of our TVF! Why? Our temp table contains only 100 customers, the JOIN onto Orders will result in 1,000 rows, so we could legitimately expect our TVF to be called 1,000 times. So why on earth is our TVF being executed 10,000 times?

Looking at the plan it starts to become clearer. The optimizer has re-written our query and decided to scan Orders first, which results in 10,000 rows (the entire table), and each ID returned is then passed into the TVF! It then executes the JOIN on to #First100Customers, which throws away the 9,000 rows that don’t match the JOIN condition. So our results look good, however the TVF was still executed 10,000 times – that’s at least 9,000 times more than was necessary.

Let’s examine how the same poorly-performing query behaves in SQL Server 2014 and SQL Server 2016:

Interestingly, now the optimiser has scanned and joined our 2 tables first, which resulted in 1,000 rows, and just 1,000 executions of our TVF – exactly what we expected.

So the answer is to tell the customer to upgrade their SQL Servers, right? Er, well – no. In our example, we can solve the problem and provide a significant performance boost, across all SQL versions, with a small code tweak.

SELECT C.CustomerID, O.OrderDate, TF.[Address]
FROM  #First100Customers AS C
      INNER JOIN dbo.Orders AS O ON C.CustomerID = O.CustomerID
      CROSS APPLY ( SELECT *
                    FROM dbo.tfnGetRecentAddress(C.CustomerID)) AS TF

The change is subtle, so I’ve highlighted it above – previously we were passing in the CustomerID from our Orders table. The data value is exactly the same, but by switching to the CustomerID from the smaller table, we force the optimiser to scan #First100Customers first, which in turn forces the optimal plan.

Let’s look at our new query plan. This plan is the same in all versions of SQL Server >= 2008.

Remember when we started there were 10,000 executions! What an improvement! Only 100 executions – 1 per entry in #First100Customers!

To many of you, this may seem very obvious and natural. However for a junior developer, it’s an easy mistake to make because our results look good. However we got a sub-optimal query plan; and therefore a slow running query. In SQL Server 2014/2016, the impact was moderate, but in 2012 or earlier, it was massive.

So what do you think? Even in the later versions, is this a bug in SQL Server? It’s arguable that the optimiser should be smart enough to recognise that#First100Customers.CustomerID and Order.CustomerID are the same value (it’s the JOIN condition!), and therefore only ever execute the TVF 100 times, regardless of which reference is passed.

Well, it doesn’t, let’s get over it. At least we know not to make that mistake again!

Before I wrap up, let’s change our example slightly. The first example illustrates an important point, but arguably it’s a junior coding mistake. However, consider this scenario where that isn’t the case:

CREATE FUNCTION dbo.tfnGetOrderPrice(@OrderID int)
RETURNS @tblOrders TABLE (
    Price numeric(9,2) NOT NULL
)
AS
BEGIN
    INSERT INTO @tblOrders (Price)
    VALUES('10.99')
    RETURN
END

GO

SELECT C.CustomerID, O.OrderDate, TF.Price
FROM  #First100Customers AS C
      INNER JOIN dbo.Orders AS O ON C.CustomerID = O.CustomerID
      CROSS APPLY ( SELECT *
                    FROM dbo.tfnGetOrderPrice(O.OrderID)) AS TF

This is a new query, calling a different TVF which takes the OrderID as the parameter (and just returning a hard-coded price for the purposes of this example). Let’s examine the execution plan in SQL 2012 or earlier:

Unsurprisingly, we’re back to 10,000 executions, and a poorly performing query. So how can we resolve this so the TVF gets executed just 1,000 times as we would expect (remember, it’s 100 customers x 10 orders)? We know from the last example that upgrading to 2014 or 2016 will fix the plan, but is there another way?

Well not that I know of, at least not an elegant way! Unfortunately, we’re going to have to start hacking.

The hack is that we can pass a dummy parameter from #First100Customers to the TVF that does nothing. That is to say, it does nothing except that the dependency forces the optimiser to produce a different plan:

ALTER FUNCTION dbo.tfnGetOrderPrice(@OrderID int, @DummyID int)
RETURNS @tblOrders TABLE (
    Price numeric(9,2) NOT NULL
)
AS
BEGIN
     INSERT INTO @tblOrders (Price)
     VALUES ('10.99')
     RETURN
END
GO

SELECT C.CustomerID, O.OrderDate, TF.Price;
FROM  #First100Customers AS C
      INNER JOIN dbo.Orders AS O ON C.CustomerID = O.CustomerID
      CROSS APPLY ( SELECT *
                    FROM dbo.tfnGetOrderPrice(O.OrderID, C.CustomerID)) AS TF

fig_11

You can see that thanks to our hack, we now have an optimal plan, resulting in 1,000 executions of our TVF, instead of 10,000.

I try to follow the mantra of “assume hacking isn’t possible”. Sometimes you don’t have a choice, but please comment if you know a nicer way of solving this problem.

My aim in this blog wasn’t to teach you a fairly dirty hack. It was to highlight this particular behaviour of TVFs, and to stress the importance of examining your execution plans carefully.

If you’re new to SQL Server, and want to make that leap from coder to developer, then it’s important to learn that SQL Server programming doesn’t stop at the moment your query returns the results you expect.

Thanks for reading!