SQL Server视图和动态SQL中AT TIME ZONE性能问题的变通方法

引言

在 SQL Server 2016 中，引入了一个新的 AT TIME ZONE 运算符。它允许将 datetime 转换为目标时区中对应的日期时间值。带有 AT TIME ZONE 的查询在小表上运行得很顺畅。但是，一旦你在拥有数百万条记录的巨大表上对表列（而不是标量表达式）应用 AT TIME ZONE，你就会遇到性能问题，例如 Jonathan Kehayias 的 文章中所描述的那样。

在我的情况下，问题在于耗时长的 SQL Server 视图，而不是动态 SQL。

自定义视图通过一个或多个 datetime 标准筛选数百万条记录 - datetime 列的值必须在浮动时间范围内。例如，Before_Now、After_Now、Today、Tomorrow、Yesterday、CurrentWeek、CurrentMonth、CurrentYear、Next_X_Hours、Previous_X_Hours 等。

这些自定义视图可以分为两类

1. 时区被指定为标量表达式。例如，AT TIME ZONE 'Pacific Standard Time';
2. 时区存储在单独的表中。例如，AT TIME ZONE (SELECT store_tz FROM [Stores] WHERE store_id = 123)。

用于第二种类型的分页的典型 CTE 查询比第一种类型花费的时间多 10 倍。有些查询甚至需要几分钟。

通过实现下面描述的解决方法，我设法将第二种类型的查询时间从几分钟缩短到几百毫秒。

解决方法技巧

首先，我们将时区周期定义为特定时区内偏移量不变的时间段

• 遵守夏令时的时区通常每年有三个周期（非夏令时⇒夏令时⇒非夏令时或夏令时⇒非夏令时⇒夏令时）。
• 最好将具有相同偏移量的相邻周期合并为一个长周期。
• 不遵守夏令时的时区将有一个覆盖几年或更长时间的周期。

我们需要时区周期的信息，以便在日期范围内正确筛选，尤其是在夏令时开始或结束时。

技巧/窍门

1. 将时区周期信息缓存在本地表中。不仅填充当前周期的信息，还填充整个当前年份、前一年和后一年的信息。年数取决于
   1. 您想多久更新一次缓存表（每年/每月/发布等）
   2. 您使用什么 datetime 标准？例如，标准“datetime 列值必须在未来 X 年内”需要缓存当前年份和接下来的 X 年的时区周期
2. 缓存表至少应包含以下列
   • 时区名称 (timezone_id)
   • UTC 周期开始日期 (period_start_date_utc)
   • UTC 周期结束日期 (period_end_date_utc)
   • 指定周期的 UTC 偏移量 (offset_in_minutes)。例如，在夏令时期间，“Pacific Standard Time”时区的偏移量为 -420 分钟
   • 指定周期与前一个周期的 UTC 偏移量差 (delta_previous_offset_in_minutes)。例如，-60/0/+60 分钟
   • 下一个周期与指定周期的 UTC 偏移量差 (delta_next_offset_in_minutes)。例如，+60/0/-60 分钟
   • ...

   可以存储两个或更多个前/后周期在 JSON 数据类型 列中。

3. 此外，将 CHECKSUM(timezone_id) 存储在单独的 [timezone_id_checksum] [int] NOT NULL 列中。
   在该 timezone_id_checksum 列上创建 CLUSTERED INDEX。
   这在按时区筛选记录时提供了最佳性能 -
   [timezone_id_checksum] = CHECKSUM(store_tz) 比 [timezone_id] = [store_tz] 快，因为 integer 数据类型空间要求低（Integer 对比 Varchar(50)）

4. 在 WHERE 语句中，组合相邻时间段的标准。例如，标准“processing_date 必须在 Today 或 Tomorrow 范围内”。

   WHERE
        (Today 12:00AM <= processing_date) AND 
        (processing_date <= Today 23:59:59PM)
        OR
        (Tomorrow 12:00AM <= processing_date) AND 
        (processing_date <= Tomorrow 23:59:59PM)

   可以改写为

   WHERE (Today 12:00AM <= processing_date) AND 
         (processing_date <= Tomorrow 23:59:59PM)

5. 如果 _timezone_period_cache 表有数百条记录，最好将时区信息子查询放在表 join 之前。

   SELECT O.*  
   FROM 
       (  --sub query
           ...
       ) STORE_TZ_INFO
   INNER JOIN [Orders] O (nolock) ON O.store_fk = STORE_TZ_INFO.store_pk

测试表

我的测试数据库中有以下表

• _timezone_period_cache 包含一个“Pacific Standard Time”时区的记录
• 包含 200 万条记录的 Orders 表
• 包含一个“Pacific Standard Time”时区记录的 Stores 表
• 其他表：Clients、Products 和 OrderItems，但它们未用于我的 SQL Server 视图

_timezone_period_cache 表

          CREATE TABLE [dbo].[_timezone_period_cache](
              [timezone_id] [varchar](50) NOT NULL,
              [offset_in_minutes] [int] NOT NULL,
              [timezone_id_checksum] [int] NOT NULL,
              [period_start_date_utc] [datetime2](7) NOT NULL,
              [period_end_date_utc] [datetime2](7) NOT NULL,
              [delta_previous_offset_in_minutes] [int] NOT NULL,
              [delta_next_offset_in_minutes] [int] NOT NULL
          ) ON [PRIMARY]
          GO
          --  create CLUSTERED INDEX
          CREATE CLUSTERED INDEX idx_timezone_id_checksum
          ON [_timezone_period_cache] (timezone_id_checksum);

填充 _timezone_period_cache 表

在评估时区周期时，重要的是不要取时钟回拨（通常是夏令时结束）时的精确 datetimeoffset，而是取一微秒前的值，然后将该微秒加到 UTC 的 datetimeoffset 中。在下面的截图中，SQL Server 返回了两个 datetimeoffset 及其差值 - 它是 1 小时 1 微秒，而不仅仅是 1 微秒。

在我的测试中，我选择了Pacific Standard Time 时区。由于我想在夏令时结束和开始的日期运行测试，我在脚本中填充了 2022/2023 年的记录。

          --2022
      
          --2022-03-13 / 2022-11-06 DST UTC-7
          INSERT INTO [dbo].[_timezone_period_cache]
                     ([timezone_id]
                     ,[offset_in_minutes]
                     ,[timezone_id_checksum]
                     ,[period_start_date_utc]
                     ,[period_end_date_utc]
                     ,[delta_previous_offset_in_minutes]
                     ,[delta_next_offset_in_minutes]
                     )
               VALUES
                     ('Pacific Standard Time'
                     , -7*60
                     , CHECKSUM(CONVERT(varchar(50), 'Pacific Standard Time'))
                     , 
                          DATEADD(
                              HOUR, 
                              3, 
                              CONVERT(datetime2, DATEFROMPARTS(2022, 03, 13))
                          ) AT TIME ZONE 'Pacific Standard Time'  AT TIME ZONE 'UTC'
                     ,
                      DATEADD(
                          MICROSECOND,
                          1,
                          DATEADD(
                              MICROSECOND,
                              -1,
                              DATEADD(HOUR, 
                                  2, 
                                  CONVERT(datetime2, DATEFROMPARTS(2022, 11, 6))
                              )
                          ) AT TIME ZONE 'Pacific Standard Time'  AT TIME ZONE 'UTC'
                      )
                      , 60
                      , -60
                  )
          GO
          --2022-11-06 / 2023-03-12 No DST UTC-8
          INSERT INTO [dbo].[_timezone_period_cache]
                     ([timezone_id]
                     ,[offset_in_minutes]
                     ,[timezone_id_checksum]
                     ,[period_start_date_utc]
                     ,[period_end_date_utc]
                     ,[delta_previous_offset_in_minutes]
                     ,[delta_next_offset_in_minutes]
                     )
               VALUES
                     ('Pacific Standard Time'
                     , -8*60
                     , CHECKSUM(CONVERT(varchar(50), 'Pacific Standard Time'))
                     , DATEADD(
                          MICROSECOND,
                          1,
                          DATEADD(
                              MICROSECOND,
                              -1,
                              DATEADD(HOUR, 
                                  2, 
                                  CONVERT(datetime2, DATEFROMPARTS(2022, 11, 6))
                              )
                          ) AT TIME ZONE 'Pacific Standard Time'   AT TIME ZONE 'UTC'
                      )
                      , DATEADD(
                              HOUR, 
                              3, 
                              CONVERT(datetime2, DATEFROMPARTS(2023, 03, 12))
                          ) AT TIME ZONE 'Pacific Standard Time'   AT TIME ZONE 'UTC'
                      , -60
                      , +60
                  )
          GO
      
          --2023 year
          --2023-03-12 / 2023-11-05 DST UTC-7
          INSERT INTO [dbo].[_timezone_period_cache]
                     ([timezone_id]
                     ,[offset_in_minutes]
                     ,[timezone_id_checksum]
                     ,[period_start_date_utc]
                     ,[period_end_date_utc]
                     ,[delta_previous_offset_in_minutes]
                     ,[delta_next_offset_in_minutes]
                     )
               VALUES
                     ('Pacific Standard Time'
                     , -7*60
                     , CHECKSUM(CONVERT(varchar(50), 'Pacific Standard Time'))
                     , 
                          DATEADD(
                              HOUR, 
                              3, 
                              CONVERT(datetime2, DATEFROMPARTS(2023, 03, 12))
                          ) AT TIME ZONE 'Pacific Standard Time'  AT TIME ZONE 'UTC'
                     ,
                      DATEADD(
                          MICROSECOND,
                          1,
                          DATEADD(
                              MICROSECOND,
                              -1,
                              DATEADD(HOUR, 
                                  2, 
                                  CONVERT(datetime2, DATEFROMPARTS(2023, 11, 5))
                              )
                          ) AT TIME ZONE 'Pacific Standard Time'  AT TIME ZONE 'UTC'
                      )
                      , +60
                      , -60
                  )
          GO
          --2023-11-05 / 2024-03-10 No DST UTC-8
          INSERT INTO [dbo].[_timezone_period_cache]
                     ([timezone_id]
                     ,[offset_in_minutes]
                     ,[timezone_id_checksum]
                     ,[period_start_date_utc]
                     ,[period_end_date_utc]
                     ,[delta_previous_offset_in_minutes]
                     ,[delta_next_offset_in_minutes]
                     )
               VALUES
                     ('Pacific Standard Time'
                     , -8*60
                     , CHECKSUM(CONVERT(varchar(50), 'Pacific Standard Time'))
                     , DATEADD(
                          MICROSECOND,
                          1,
                          DATEADD(
                              MICROSECOND,
                              -1,
                              DATEADD(HOUR, 
                                  2, 
                                  CONVERT(datetime2, DATEFROMPARTS(2023, 11, 5))
                              )
                          ) AT TIME ZONE 'Pacific Standard Time'   AT TIME ZONE 'UTC'
                      )
                      , DATEADD(
                              HOUR, 
                              3, 
                              CONVERT(datetime2, DATEFROMPARTS(2024, 03, 10))
                          ) AT TIME ZONE 'Pacific Standard Time'   AT TIME ZONE 'UTC'
                      , -60
                      , +60		    
                  )
          GO

Orders 表

          CREATE TABLE [dbo].[Orders](
              [order_pk] [int] IDENTITY(1,1) NOT NULL,
              [client_fk] [int] NOT NULL,
              [store_fk] [int] NOT NULL,
              [created_date_store_local] [datetime2](7) NOT NULL,
              [shipping_date_store_local] [datetime2](7) NULL,
              [delivered_date_client_local] [datetime2](7) NULL,
           CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
          (
              [order_pk] ASC
          ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                  ALLOW_PAGE_LOCKS = ON, _
                  OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
          ) ON [PRIMARY]

Stores 表

CREATE TABLE [dbo].[Stores](
             [store_pk] [int] IDENTITY(1,1) NOT NULL,
             [store_tz] [varchar](50) NOT NULL,
             [store_name] [nvarchar](100) NOT NULL,
         CONSTRAINT [PK_Stores] PRIMARY KEY CLUSTERED 
        (
            [store_pk] ASC
        ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
                OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
        ) ON [PRIMARY]

原始视图

对于位于旧金山（“Pacific Standard Time”时区）的商店，v_Original 视图按 DateTime 标准筛选订单 - 列值应在当前时间之前的 24 小时到当前时间之后的 24 小时之间（为了获得 GETUTCDATE() 的不同值，我更改了本地 SQL SERVER 系统时间）。

CREATE VIEW v_Original AS
          SELECT O.*
          FROM [Orders] O  
          INNER JOIN Stores S ON S.store_pk = O.store_fk 
          WHERE 
                  O.store_fk = 1
              AND
                  [created_date_store_local] 
                   AT TIME ZONE S.store_tz < DATEADD(day, +1, GETUTCDATE())
              AND 
                  [created_date_store_local] 
                   AT TIME ZONE S.store_tz > DATEADD(day, -1, GETUTCDATE())

获取总记录数查询

SELECT COUNT(*) FROM v_Original

带分页的 CTE

      DECLARE @PageNumber int = 10
      DECLARE @PageSize int = 100
      
      ;WITH v_Original AS
      (
          SELECT *,
              ROW_NUMBER() OVER (ORDER BY order_pk) AS RowNumber
          FROM v_Original
      )
      
      SELECT * FROM v_Original
      WHERE RowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1 
                      AND (@PageNumber * @PageSize);

优化视图

          CREATE VIEW v_Optimized AS
          SELECT O.*  
          FROM 
               (
                  SELECT tz_name
                      , utc_plus_tzOffset			
-- Evaluate lower time bound for [now - 24h < column] criterion
                      , DATEADD(minute
                          , IIF(
                              utc_plus_tzOffset_minus_24h < 
                              period_start_date_utc --if Date from past 
                                                    --is in preceding period
                              , -1 * delta_previous_offset_in_minutes
                              , 0
                            )
                          , utc_plus_tzOffset_minus_24h
                        ) AS utc_plus_tzOffset_minus_24h_adjusted
-- Evaluate upper time bound for [column < now + 24h] criterion
                      , DATEADD(minute
                          , IIF(
                              utc_plus_tzOffset_plus_24h > 
                              period_end_date_utc --if Date from future 
                                                  --is in next period
                              , delta_next_offset_in_minutes
                              , 0
                          )
                          , utc_plus_tzOffset_plus_24h
                          ) AS utc_plus_tzOffset_plus_24h_adjusted
                      , store_pk
                  FROM 
                  (
                      SELECT
                          tz_name
                          , utc_plus_tzOffset				
                          , store_pk
                          , DATEADD(day, -1, utc_plus_tzOffset) 
                                    AS utc_plus_tzOffset_minus_24h
                          , DATEADD(day, +1, utc_plus_tzOffset) 
                                    AS utc_plus_tzOffset_plus_24h
                          , period_start_date_utc, delta_previous_offset_in_minutes 
                          , period_end_date_utc, delta_next_offset_in_minutes
                      FROM 
                      (
                          SELECT timezone_id AS tz_name
            --   column AT TIME ZONE 'any timezone' < UtcNow ⇒
            --   column' < UtcNow ⇒
--  Since  column - column' = TimeZoneOffset 
            --   column' + (column - column') < UtcNow + TimeZoneOffset ⇒
            --   column < UtcNow + TimeZoneOffset
                              , DATEADD(MINUTE,  [offset_in_minutes], 
                                GETUTCDATE()) AS utc_plus_tzOffset		
                              , store_pk
                              , period_start_date_utc, delta_previous_offset_in_minutes
                              , period_end_date_utc, delta_next_offset_in_minutes
                          FROM 
                          (
                              SELECT timezone_id, [offset_in_minutes], store_pk
                                  , TZP.period_start_date_utc, _
                                    TZP.delta_previous_offset_in_minutes
                                  , TZP.period_end_date_utc, _
                                    TZP.delta_next_offset_in_minutes
                              FROM [_timezone_period_cache] TZP (nolock)
                              INNER JOIN Stores (nolock) ON (1=1)
                              WHERE                                 
--  If 'store_tz' time zone column type does not match  'timezone_id' 
--  column type - use CONVERT function
     -> CHECKSUM(CONVERT(varchar(50), store_tz_unicode))
                                  TZP.[timezone_id_checksum] = CHECKSUM(store_tz) 
                                  --  Find current TimeZonePeriod 
                                  --  for 'store_tz' time zone  
                                  AND [period_start_date_utc] < GETUTCDATE()
                                  AND [period_end_date_utc] > GETUTCDATE()				
                          ) TZI	
                      ) TZI2
                  ) TZI3
                ) STORE_TZ_INFO
            INNER JOIN [Orders] O (nolock) ON O.store_fk = STORE_TZ_INFO.store_pk 
            WHERE
                  store_fk = 1 
              AND
                  [created_date_store_local] >= utc_plus_tzOffset_minus_24h_adjusted  
              AND 
                  [created_date_store_local] <= utc_plus_tzOffset_plus_24h_adjusted

获取总记录数查询

SELECT COUNT(*) FROM v_Optimized

带分页的 CTE

      DECLARE @PageNumber int = 10
      DECLARE @PageSize int = 100
      
      ;WITH cte_optimized AS
      (
          SELECT *,
              ROW_NUMBER() OVER (ORDER BY order_pk) AS RowNumber
          FROM v_Optimized
      )
      
      SELECT * FROM cte_optimized
      WHERE RowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1 
                      AND (@PageNumber * @PageSize);

测试结果

对于测试，我使用了一台桌面 PC，配备 4 核 CPU、16 GB RAM 和 HDD。我将系统时间分别更改为 2022 年 11 月 5 日和 6 日（“Pacific Standard Time”时区夏令时更改的前后两天）。如预期的那样，所有查询都返回了相同的 resultsets - 这证实了该解决方法是跨日期范围完全有效可行的解决方案。

首先，我运行 Orders 表只有主键（PK）时的查询。之后，我添加了一个 nonclustered 索引

CREATE NONCLUSTERED INDEX [idx_Orders_created_date]
              ON [dbo].[Orders] ([store_fk],[created_date_store_local])

这是结果：

SQL Server 视图/查询时间	count(*)		带分页的 CTE
	仅 PK	PK + 非聚集索引	仅 PK	PK + 非聚集索引
原始（使用 AT TIME ZONE 运算符）	9082 毫秒	8358 毫秒	318 毫秒	324 毫秒
优化（使用 _timezone_period_cache 表）	1514 毫秒	23 毫秒	52 毫秒	53 毫秒

结论

通过使用时区周期缓存表，SQL 查询性能提高了 6 到 360 倍。通过提出的解决方法，我们可以获得与使用 AT TIME ZONE 运算符完全相同的 resultset。这种方法不仅可以在 SQL Server 视图中使用，也可以在动态 SQL 中使用。每一组固定或浮动时间范围的标准都将有其自身的棘手解决方案和复杂的子查询。

历史

• 2023 年 3 月 6 日：初始版本