T-SQL 夏令时查找表 - 性能不佳的表值函数

如果定义一个唯一行，我建议按这两列对Tzid表进行聚类。如果需要，您可以将这些列设为主键，也可以将它们设为聚集索引。DT_WhenSwitchdbo.DSTLookup

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

CREATE CLUSTERED INDEX CI_DSTLookup ON dbo.DSTLookup ([Tzid], [DT_WhenSwitch]); -- new

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);

这样做的原因是它将允许非常快速的单个行查找。对于针对要过滤的表的两个查询，并按降序[Tzid]查找第一个值。[DT_WhenSwitch]使用正确的聚集索引获取该行可以是单个聚集索引查找。

为了获得我想要的计划，我将使用APPLY和TOP运算符稍微简化 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 
WITH SCHEMABINDING
AS
RETURN
(
        SELECT
               DATEADD(SECOND, (COALESCE(S.s_DST_OffSet, 0) + COALESCE(S.s_GMT_OffSet, 0)), @StartDateTime) 'LocalStartDateTime'
             , DATEADD(SECOND, (COALESCE(E.e_DST_OffSet, 0) + COALESCE(E.e_GMT_OffSet, 0)), @EndDateTime)   'LocalEndDateTime'
        FROM (SELECT 1 t) t
        OUTER APPLY (
            SELECT TOP 1 
                D.DSTOffSetSeconds 's_DST_OffSet',
                D.GMTOffSetSeconds 's_GMT_OffSet'
            FROM dbo.DSTLookup D
            WHERE D.DT_WhenSwitch <= @StartDateTime AND D.Tzid = @Tzid
            ORDER BY D.DT_WhenSwitch DESC
        ) s
        OUTER APPLY (
            SELECT TOP 1  
                 D.DSTOffSetSeconds 'e_DST_OffSet',
                 D.GMTOffSetSeconds 'e_GMT_OffSet'
             FROM dbo.DSTLookup D
             WHERE D.DT_WhenSwitch <= @EndDateTime AND D.Tzid = @Tzid
             ORDER BY D.DT_WhenSwitch DESC
        ) e
);

这是问题中示例查询的查询计划：

正如预期的那样，我们只对聚集索引进行两次查找：

（1 行受影响）

表“DSTLookup”。扫描计数 2，逻辑读取 4，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

SQL Server 执行时间：

CPU 时间 = 0 毫秒，经过时间 = 1 毫秒。

我无法针对 SQL Server 2008 进行测试，但我认为该语法可以在该平台上运行。SQL Server 2014 的数据库小提琴。

WITH SCHEMABINDING通过添加到RETURNS TABLE子句使您的函数成为模式绑定表值函数。

所以：

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
);

这允许查询处理器“内联”函数。这允许进行多项优化，其中最重要的是能够正确理解函数中引用的对象的统计信息。

dbo.DSTLookup向表中添加聚簇索引。这允许查询执行查找而不是扫描。对于示例数据中的行数，这可能不会产生很大的差异，但对于您的真实表，它可能会产生很大的差异。

由于您有一个Id似乎是单调递增整数的列，也许这是用作聚集主键的一个很好的候选键：

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

我会考虑根据您的 TVF 添加以下索引：

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