嵌套循环连接无连接谓词：SQL查询性能缓慢求助

1. 无有效连接谓词的本质：你的查询中LEFT JOIN [sys].[time_zone_info]的连接条件仅依赖输入变量@timezone，未与主查询的OrderReport或DimAccountsPartner表的任何字段关联。优化器会将这种连接判定为无连接谓词，因为每一行主表数据都会和sys.time_zone_info中匹配变量的行进行关联，本质上是一种冗余的表连接，而非基于业务逻辑的关联。
2. 行目标（Row Goal）放大性能问题：查询末尾的TOP(10)触发了优化器的行目标机制，使其优先选择嵌套循环连接。但这种连接方式会在主表的每一行数据上重复查询sys.time_zone_info，当主表数据量较大时，性能损耗会被急剧放大。你使用DISABLE_OPTIMIZER_ROWGOAL有效，也验证了行目标是性能问题的诱因之一，但并非根本原因。

既然sys.time_zone_info中每个时区名称对应唯一行，完全可以提前查询出目标时区的偏移量并存入变量，主查询直接使用该变量即可，彻底消除冗余连接和无连接谓词警告。

修改后的代码如下：

declare @StartDate date = '2022-12-08'
, @EndDate date = '2022-12-08'
, @OnlyActivated int = 0
, @partner int = 32558
, @timezone varchar(50) = 'Pacific Standard Time'
, @timezoneOffset int; -- 新增变量存储时区偏移量

-- 提前查询时区偏移量
SELECT @timezoneOffset = CAST(LEFT(current_utc_offset, 3) AS INT)
FROM [sys].[time_zone_info]
WHERE [name] = ISNULL(@timezone, 'Pacific Standard Time');

-- 处理时区不存在的情况（默认偏移量）
SET @timezoneOffset = ISNULL(@timezoneOffset, -8); -- 太平洋标准时区默认偏移为-8

;WITH Result
AS
(SELECT
        [Program] = a.ProgramName
       ,[Account #] = a.accountID
       ,[Account Name] = a.CustomerName
       ,[Purchase ID] = r.PURCHASEID
       ,[Purchase Date] = CAST((r.PURCHASE_DATE AT TIME ZONE ISNULL(@timezone, 'Pacific Standard Time')) AS DATETIMEOFFSET)
       ,[Activation Date] = CAST((r.ACTIVATION_DATE AT TIME ZONE ISNULL(@timezone, 'Pacific Standard Time')) AS DATETIMEOFFSET)
       ,[Cancellation Date] = CAST((r.CANCELLATION_DATE AT TIME ZONE ISNULL(@timezone, 'Pacific Standard Time')) AS DATETIMEOFFSET)
       ,[Product Description] = r.DESCR
       ,[Product Code] = r.PartNumber
       ,[Product Type] = r.ChargeType
       ,[Currency] = r.CURRENCY
       ,[Customer Name] = a.CustomerName
       ,[Email of Login User] = r.EmailOfLoginUser
       ,[Creation Date] = a.dateOfPurchase
       ,[Account Plan] = a.PlanName
    FROM [Partner].[OrderReport] r 
    INNER JOIN [Partner].[DimAccountsPartner] a 
        ON r.AccountKey = a.AccountKey 
    -- 移除冗余的LEFT JOIN sys.time_zone_info
    WHERE ((a.RootPLR_ID IN (34747, 37323)
    AND SKU NOT LIKE 'VSHP%'
    AND SKU NOT LIKE 'VUSG%'
    AND SKU NOT LIKE 'VOPT%'
    AND SKU NOT LIKE 'VUSR00000%'
    AND SKU NOT LIKE 'VUSR000101')
    OR a.RootPLR_ID NOT IN (34747, 37323))
    AND ((@OnlyActivated = 0
    AND ((DATEADD(HOUR, @timezoneOffset, r.PURCHASE_DATE) BETWEEN @StartDate AND DATEADD(DAY, 1, @EndDate))
    OR (DATEADD(HOUR, @timezoneOffset, r.Cancellation_Date) BETWEEN @StartDate AND DATEADD(DAY, 1, @EndDate))))
    OR (@OnlyActivated = 1
    AND DATEADD(HOUR, @timezoneOffset, r.Activation_Date) BETWEEN @StartDate AND DATEADD(DAY, 1, @EndDate)))
    AND (a.PLR_ID = @partner
    OR a.Parent_PLR_ID = @partner
    OR a.RootPLR_ID = @partner)
    AND a.[AccountFlags] <> 4
    AND SKU NOT IN ('VUSG000001','VUSG000000')
    )
SELECT TOP(10)
    KeyColumn = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
   ,[Program] = ISNULL([Program], '')
   ,[Account #] = ISNULL([Account #], 0)
   ,[Account Name] = ISNULL([Account Name], '')
   ,[Purchase ID] = ISNULL([Purchase ID], '')
   ,[Purchase Date] = ISNULL(FORMAT([Purchase Date], 'yyyy-MM-dd hh:mm:ss'), '')
   ,[Activation Date] = ISNULL(FORMAT([Activation Date], 'yyyy-MM-dd hh:mm:ss'), '')
   ,[Cancellation Date] = ISNULL(FORMAT([Cancellation Date], 'yyyy-MM-dd hh:mm:ss'), '')
   ,[Product Description] = ISNULL([Product Description], '')
   ,[Product Code] = ISNULL([Product Code], '')
   ,[Product Type] = ISNULL([Product Type], '')
   ,[Currency] = ISNULL([Currency], '')
   ,[Email of Login User] = ISNULL([Email of Login User], '')
   ,[Creation Date] = ISNULL(FORMAT([Creation Date], 'yyyy-MM-dd hh:mm:ss'), '')
   ,[Account Plan] = ISNULL([Account Plan], '')
FROM Result

• 避免在WHERE条件中对列使用函数（如DATEADD(HOUR, @timezoneOffset, r.PURCHASE_DATE)），如果业务允许，可以考虑在OrderReport表中预计算并存储转换后的时区日期字段，创建索引以提升查询性能。
• 检查DimAccountsPartner表的RootPLR_ID、PLR_ID、Parent_PLR_ID、AccountFlags字段是否有合适的索引，减少主查询的过滤和连接开销。

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