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