Function performance

set statistics time on

create FUNCTION [dbo].[Test](@parm int)
RETURNS int AS
BEGIN
	declare @var int
	set @var = 2
	return @parm * 2
END


select dbo.Test(a.column_id)
from
	sys.all_columns a
	join sys.all_columns b on a.column_id = b.column_id

select a.column_id * 2
from
	sys.all_columns a
	join sys.all_columns b on a.column_id = b.column_id

SQL Server parse and compile time:
CPU time = 32 ms, elapsed time = 32 ms.

(4311158 rows affected)

SQL Server Execution Times:
CPU time = 7781 ms, elapsed time = 9804 ms.

(4311158 rows affected)

SQL Server Execution Times:
CPU time = 625 ms, elapsed time = 9262 ms.

Looks like you results are reversed. When I ran your code (just 100,000 rows) I got:

Q1 (with function): SQL Server Execution Times:
CPU time = 641 ms, elapsed time = 700 ms.
(100000 rows affected)

Q2: (inline): SQL Server parse and compile time:
CPU time = 22 ms, elapsed time = 22 ms.
(100000 rows affected)

Pretty much what I expected and what most SQL devs have experienced.

I suggest you read https://www.mssqltips.com/sqlservertip/4689/understand-the-performance-behavior-of-sql-server-scalar-user-defined-functions/

There are other implications. Check out the note on parallelism.

Do you have an SSD?

The point ALL devs are missing:

Performance on these scalar functions is less important than readable code.

I agree, in general. but when the performance diff is 30 x as in my results with your query, performance wins over readability, hands down

I'm not running on an SSD. I'm running on a multi-terrabyte server

Scalar functions do have some inherent overhead. Therefore, be sure to make them as efficient as possible (no need to declare a local variable). Whenever possible, use an in-line-table-value function instead of scalar, because they perform vastly better.

CREATE FUNCTION [dbo].[Test]
(
    @parm int
)
RETURNS int AS
BEGIN
RETURN (
	SELECT @parm * 2
)
END
1 Like

Be warned. I don't care how good you think you are.

If you don't use functions, I won't hire you.

The IT industry is going to be undergoing changes. Hacking culture has taken root at every corporation.

You either write clear, concise, easy-to-read, easy-to-maintain code or you'll be considered one of the hackers.

Heh... Be warned... I don't care how good you think you are. If you write functions that contain the word "BEGIN", I won't hire you. :wink:

Now THAT I agree with. However, it doesn't mean that the code has to be slow and it doesn't mean that I can't use high performance techniques just because someone else doesn't know them. I DO comment the code correctly when I believe that I've used something that the common developer may not grasp.

I strongly disagree with this notion. Both are equally important and second only to working correctly and in a secure fashion.

Other than making several mistakes in this test, which may have seriously affected the outcome, do you have a question on this?

I kind of agree that functions don't require BEGIN.

I forgot the syntax, so I used the template and didn't bother fixing it. It's been ~5 years since I wrote code.

It looks like the results are:

Functions require lots of CPU.

Also, CPU cycles are cheap. Human brain cycles are not.

True but irrelevant here. Elapsed run time matters. If I have a query that runs for 2 hours (I do. Very large tables.) and I can make it run in 10 min by eliminating a function call at the expense of some non-DRY code, I'll do it.

OTOH, if you wouldn't, you'll never work for me!

1 Like

I agree with @gbritton above... CPU cycles aren't actually cheap. They appear to be so when you're working with a single little thing, like your function example, but you have to remember that there will be thousands of such things operating billions of times possibly every hour.

Any way, getting back to your original post, what is the overall purpose of your post. Are you trying to prove that there's not much difference between using a well encapsulated function, which supports both readability of the code that uses it and provides standardization of methods so that Developers don't need to recreate the proverbial wheel, possibly doing it incorrectly? If so, then I completely agree but your code in your original post is actually testing for such a thing incorrectly. Please see the following article for a discussion and code that tries to drive the point that I think you're trying to make home. It'll also explain my comment on why you shouldn't use functions that contain the word "BEGIN".

You are stating the exception case as the rule.

Heh... first, the problem in the article is actually identical to the original problem that you posed so I'm not sure where you're coming from now. And what exception are you talking about? I've found that the principles express both in what we're saying and what are in the article I cited solve a lot of the supposed problems that people regularly cite about using functions or not. There doesn't appear to be an "exception case" here, so you need to explain what you mean by "You are stating the exception case as the rule".

Most servers aren't running at 50+% CPU for 8 hours per day.

Readable code is more important. IF something is a performance problem, you can cut and paste the code and add a comment. EDIT: you'd probably rewrite the query, but you understand. Readable first.

YOUR brain cycles are more important. The human brain stores a finite # of short-term memory items. Push a few out to functions and you'll perform better.

How do you know that? Our CPUs are frequently above 50% 24 hours a day. if they were not, our management would rightly ask why we don't go for cheaper servers. Anyway, the problems I see with functions concern elapsed time and sub-optimal query plans. Jeff's comment about ISFs is an obvious way around that: preserve encapsulation without a penalty!

Like most things, "it depends". Though I certainly agree with avoiding premature optimization and generously commenting code when a particular, perhaps arcane, optimization is made.