September 18, 2009 at 10:36 am
So we are thinking about about creating SQL functions to call look up data for our application. Are there any drawbacks in using functions instead of doing a JOIN. Does this effect preformance at all?
September 18, 2009 at 10:39 am
In my opinion, functions are OK if you are getting a single row of data. but if you are getting more than one row, a functions should be replaced with a join;
functions are called fo reach row, and lose out on the performance advantage a join would use.
Lowell
September 18, 2009 at 1:16 pm
I'm a little confused on the idea of a function vs. a join. If you have two sets of data that you want to put together, you have to do a join.
But, that said, assuming you're talking about user defined functions, they can be very problematic. It really depends on how much data you're moving and whether or not the function is inline. If it's inline, a single statement UDF, it can be very fast, more or less like a parameterized view. If it's a multi-statement UDF, it's going to tend to be very problematic because it uses table variables. Table variables have no statistics, which means that execution plans made for them are based off of one row. As long as you're only returning one row, or a few rows, you'll be fine. Once it's hundreds or thousands... performance gets very bad.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 18, 2009 at 2:18 pm
Lowell (9/18/2009)
In my opinion, functions are OK if you are getting a single row of data. but if you are getting more than one row, a functions should be replaced with a join;functions are called fo reach row, and lose out on the performance advantage a join would use.
I'm not sure I folow this argument. If the function itself returns all of the information you need, and there is no need to join this data to anything else, then the frunction is called just once, and essentially acts as a parameterized view.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 19, 2009 at 5:44 am
Yes, but what is occuring inside the function? Is it a single statement function with a query against a single table just with parameters or are multiple tables involved in which case, you have a function, but it has joins... I'm still stuck on this comparison between a join and a function.
A join in TSQL:
SELECT *
FROM TableA as A
JOIN TableB as B
ON A.ID = B.ID
LEFT JOIN TableC as C
ON B.ID2 = C.ID2
WHERE A.ID = 42
A user defined function in TSQL:
CREATE FUNCTION MyFunction
(@Param1 int)
RETURNS TABLE
AS
RETURN
(SELECT *
FROM TableA as A
JOIN TableB as B
ON A.ID = B.ID
LEFT JOIN TableC as C
ON B.ID2 = C.ID2
WHERE A.ID = @Param1)
Joins are there in both. Where is the comparison between the two?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 19, 2009 at 7:50 am
The short answer is they are not related JOINs in ANSI SQL are compared to Subqueries and UNION because very long ago JOINs are two or more UNIONs combined. I think you will still find code doing that I have seen some here not very long ago.
In relational algebra JOINs are idempotent but most vendor extentions comes with updatable JOINs there is a movement to remove that in future SQL Server.
🙂
Kind regards,
Gift Peddie
September 19, 2009 at 11:02 am
The big problem with functions that perform queries, is that they cause hidden joins, hence should be transformed to (well tunable) regular joins !
Functions aren't always visible at first sight when a dev or dba is interpreting a query.
The same question goes for "functional views". They have their purpose, but are far to often abused, causing performance problems.
Once these abuses are rewritten to optimal sql performance problems disapear.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 20, 2009 at 2:01 am
Would putting the queries in a Sproc be faster? I thought both functions and sprocs parsed and pre-compiled the data. Meaning that performance would be better? Well maybe not functions but sprocs for sure.
As long as the underlying tables were frequently changing I would have thought performance would have been better.. with a sproc that is ( not sure about UDF)
September 20, 2009 at 4:22 am
raym85 (9/20/2009)
Would putting the queries in a Sproc be faster? I thought both functions and sprocs parsed and pre-compiled the data. Meaning that performance would be better? Well maybe not functions but sprocs for sure.As long as the underlying tables were frequently changing I would have thought performance would have been better.. with a sproc that is ( not sure about UDF)
The huge difference of a UDF vs a SPROC is that a UDF is called by a query statement (or at column level (scalar udf) or at from level (table tvf), a sproc is executed by a exec statement.
You cannot use a sproc within a query like you can use a udf.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 20, 2009 at 4:42 pm
cm62597 (9/18/2009)
So we are thinking about about creating SQL functions to call look up data for our application. Are there any drawbacks in using functions instead of doing a JOIN. Does this effect preformance at all?
I'm not going to guess... tell us just exactly what you mean about using SQL functions to call lookup data for your application. Give us an example of what you have in mind.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2009 at 8:27 pm
raym85 (9/20/2009)
Would putting the queries in a Sproc be faster? I thought both functions and sprocs parsed and pre-compiled the data. Meaning that performance would be better? Well maybe not functions but sprocs for sure.As long as the underlying tables were frequently changing I would have thought performance would have been better.. with a sproc that is ( not sure about UDF)
There's no "pre". It's parsed and compiled on first execution, and then the plan is reused on future executions (in most cases).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 21, 2009 at 1:33 am
This really is one of my pet hates. By using scalar udfs , you are seriously handicapping your system. Apart from the obvious overhead of calling a function, SQLServer will not be able to pick an optimal query plan. The overhead can be easily demonstrated in AdventureWorks
create function GetSalesTotal(@SalesOrderId integer)
returns money
as
begin
declare @Total money
Select @total = sum(LineTotal)
from Sales.SalesOrderDetail
where SalesOrderId = @SalesOrderID
return @Total
end
go
select SalesOrderHeader.SalesOrderID,SUM(Linetotal)
from Sales.SalesOrderHeader
join Sales.SalesOrderDetail
on Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
group by Sales.SalesOrderHeader.SalesOrderID
go
Select SalesOrderID,dbo.GetSalesTotal(SalesOrderId)
from Sales.SalesOrderHeader
Using profiler :
Join CPU 297 Reads 1941 Dur 294
Udf CPU 1391 Reads 95960 Dur 1387.
So in that respect it really is a no brainer.
If you really must use a udf then try to use an inline one, they are much more efficient as they are effectively views.
create function dbo.GetSalesTotalinline(@SalesOrderId integer)
returns table
as
return(
Select SalesTotal = sum(LineTotal)
from Sales.SalesOrderDetail
where SalesOrderId = @SalesOrderID
)
go
Select SalesOrderID, SalesTotal
from Sales.SalesOrderHeader cross apply dbo.GetSalesTotalinline(SalesOrderId)
Complex logic can usually be broken down into a series of CTE's.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply