Tabela de pesquisa T-SQL Daylight Saving - função com valor de tabela com desempenho insatisfatório

Se Tzide DT_WhenSwitchdefinir uma linha exclusiva, recomendo agrupar a dbo.DSTLookuptabela por essas duas colunas. Você pode tornar essas colunas a chave primária, se desejar, ou apenas torná-las o índice clusterizado.

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

A razão para fazer isso é permitir pesquisas de linha individuais muito rápidas. Para ambas as consultas na tabela que você deseja filtrar [Tzid]e encontrar o primeiro [DT_WhenSwitch]valor em ordem decrescente. Com o índice clusterizado correto, obter essa linha pode ser uma única busca de índice clusterizado.

Para chegar ao plano que eu quero vou simplificar um pouco o TVF com as operadoras APPLYand . TOPTambém quero deixar bem óbvio para o otimizador que recebo apenas uma linha de cada vez. Aqui está uma implementação:

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

Aqui está o plano de consulta para sua consulta de exemplo na pergunta:

Como esperado, fazemos apenas duas buscas no índice clusterizado:

(1 linha(s) afetada)

Tabela 'DSTLookup'. Contagem de varredura 2, leituras lógicas 4, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tempos de execução do SQL Server:

Tempo de CPU = 0 ms, tempo decorrido = 1 ms.

Não consegui testar no SQL Server 2008, mas acho que essa sintaxe funcionará nessa plataforma. db violino para SQL Server 2014.

Torne sua função uma função com valor de tabela vinculada ao esquema adicionando WITH SCHEMABINDINGà RETURNS TABLEcláusula.

Então:

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

Isso permite que o processador de consultas "in-line" a função. Isso permite várias otimizações, entre elas a capacidade de entender corretamente as estatísticas dos objetos referenciados na função.

Adicione um índice clusterizado à dbo.DSTLookuptabela. Isso permite que a consulta execute uma pesquisa em vez de uma verificação. Para o número de linhas em seus dados de amostra, isso provavelmente não fará uma grande diferença, mas para sua tabela real, pode fazer uma grande diferença.

Como você tem uma Idcoluna que parece ser um número inteiro monotonicamente crescente, talvez essa seja uma boa chave candidata a ser usada como chave primária clusterizada:

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

Eu consideraria adicionar o seguinte índice com base no seu TVF:

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