Sql-server – T-SQL Daylight Saving lookup table – poorly performing table-valued function

performancequery-performanceset-returning-functionssql serversql-server-2008-r2

I've created a "Daylight Savings" lookup calendar table for the GMT region. The function I'm using to query the table to return the local datetime from a UTC datetime is performing poorly.

Any help to improve this, including changing the way the TVF is coded, would be appreciated.

The function will be used in queries that can return 1m+ rows on a frequent basis. The function is used when querying warehouse tables containing trip data.

The start and end datetimes of the trips are stored in UTC and the function above is used to convert them into local time. A developer, long since gone from the company, wrote a scalar function that converts UTC time to local time. I was tasked to rewrite that function using a calendar table and a TVF as TVF are supposed to better performing than scalar functions

Without the function:

SQL Server Execution Times:    CPU time = 4633 ms,  elapsed time = 4909 ms.

execution plan without function

With the function:

SQL Server Execution Times:    CPU time = 20795 ms,  elapsed time = 21176 ms.

execution plan with function

Here's a sample output from the table

CREATE TABLE dbo.DSTLookup 
(
     [Id] int, 
     [Tzid] int, 
     [DT_WhenSwitch] datetime, 
     [DSTOffSetSeconds] int, 
     [GMTOffSetSeconds] int 
)

INSERT INTO dbo.DSTLookup
VALUES (29, 2, N'2014-03-30T01:00:00', 3600, 0), 
       (30, 2, N'2014-10-26T02:00:00', 0, 0), 
       (31, 2, N'2015-03-29T01:00:00', 3600, 0), 
       (32, 2, N'2015-10-25T02:00:00', 0, 0), 
       (33, 2, N'2016-03-27T01:00:00', 3600, 0), 
       (34, 2, N'2016-10-30T02:00:00', 0, 0), 
       (35, 2, N'2017-03-26T01:00:00', 3600, 0), 
       (36, 2, N'2017-10-29T02:00:00', 0, 0), 
       (37, 2, N'2018-03-25T01:00:00', 3600, 0), 
       (38, 2, N'2018-10-28T02:00:00', 0, 0)

This is the TVF:

CREATE FUNCTION dbo.FN_GetLocalTime_FromUTC_BasedOnTZId 
     (@StartDateTime DATETIME, @EndDateTime DATETIME, @Tzid INT)
/*=========================================================================
*   2017-03-27
*   Returns local time from UTC time based on timeZoneId
*
==========================================================================*/
RETURNS TABLE 
AS
    RETURN
        (
         WITH cteStartDate AS
         (
            SELECT
                RN = ROW_NUMBER() OVER (ORDER BY D.Id DESC),
                D.DSTOffSetSeconds 's_DST_OffSet',
                D.GMTOffSetSeconds 's_GMT_OffSet'
            FROM
                dbo.DSTLookup D
            WHERE
                D.DT_WhenSwitch <= @StartDateTime
                AND D.Tzid = @Tzid
         ),
         cteEndDate AS
         (
             SELECT
                 RN = ROW_NUMBER() OVER (ORDER BY D.Id DESC),
                 D.DSTOffSetSeconds 'e_DST_OffSet',
                 D.GMTOffSetSeconds 'e_GMT_OffSet'
             FROM
                 dbo.DSTLookup D
             WHERE
                 D.DT_WhenSwitch <= @EndDateTime
                 AND D.Tzid = @Tzid
         ),
         cteConvertStartDate AS
         (
              SELECT
                  DATEADD(SECOND, (COALESCE(S.s_DST_OffSet, 0) + COALESCE(S.s_GMT_OffSet, 0)), @StartDateTime) 'LocalStartDateTime'
              FROM
                  cteStartDate S
              WHERE
                  S.RN = 1
         ),
         cteConvertEndDate AS
         (
              SELECT
                  DATEADD(SECOND, (COALESCE(E.e_DST_OffSet, 0) + COALESCE(E.e_GMT_OffSet, 0)), @EndDateTime)    'LocalEndDateTime'
              FROM
                  cteEndDate E
              WHERE
                  E.RN = 1
         )
         SELECT
             S.LocalStartDateTime, E.LocalEndDateTime
         FROM
             cteConvertStartDate S, cteConvertEndDate E
);
GO

To query the TVF:

SELECT * 
FROM dbo.FN_GetLocalTime_FromUTC_BasedOnTzId
    ('2017-03-27 10:00:30', '2017-03-27 10:15:54', 2);

Execution plan following Max's recommendations to include the primary key.

Best Answer

Make your function a schema-bound table-valued-function by adding WITH SCHEMABINDING to the RETURNS TABLE clause.

So:

CREATE FUNCTION dbo.FN_GetLocalTime_FromUTC_BasedOnTZId 
     (@StartDateTime DATETIME, @EndDateTime DATETIME, @Tzid INT)
/*=========================================================================
*   2017-03-27
*   Returns local time from UTC time based on timeZoneId
*
==========================================================================*/
RETURNS TABLE 
WITH SCHEMABINDING
AS
    RETURN
        (
         WITH cteStartDate AS
         (
            SELECT
                RN = ROW_NUMBER() OVER (ORDER BY D.Id DESC),
                D.DSTOffSetSeconds 's_DST_OffSet',
                D.GMTOffSetSeconds 's_GMT_OffSet'
            FROM
                dbo.DSTLookup D
            WHERE
                D.DT_WhenSwitch <= @StartDateTime
                AND D.Tzid = @Tzid
         ),
         cteEndDate AS
         (
             SELECT
                 RN = ROW_NUMBER() OVER (ORDER BY D.Id DESC),
                 D.DSTOffSetSeconds 'e_DST_OffSet',
                 D.GMTOffSetSeconds 'e_GMT_OffSet'
             FROM
                 dbo.DSTLookup D
             WHERE
                 D.DT_WhenSwitch <= @EndDateTime
                 AND D.Tzid = @Tzid
         ),
         cteConvertStartDate AS
         (
              SELECT
                  DATEADD(SECOND, (COALESCE(S.s_DST_OffSet, 0) + COALESCE(S.s_GMT_OffSet, 0)), @StartDateTime) 'LocalStartDateTime'
                  , S.RN
              FROM
                  cteStartDate S
              WHERE
                  S.RN = 1
         ),
         cteConvertEndDate AS
         (
              SELECT
                  DATEADD(SECOND, (COALESCE(E.e_DST_OffSet, 0) + COALESCE(E.e_GMT_OffSet, 0)), @EndDateTime)    'LocalEndDateTime'
                  , E.RN
              FROM
                  cteEndDate E
              WHERE
                  E.RN = 1
         )
         SELECT
             S.LocalStartDateTime, E.LocalEndDateTime
         FROM
             cteConvertStartDate S
             INNER JOIN cteConvertEndDate E ON S.RN = E.RN
);

This allows the query processor to "in-line" the function. This allows several optimizations, not least of which is the ability to properly understand statistics for objects referenced in the function.

Add a clustered index to the dbo.DSTLookup table. This allows the query to perform a lookup instead of a scan. For the number of rows in your sample data, this won't likely make a large difference, but for you real table, it may make a very big difference.

Since you have an Id column that seems to be a monotonically increasing integer, perhaps that is a good candidate key to be used as a clustered primary key:

CREATE TABLE dbo.DSTLookup 
(
     [Id] int
        CONSTRAINT PK_DSTLookup
        PRIMARY KEY CLUSTERED, 
     [Tzid] int, 
     [DT_WhenSwitch] datetime, 
     [DSTOffSetSeconds] int, 
     [GMTOffSetSeconds] int 
);

I'd consider adding the following index based on your TVF:

CREATE INDEX IX_DSTLookup_001
ON dbo.DSTLookup (DT_WhenSwitch, Tzid)
INCLUDE (DSTOffSetSeconds, GMTOffSetSeconds);

Related Solutions

Sql-server – How to avoid using variables in WHERE clause

Parameter sniffing is your friend almost all of the time and you should write your queries so that it can be used. Parameter sniffing helps building the plan for you using the parameter values available when the query is compiled. The dark side of parameter sniffing is when the values used when compiling the query is not optimal for the queries to come.

The query in a stored procedure is compiled when the stored procedure is executed, not when the query is executed so the values that SQL Server has to deal with here...

CREATE PROCEDURE WeeklyProc(@endDate DATE)
AS
BEGIN
  DECLARE @startDate DATE = DATEADD(DAY, -6, @endDate)
  SELECT
    -- Stuff
  FROM Sale
  WHERE SaleDate BETWEEN @startDate AND @endDate
END

is a known value for @endDate and an unknown value for @startDate. That will leave SQL Server to guessing on 30% of the rows returned for the filter on @startDate combined with whatever the statistics tells it for @endDate. If you have a big table with a lot of rows that could give you a scan operation where you would benefit most from a seek.

Your wrapper procedure solution makes sure that SQL Server sees the values when DateRangeProc is compiled so it can use known values for both @endDate and @startDate.

Both your dynamic queries leads to the same thing, the values are known at compile-time.

The one with a default null value is a bit special. The values known to SQL Server at compile-time is a known value for @endDate and null for @startDate. Using a null in a between will give you 0 rows but SQL Server always guess at 1 in those cases. That might be a good thing in this case but if you call the stored procedure with a large date interval where a scan would have been the best choice it may end up doing a bunch of seeks.

I left "Use the DATEADD() function directly" to the end of this answer because it is the one I would use and there is something strange with it as well.

First off, SQL Server does not call the function multiple times when it is used in the where clause. DATEADD is considered runtime constant.

And I would think that DATEADD is evaluated when the query is compiled so that you would get a good estimate on the number of rows returned. But it is not so in this case.
SQL Server estimates based on the value in the parameter regardless of what you do with DATEADD (tested on SQL Server 2012) so in your case the estimate will be the number of rows that is registered on @endDate. Why it does that I don't know but it has to do with the use of the datatype DATE. Shift to DATETIME in the stored procedure and the table and the estimate will be accurate, meaning that DATEADD is considered at compile time for DATETIME not for DATE.

So to summarize this rather lengthy answer I would recommend the wrapper procedure solution. It will always allow SQL Server to use the values provided when compiling the the query without the hassle of using dynamic SQL.

PS:

In comments you got two suggestions.

OPTION (OPTIMIZE FOR UNKNOWN) will give you an estimate of 9% of rows returned and OPTION (RECOMPILE) will make SQL Server see the parameter values since the query is recompiled every time.

Sql-server – Why would call to scalar function inside a Table Value Function be slower than outside the TVF

Scalar functions are called once-per-row, when called as part of a query.

Consider the following example.

Create a new, blank database for our tests:

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'mv')
BEGIN
    ALTER DATABASE mv SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE mv;
END
GO
CREATE DATABASE mv;
GO

Create a table, a multi-statement function, and a table-valued-function:

USE mv;
GO
CREATE TABLE dbo.t
(
    t_id int NOT NULL
        CONSTRAINT PK_t
        PRIMARY KEY CLUSTERED
);
GO

CREATE FUNCTION dbo.t_func
(
    @t_id int
)
RETURNS bit
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @r bit;
    IF EXISTS (SELECT 1 FROM dbo.t WHERE t.t_id = @t_id)
        SET @r = 1
    ELSE
        SET @r = 0;
    RETURN @r;
END
GO

CREATE FUNCTION dbo.t_tvf
(
    @min_t_id int
    , @max_t_id int
)
RETURNS TABLE 
WITH SCHEMABINDING
AS
RETURN (
    SELECT t_id = t.t_id
        , e = dbo.t_func(dbo.t.t_id)
    FROM dbo.t
    WHERE t.t_id >= @min_t_id
        AND t.t_id <= @max_t_id
);
GO

Insert some sample data into the table:

INSERT INTO dbo.t (t_id)
SELECT ROW_NUMBER() OVER (ORDER BY c.id, c.colid)
FROM sys.syscolumns c;
GO

Create a table to store function execution stats, and populate it with a start-row showing execution counts for the multi-statement-function, t_func:

CREATE TABLE dbo.function_stats
(
    run_num int NOT NULL
    , object_name sysname NOT NULL
    , execution_count int NULL 
    , CONSTRAINT PK_function_stats
        PRIMARY KEY CLUSTERED (run_num, object_name)
);
GO
INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
SELECT 1
    , o.name
    , COALESCE(fs.execution_count, 0)
FROM sys.objects o 
    LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
WHERE o.name = 't_func';
GO

Run a query against the TVF:
```
SELECT t.*
FROM dbo.t_tvf(1, 2) t;
GO
```

Capture the execution stats now:

INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
SELECT 2
    , o.name
    , COALESCE(fs.execution_count, 0)
FROM sys.objects o 
    LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
WHERE o.name = 't_func';

The function stats results:

SELECT *
FROM dbo.function_stats fs
ORDER BY fs.run_num
    , fs.object_name;

╔═════════╦═════════════╦═════════════════╗
║ run_num ║ object_name ║ execution_count ║
╠═════════╬═════════════╬═════════════════╣
║       1 ║ t_func      ║               0 ║
║       2 ║ t_func      ║               2 ║
╚═════════╩═════════════╩═════════════════╝

As you can see, the multi-statement-function has execute twice, once per row for the source table accessed by the TVF.

I expect the mutli-statement-function is being called many, many times by the TVF, giving the impression that it is running slowly, whereas in fact it is simply being called many times.

Best Answer

Related Solutions

Sql-server – How to avoid using variables in WHERE clause

Sql-server – Why would call to scalar function inside a Table Value Function be slower than outside the TVF

Related Question