I came across problem where my query takes long time when I use UDF in my view defination as following way. 5mins to run and way more activities in traces. select dbo.fnmmyyyy(a.as_of_date) as [Report Month], --dbo.fnmmyyyy(a.as_of_date) but if I use the inline functions from SQL than query ran in 29secs and faster and less activity in trace. select convert(varchar(4), year(a.as_of_date)) + '-' + right('0' + convert(varchar(2), month(a.as_of_date)), 2) as [Report Month], Is this expected? My UDF as follow: (which pretty much does same thing as my inline function) Declare @Date as varchar(7), @mth as varchar(2), @yr as varchar(4) Set @mth = ltrim(rtrim(str(month(@MyDate)))) Set @yr = ltrim(rtrim(str(year(@MyDate)))) if len(@mth) = 2 begin Select @Date = Convert(varchar(10), cast(@yr as varchar(4))+'-'+cast(@mth as varchar(2))) end else begin Select @Date = Convert(varchar(10), cast(@yr as varchar(4))+'-0'+cast(@mth as varchar(2))) end Return @Date END
This kind of formatting should be done at the presentation layer and offload some load to IIS from DB server. Your presentation layers have built in functions that readily convert to the format you need rather than write your own function in DB to do the same thing. Let SQL Server just return data.
Thanks for the reply. The front end that we use with this query is seibel reporting and I am not sure if seibel has anything for formating. I am not seibel expert so I will pass this to seibel person. Thanks again.
Ahem, if your goal is to get a result in the format '2007-09' why don't you just simply use CONVERT (CHAR(7), <put your datetime column here>, 121) There is no need for a function here. Still Dinakars suggestion is valid. The presentation layer typically has a richer set of formatting functions.
When you run into performance problems, UDFs are one of the first things to look at. For some purposes, you cannot avoid using a UDF. But if you have an inline alternative with proper SQL syntax then always go for the proper SQL.