SQL Server - Function, performance issue

Question

Let's consider a simple query:

select distinct p.id
from posts p 
inner join Users u on u.id = p.owneruserid
inner join votes v on v.UserId = u.Id
inner join Badges b on b.UserId = u.Id
where CommentCount > 1 and p.id < 100000

When I run this query directly: elapsed time = 7667 ms. But if I wrap this query into a function:

alter function getPostsWhereCount()
returns @ids table (id int)
as
begin
    insert into @ids
    select distinct p.id
    from posts p 
    inner join Users u on u.id = p.owneruserid
    inner join votes v with on v.UserId = u.Id
    inner join Badges b with on b.UserId = u.Id
    where CommentCount > 1 and p.id < 100000

    return
end

And run: select * from getPostsWhereCount()

elapsed time = 10722 ms.

Also, I've noticed that CPU time = 20779 ms for the first query and CPU time = 10766 ms for the last one

Why there are differences?

Use an inline table value function, not a multiline one. Also [Bad habits : Putting NOLOCK everywhere](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere). — Thom A, Dec 09 '19 at 20:42
Can't see the reason for the function since it has no parameters. might as well just create a view -- and please remove NOLOCK — user1443098, Dec 09 '19 at 21:03
Ah then we need the real code to try to answer the question properly, I think — user1443098, Dec 09 '19 at 21:30
This example is full. And describe the same issue.@Larnu suggestion solve the issue, but I still don’t have full image ‘why? — Basil Kosovan, Dec 09 '19 at 21:34
Shall we assume that you flushed the cache between runs and averaged multiple runs? Have you looked at the _actual execution plan_ for each query? What differences did you find? — HABO, Dec 10 '19 at 04:27
@HABO If you run `select * from getPostsWhereCount()` - execution plan just shows one scan and doesn't show internal operation from function — Basil Kosovan, Dec 10 '19 at 12:24
That would indicate that you didn't use an inline table function. — user1443098, Dec 10 '19 at 15:10

score 1 · Accepted Answer · edited Dec 10 '19 at 12:14

As mentioned, an inline table value function will be faster. You asked for a link, here's a few, some with more links to related topics on this subject.

Set based plan runs slower than scalar valued function with many conditions

T-SQL UDF vs full expression run-time

When would you use a table-valued function?

CTE slow performance on Left join

Below is what your function should look like. Leaving aside the topicsof parallelism, restricted options for the optimizer, etc... A multi-statement table valued function required you to create and populate a table variable before returning the results whereas an inline table valued function simply expands the underlying SQL and returns the results.

create function getPostsWhereCount()
returns table as RETURN
select distinct p.id   
from posts p 
inner join Users u on u.id = p.owneruserid
inner join votes v with on v.UserId = u.Id
inner join Badges b with on b.UserId = u.Id
where CommentCount > 1 and p.id < 100000;
GO

You can make your query and/or function even faster by turning your join ogic into an indexed view.

select p.id, members = COUNT_BIG(p.id)
from posts p 
inner join Users u on u.id = p.owneruserid
inner join votes v with on v.UserId = u.Id
inner join Badges b with on b.UserId = u.Id
where CommentCount > 1 and p.id < 100000;
group by p.id;

You can just use this or create an inline table valued function to call it.

SQL Server - Function, performance issue

1 Answers1