SQL Server Performance

Peformance question with UDF used in view?

Discussion in 'Performance Tuning for DBAs' started by amu_27, Sep 4, 2007.

  1. amu_27 New Member

    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
  2. ndinakar Member

    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.
  3. amu_27 New Member

    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.
  4. satya Moderator

    Follow as explained, also let us know what you want to achieve with thsi query.
  5. FrankKalis Moderator

    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.
  6. Adriaan New Member

    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.

Share This Page