5

I read this article quite recently on the performance issues to do with Functions.

I'm currently in the development phase of a new database on the Azure SQL Database platform. It won't go live for a new months yet. I'm using a variety of scalar-valued functions with one being to convert UTC to Local Date where it is specified in the Config table, e.g. AUS Eastern Standard Time

The function is called as the following:

SELECT
    dbo.fnGetLocalDate(DateColumn) as DateColumn,
FROM
    table

Does anyone have any suggestions on how to avoid functions in cases such as this? I find them useful for code reuse and I'm not sure how to avoid it here.

I'm also wondering whether with vNext fixing the performance issues with Functions in the Azure platform that I'm better off continuing to work with functions.

CREATE FUNCTION [dbo].[fnGetLocalDate]
(
    @DateToConvert datetimeoffset = NULL
)
RETURNS datetimeoffset
AS
BEGIN

    DECLARE @TimeZone varchar(50)

    RETURN 
    CASE 
        WHEN @DateToConvert is NULL then NULL
    ELSE 
        CONVERT(datetimeoffset, @DateToConvert AT TIME ZONE (SELECT Value FROM LookUp.Config WHERE Property = 'TimeZone'))
    END

END
  • You can rewrite it as an inline TVF returning a single column and row and CROSS APPLY it to get the benefits of inlining now without having to wait. – Martin Smith Feb 6 '18 at 11:16
  • Thanks for the suggestion @MartinSmith - would you mind responding with an answer? – Philip Feb 6 '18 at 11:17
  • 1
    Functions aren't automatically bad (well, multi-statement table-valued functions can be). I wouldn't assume that just because you're using a function that you automatically have a problem here. Are you seeing issues within the execution plans or on wait stats on the queries? If not, I wouldn't necessarily sweat this particular function. It looks largely benign. Add to it @MartinSmith approach and you should be fine. – Grant Fritchey Feb 6 '18 at 12:41
  • FWIW, I've been down this road and you might want to consider just returning all values as UTC and let the presentation layer apply the time zone conversions as needed. It can get hairy to maintain on the database side over time. Otherwise, +1 for Martin's answer. – LowlyDBA Feb 6 '18 at 14:28
6

You can rewrite it as an inline TVF returning a single column and row and CROSS APPLY it to get the benefits of inlining now (parallelism, no overhead of switching execution contexts, holistic query costing and optimisation) without having to wait for the work done on inlining of Scalar UDFs to get released.

So your function definition would be

CREATE FUNCTION [dbo].[fnGetLocalDate] (@DateToConvert DATETIMEOFFSET = NULL) 
RETURNS TABLE 
AS 
    RETURN 
      SELECT CONVERT(DATETIMEOFFSET, @DateToConvert AT TIME ZONE 
                                     (SELECT Value 
                                      FROM   LookUp.Config 
                                      WHERE  Property = 'TimeZone')) AS 
                    LocalDate 

With example usage

SELECT o.name, L.LocalDate
FROM sys.objects o
CROSS APPLY [dbo].[fnGetLocalDate](o.modify_date) AS L

You don't really need the CASE as it will return NULL on NULL input anyway and may get better plans without it

4

C# offers the System.TimeZoneInfo class. This class exposes 2 helpful methods. The first useful method is named FindSystemTimeZoneById(). By passing the time zone id to the method you can get the TimeZoneInfo for the given Id. For example, if your Azure Website was hosted in West Europe and you wanted the time for Munich, Germany, then you could use the following code set shown below.

var cetZone = TimeZoneInfo.FindSystemTimeZoneById("Central Europe Standard Time"); 
var cetTime = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, cetZone);
MunichTimeLabel.Text = cetTime.ToString();

It seems Microsoft is recommending using a SQL Server function on this article.

These two are the two options I know you can use.

  • Thanks Alberto, and yes that's where I got the initial idea from to do a Function call for this. – Philip Feb 6 '18 at 4:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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