A while back, I wrote a blog post showing the differences (including performance) between Inline Table-Valued Functions (ITVF) and Multi-Statement Table-Valued Functions (MSTVF). Since then, whenever I speak about ITVFs / MSTVFs, I’m frequently asked about how Scalar Functions (SF) perform. My stock answer has always been that they perform in a similar manner to a MSTVF, and that you should just try to use ITVFs whenever possible.

In this post, I’m going to do a head-to-head-to-head comparison of an ITVF, MSTVF and SF, all performing the same task. In order to show just the performance difference, the code inside the three functions is the same – the only differences is that necessary for that specific type of function.

For this test, I’m going to create a table with a VARCHAR(30) column. This column will hold numeric and some non-numeric characters. The test will be to return just the numeric characters from this string, in the order that they appear in the string.

The Test Data

In order to do a true performance test, you can’t test against just a few rows… you need to test against a bunch. So, let’s create a million row test table. I’ll do this in tempdb. The following script will create a column with numeric and non-numeric characters in it.

A Sampling of the output to see what we’re working with:

RowID StringOfNumbersWithNonNumbers
1 (4925397(4925397(
2 (8054770(4925397(
3 (2054713(4925397(
4 (6054656(4925397(
5 (6925511(4925397(
6 (925454(4925397(
7 (925169(4925397(
8 (2317107(4925397(
9 (9578706(4925397(
10 (5578763(4925397(

The Functions

The next step is to create the functions that the test will utilize:

Now that we have the data and functions, let’s look at what the functions are returning, and ensure that they are returning the same values:

StringOfNumbers ScalarFunction MultiStatementTVF InlineTVF
(4925397(4925397( 49253974925397 49253974925397 49253974925397
(8054770(4925397( 80547704925397 80547704925397 80547704925397
(2054713(4925397( 20547134925397 20547134925397 20547134925397
(6054656(4925397( 60546564925397 60546564925397 60546564925397
(6925511(4925397( 69255114925397 69255114925397 69255114925397
(925454(4925397( 9254544925397 9254544925397 9254544925397
(925169(4925397( 9251694925397 9251694925397 9251694925397
(2317107(4925397( 23171074925397 23171074925397 23171074925397
(9578706(4925397( 95787064925397 95787064925397 95787064925397
(5578763(4925397( 55787634925397 55787634925397 55787634925397

The Performance Test

We can see that the data being returned by each of the functions is the same, so it’s time to compare the performance of the functions. This test will create a table to hold times for each function, and then will run each function individually against all of the rows in the table (inserting into the table just prior to and immediately after running the query). The results of the query calling the function will be sent to a new temporary table. When the testing is finished, we’ll get the duration for each function:

The Results

FunctionName Duration (microseconds) PercentOfBatch
ITVF_TEST 11,961,481 4.36
SF_TEST 86,042,922 31.37
MSTVF_TEST 176,248,081 64.26

Which one of these would you prefer to be using? The ITVF is clearly the best performing. However, I expected to see the SF and the MSTVF being closer – the SF is twice as fast as the MSTVF. Notice that the MSTVF is an entire order of magnitude slower than the ITVF!

But Wait, There’s More!

No, this isn’t a commercial. But before we draw a conclusion, let’s try another test. For this test, let’s just do some simple string concatenation. To start off, we’ll make a test table with three columns, each with 5 random characters. The functions will just concatenate these columns together, with a space between the first two columns and a comma/space between the last two columns (this can be related to building a city+state+zip address line):

For an explanation of how the randomization works, please see this article by SQL Server MVP Jeff Moden.
Once again, let’s compare the results of the functions:

Which produces these results

ITVF MSTVF SF
OFOPV VOOFP, OOPVF OFOPV VOOFP, OOPVF OFOPV VOOFP, OOPVF
VFAMP PAVFM, AVMPF VFAMP PAVFM, AVMPF VFAMP PAVFM, AVMPF
MODCD DDMOC, DMCDO MODCD DDMOC, DMCDO MODCD DDMOC, DMCDO
BQQCQ QQBQC, QBCQQ BQQCQ QQBQC, QBCQQ BQQCQ QQBQC, QBCQQ
ZRBSB BBZRS, BZSBR ZRBSB BBZRS, BZSBR ZRBSB BBZRS, BZSBR
SKIRF FISKR, ISRFK SKIRF FISKR, ISRFK SKIRF FISKR, ISRFK
BZRWR RRBZW, RBWRZ BZRWR RRBZW, RBWRZ BZRWR RRBZW, RBWRZ
MPWHR RWMPH, WMHRP MPWHR RWMPH, WMHRP MPWHR RWMPH, WMHRP
JLAWD DAJLW, AJWDL JLAWD DAJLW, AJWDL JLAWD DAJLW, AJWDL
VZBKA ABVZK, BVKAZ VZBKA ABVZK, BVKAZ VZBKA ABVZK, BVKAZ

Okay, the functions are returning the same data, so let’s test their performance.

And the results are:

FunctionName Duration (microseconds) PercentOfBatch
ITVF_TEST 371,022 0.40
SF_TEST 3,356,192 3.63
MSTVF_TEST 88,856,118 95.97

Wow, this is even more impressive! Once again, the ITVF blows the other types of functions out of the water, being one order of magnitude faster than the SF and two (almost three) orders of magnitude faster than the MSTVF. The SF readily out-performs the MSTVF, being twenty-five times faster.

The Wrap Up

In the examples shown here, the ITVF clearly performs much better than either a SF or a MSTVF, and the MSTVF is also clearly the worst performing type of function, being orders of magnitude slower. However, as with all performance testing, you should test all three against a nice, large set of data (at least one million rows) for what you need the function to do, and utilize the best one for your needs.

I guess I’ll be changing my stock answer now… a SF is better than a MSTVF, but not as good as an ITVF.

Related Posts:

Comparing Inline and Multi-Statement Table-Valued Functions