SQL Server 2017: Interleaved MSTVFs Vs Inline Table Valued Functions

Last Updated 7 years ago

Adaptive Joins, Execution Plans, SQL Server 2017

But is it faster?

Now, I know. There are very few “always” things out there in SQL Server. This is also true for functions.

A lot of the time — I might even say most of the time, inline table valued functions are going to be faster that scalar and multi statement table valued functions.

Before you huff and puff, I’ve seen cases where a scalar valued function was faster than either other kind of function.

Of course, Jeff Moden was involved, so there is clearly black magic at play here. Or maybe just some more beer popsicles.

Unfortunately… Or, I don’t know, maybe fortunately, Microsoft doesn’t seem to have been putting much development time into improving the performance characteristics of scalar valued functions. Just little bits.

Us vs Them

Let’s get back to the point at hand, though. MSTVFs have been improved in certain circumstances. Inline table valued functions are the reigning champions.

How do they stack up?

I’m going to take my best-timed MSTVF with Interleaved Execution, and put it up against an inline table valued function.

Here are my functions.

CREATE FUNCTION [dbo].[MultiStatementTVF_Join] (@h BIGINT)
RETURNS @Out TABLE (UserId INT, BadgeCount BIGINT )
AS
    BEGIN
        INSERT  INTO @Out
                (UserId, BadgeCount)
        SELECT  UserId, COUNT_BIG(*) AS BadgeCount
        FROM    dbo.Badges AS b
        GROUP BY b.UserId
        HAVING COUNT_BIG(*) > @h
        RETURN;
    END;

CREATE FUNCTION [dbo].[ITVF_Join] (@h BIGINT)
RETURNS TABLE
AS
    RETURN
        SELECT  UserId, COUNT_BIG(*) AS BadgeCount
        FROM    dbo.Badges AS b
        GROUP BY b.UserId
        HAVING COUNT_BIG(*) > @h;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

CREATE FUNCTION [dbo].[MultiStatementTVF_Join] (@h BIGINT)

RETURNS @Out TABLE (UserId INT, BadgeCount BIGINT )

AS

BEGIN

INSERT INTO @Out

(UserId, BadgeCount)

SELECT UserId, COUNT_BIG(*) AS BadgeCount

FROM dbo.Badges AS b

GROUP BY b.UserId

HAVING COUNT_BIG(*) > @h

RETURN;

END;

CREATE FUNCTION [dbo].[ITVF_Join] (@h BIGINT)

RETURNS TABLE

AS

RETURN

SELECT UserId, COUNT_BIG(*) AS BadgeCount

FROM dbo.Badges AS b

GROUP BY b.UserId

HAVING COUNT_BIG(*) > @h;

Here are my queries.

SELECT u.Id, mj.*
  FROM dbo.Users_cx AS u
  JOIN dbo.MultiStatementTVF_Join(0) mj
  ON  mj.UserId = u.Id
WHERE u.LastAccessDate >= '2016-12-01'

SELECT u.Id, mj.*
  FROM dbo.Users_cx AS u
  JOIN dbo.ITVF_Join(0) mj
  ON  mj.UserId = u.Id
WHERE u.LastAccessDate >= '2016-12-01'

1

2

3

4

5

6

7

8

9

10

11

SELECT u.Id, mj.*

FROM dbo.Users_cx AS u

JOIN dbo.MultiStatementTVF_Join(0) mj

ON mj.UserId = u.Id

WHERE u.LastAccessDate >= '2016-12-01'

SELECT u.Id, mj.*

FROM dbo.Users_cx AS u

JOIN dbo.ITVF_Join(0) mj

ON mj.UserId = u.Id

WHERE u.LastAccessDate >= '2016-12-01'

Start your (relational) engines

First, here’s are the stats on my MSTVF

Table 'Users_cx'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 626, lob physical reads 0, lob read-ahead reads 0. Table 'Users_cx'. Segment reads 1, segment skipped 0. Table '#B42CA826'. Scan count 1, logical reads 754, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1117 ms, elapsed time = 1122 ms.

Here’s the execution plan (I collected this separately from getting the CPU timing).

Second, here are the stats on my inline table valued function.

Table 'Users_cx'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 626, lob physical reads 0, lob read-ahead reads 0. Table 'Users_cx'. Segment reads 1, segment skipped 0. Table 'Badges'. Scan count 1, logical reads 1759, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 271 ms.

Here’s the execution plan (again, collected this separately from getting the CPU timing).

Well then

In this case, the inline table valued function wiped the floor with the MSTVF, even with Interleaved Execution.

Obviously there’s overhead dumping that many rows into a table variable prior to performing the join, but hey, if you’re dumping enough rows in a MSTVF to care about enhanced cardinality estimation…

Picture my eyebrows.

Picture them raising.

Thanks for reading!