2

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?

Improve this question

1 Answer 1

Reset to default
0

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
Improve this answer
1
  • 2
    All of the functions in my database are inline table value functions that are schema bound and deterministic. I'm quite aware of the performance implication of UDFs and msTVFs. Your answer doesn't seem relevant.
    – Michael B
    Commented May 11, 2019 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.