UTC转加州时区后SQL查询变慢，求高效昨日数据查询方案

问题背景 #

我有一张名为AuditLogs的表，记录了所有用户操作及其时间戳。所有时间戳均以UTC（偏移量为0）记录，而实际执行操作的用户位于加州（时区为UTC-8）。需要基于这些数据生成报表，要求所有数据转换为加州时间以准确呈现用户操作的时间语境。

当前使用的查询语句如下：

select distinct top 1000
    Users.FirstName + ' ' + Users.LastName Name
    ,AuditRecords.username
    ,AuditRecords.subtype
    ,convert(varchar, timestamp at time zone 'Pacific Standard Time', 0)  time
    ,timestamp

from 
    AuditRecords 
    join Users on AuditRecords.UserId = Users.Id
where 
    AuditRecords.subtype <> 'log%'
    and DATEPART(dy,timestamp at time zone 'Pacific Standard Time') = datepart(dy,SYSDATETIMEOFFSET() at time zone 'Pacific Standard Time')-1

其中关键筛选条件为：

and DATEPART(dy,timestamp at time zone 'Pacific Standard Time') = datepart(dy,SYSDATETIMEOFFSET() at time zone 'Pacific Standard Time')-1

该条件用于获取前一日的所有记录（非滚动24小时范围，例如11月9日执行报表时，无论早晚都返回11月8日的记录）。但问题是，移除条件中的at time zone 'Pacific Standard Time'后查询速度显著提升，保留时区转换时查询速度大幅变慢，需要更高效的实现方式。

优化方案 #

核心思路 #

避免在查询条件中对索引字段（timestamp）进行函数/时区转换操作——这类操作会导致数据库无法使用timestamp字段上的索引，只能执行全表扫描，从而大幅降低查询速度。

正确的做法是：先计算出加州时区「前一日」对应的UTC时间范围，再用这个范围直接过滤timestamp字段，这样就能利用字段上的索引加速查询。

具体实现代码 #

-- 先计算加州时区前一日的UTC时间边界
DECLARE @YesterdayStartUTC DATETIME2, @YesterdayEndUTC DATETIME2;

-- 获取当前加州时间的前一日起始点（本地时间00:00），转换为UTC
SET @YesterdayStartUTC = DATEADD(day, -1, CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time' AS DATE)) 
                         AT TIME ZONE 'Pacific Standard Time' 
                         AT TIME ZONE 'UTC';

-- 获取当前加州时间的前一日结束点（本地时间23:59:59.9999999），转换为UTC
SET @YesterdayEndUTC = DATEADD(millisecond, -1, 
                               CAST(SYSDATETIME() AT TIME ZONE 'Pacific Standard Time' AS DATE) 
                               AT TIME ZONE 'Pacific Standard Time' 
                               AT TIME ZONE 'UTC');

-- 用UTC时间范围查询，避免对timestamp字段做转换
select distinct top 1000
    Users.FirstName + ' ' + Users.LastName Name
    ,AuditRecords.username
    ,AuditRecords.subtype
    ,convert(varchar, timestamp at time zone 'Pacific Standard Time', 0)  time
    ,timestamp

from 
    AuditRecords 
    join Users on AuditRecords.UserId = Users.Id
where 
    AuditRecords.subtype <> 'log%'
    and AuditRecords.timestamp BETWEEN @YesterdayStartUTC AND @YesterdayEndUTC

额外优化建议 #

• 确保AuditRecords.timestamp字段上创建了非聚集索引，如果还没有，执行：

  CREATE INDEX IX_AuditRecords_Timestamp ON AuditRecords(timestamp);
  

• 如果经常按subtype和timestamp组合查询，可以创建复合索引，进一步提升过滤效率：

  CREATE INDEX IX_AuditRecords_Subtype_Timestamp ON AuditRecords(subtype, timestamp);
  

• 检查DISTINCT是否必要：如果AuditRecords和Users的关联不会产生重复行，可以去掉DISTINCT，减少查询开销。

内容的提问来源于stack exchange，提问作者user7959439