Is there any benefit for natively compiled TVFs when used in non native modules?

Posted on

Question :

I have some schema-bound inline table valued functions that I could natively compile.

However, my workflow does not use store procedures and it would only be invoked from entity framework or ado.net directly.

As the functions must be inlined into our queries, I fail to see a point in marking my functions as natively compiled. However, a colleague of mine wants to use it because it’s shiny and new.

From some brief testing there seemed to be no actual benefit. Am I mistaken or could there beneficial scenarios?

Answer :

it is usually much better to use inline table value functions instead of multi statement TVFs or scalar functions, even if they are natively compiled, so you should better try to rewrite them as inline (single SELECT statement).

Gail Shaw made a performance test with scalar functions. The native compiled version was about 10 % faster (3352 instead of 3814 millseconds, while the inline table value function only took 300 ms). So I would not waste time to optimize something for 10 % when I could reach 1266%…


Edit:
I made another test by myself comparing native compiled inline table value fuctions with common inline table value functions (none of them accessing a table, so they are only CPU bound).

The most time the native compiled version is about 10 % faster, sometimes slower (as usual, since the CPU metric fluctuates usually).

use tempdb
go
CREATE FUNCTION [dbo].[f_test]
(
   @nr int
)
RETURNS TABLE
WITH SCHEMABINDING,
     NATIVE_COMPILATION
AS
     RETURN
select L1.n n1, L2.n n2, L3.n n3, 
       calc.total

  from (select 1 as n 
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L1
  cross join (select 1 as n 
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L2
  cross join (select 1 as n 
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L3
  cross apply (SELECT L1.n * 100 + L2.n * 10 + L3.n as total) as calc
  where calc.total = @nr
go
CREATE FUNCTION [dbo].[f_test2]
(
   @nr int
)
RETURNS TABLE
WITH SCHEMABINDING
AS
     RETURN
select L1.n n1, L2.n n2, L3.n n3, 
       calc.total

  from (select 1 as n 
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L1
  cross join (select 1 as n 
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L2
  cross join (select 1 as n 
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L3
  cross apply (SELECT L1.n * 100 + L2.n * 10 + L3.n as total) as calc
  where calc.total = @nr
go
create view v_test as
select L1.n n1, L2.n n2, L3.n n3, 
       calc.total
  from (select 1 as n 
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L1
  cross join (select 1 as n 
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L2
  cross join (select 1 as n 
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L3
  cross apply (SELECT L1.n * 100 + L2.n * 10 + L3.n as total) as calc
go
set statistics time on
create table #t (total int, n1 int, n2 int, n3 int)
go
insert into #t
select f.total, f.n1, f.n2, f.n3
  from dbo.v_test as v
 cross join dbo.v_test as v2
 cross join dbo.v_test as v3
 cross apply dbo.f_test(v.total) as f
 where v3.total <= 20

go
insert into #t
select f.total, f.n1, f.n2, f.n3
  from dbo.v_test as v
 cross join dbo.v_test as v2
 cross join dbo.v_test as v3
 cross apply dbo.f_test2(v.total) as f
 where v3.total <= 20
 GO
 truncate table #t

Leave a Reply

Your email address will not be published.