Sql-server – User defined function performance disadvantages

functionssql-server-2005

I have a database with a lot of UDFs that are called by a long running process involving lots of data manipulation and calculations.

My thinking in using UDFs is to separate out logical units of information from the tables underlying. For example, if i am trying to get information about a car i might have several tables like Color, Model, Year, etc that i would have to join each time to get a Car. Instead, I would have a function like fnCar() to get a denormalized view of the data.

I call these functions a lot during my long running process and I'm wondering if it would be better if instead I had a denormalized working table,view, or temp table to do my data manipulation and calculations. Is there some disadvantage to using UDFs in general that I should be aware of in terms of performance?

For example, I make some calculations using a UDF. I then unpivot that data and store in a table. Whenever i need to use that data again, I call a UDF to pivot the data back out. The reason we do it this way is to keep our calculations flexible. We don't want to change the data model if we add/remove/change the calculations. 

--Calculate some values in a function

declare @location table
(
    id int,
    lattitude float,
    longitude float
)

insert into @location select  1, 40.7, 74
insert into @location select  2, 42, 73
insert into @location select  3, 61, 149
insert into @location select  4, 41, 87


declare @myLattitude float
declare @myLongitude float
set @myLattitude =43
set @myLongitude = 116

declare @distance table
(
    id int,
    distance float
)

insert into @distance
select id, sqrt(power(lattitude-@mylattitude,2)+power(longitude-@mylongitude,2))
from @location



--Store unpivoted data in a table
declare @unpivot table
(
    id int,
    attribute varchar(100),
    attributeValue float
)

insert into @unpivot
(
    id,
    attribute,
    attributeValue
)
select id
    ,attribute
    ,attributevalue 
from
(
    select 
        L.id,
        L.Lattitude, 
        L.Longitude,
        D.Distance
    from @location L 
        inner join @distance D 
        on L.id=D.id
) a
unpivot 
(
    attributeValue for attribute in
    (lattitude, longitude, distance)
) x

--retrive data from store via pivoting function for reporting

select * 
from @unpivot
pivot 
(
    max(attributeValue) for Attribute in (lattitude, longitude, distance)

) x

Best Answer

There comes a time when you have to decide what is more important to you, maintenance of the code or the speed at which it runs? The reason it is running slow is because UDF's get processed on a row-by-row basis - SQL Server performs best using set-based operations. There is no reason why you cant keep your UDF's as there may be time where it is more practical to use them than to expand out the query.

My advice is this: If your data set is small feel free to use the UDF's; but if you are working with a large data set then take the time to write, test and optimize the query to obtain the best results - it will benefit you in the long run when you don't have users complaining their system is slow.

Related Question