SQL Server 2017: Interleaved MSTVFs Vs Inline Table Valued Functions

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.

Here are my queries.

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

No funny business

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

Double filtered for your pleasure

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!

, , ,
Previous Post
SQL Server 2017: Interleaved Execution for MSTVFs
Next Post
Adaptive Joins And Scalar Valued Functions

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}