I was testing the costs of using a simple function inside a query because I read it is slowing down queries so I made a sum function and tested it on a numbers table (100000 numbers). When I saw the results I was confused because I didn't see any difference.
I there any reason why I don't see a performance gain when I don't use the function...?
This is my function:
create function [dbo].[calculator]
(
@a bigint,
@b bigint
)
returns bigint
as
begin
return @a + @b;
end
And here are the queries:
declare @top bigint = 100
select
n.n,
n2.n,
dbo.calculator(n.n,n2.n)
from dbo.Nums100 n
cross join dbo.Nums100 n2
where dbo.calculator(n.n,n2.n) < @top;
select
n.n,
n2.n,
n.n+n2.n
from dbo.Nums100 n
cross join dbo.Nums100 n2
where n.n + n2.n < @top;
select
n.n,
n2.n,
calc.s
from dbo.Nums100 n
cross join dbo.Nums100 n2
cross apply(values(dbo.calculator(n.n,n2.n)))calc(s)
where calc.s < @top;
SQL performance is a very complex art.
The cost of functions usually isn't the function itself(unless complex) but usually the overall impact on the query. For example using a Table Valued function can easily cause stats loss meaning SQL will plan poorly for the number of returned rows. Using a scalar function as posted may be executed prior to all the filtering, and may thus be ran more times than needed (not significant in your example because it isn't complex) Also, when used in filtering or join expressions they can cause indexes not to be used, and can wreak havoc in the execution plan(sometimes resulting in table scans). Sometimes it can also cause loss of parallelism, something really only seen at large scale.
Moral of the story? If it works fast enough for you in your specific case great. But don't try to scale down or overly simply the problem and assume the results will directly scale.
The functions themselves are not a problem, but they can cause issues if not used carefully (like most other SQL features)