Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
1  
Are you expecting different answers here than on your other question? –  Aaron Bertrand Aug 14 '12 at 15:58
    
Yes, I'm trying to broaden the audience to get a different perspective. another user from my other question suggested i post here. –  FistOfFury Aug 14 '12 at 16:07
add comment

2 Answers

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.

share|improve this answer
    
the UDFs I'm wondering about are actually table valued functions, not scalar functions. I believe the 1-row-at-a-time problem with UDFs only occurs when you are using scalar functions. –  FistOfFury Aug 15 '12 at 18:41
    
if they are inline-TVF then the optimizer should treat them the same as a view and inline the SQL directly into your query. if they are multi-statement TVF then it will depend on what it is doing to generate the result set before passing it back to the main query –  Mr.Brownstone Aug 15 '12 at 18:47
    
got it. thanks @mr.brownstone –  FistOfFury Aug 15 '12 at 19:05
add comment

I agree with the idea of recording these attributes into a denormalised table rather than calling functions and unpivoting results, etc. If you data volume is very low, using functions is not an issue, but in most busisnesses the data volume grow gradually (and sometimes fast). Therefore you will end up seeing your queries running slower and slower. Please have a look at the link below which tells you about Functions disadvantaged performance.

http://sqlblog.com/blogs/hugo_kornelis/archive/2012/05/20/t-sql-user-defined-functions-the-good-the-bad-and-the-ugly-part-1.aspx

If you still want to keep the logic wrapped rather than storing in denormalised tables, a better approach would be using views which you can filter easier by any column available in there.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.