Search code examples
sql-serverjointimezonequery-optimization

Nested loops join - no join predicate in query

Trying to solve this puzzle: the query works very slow and as I could understand, the problem is the join to the [sys].[time_zone_info] table - it creates nested loops with the specific warning - "No Join Predicate" I read numerous articles trying to identify the issue but I couldn't.

I tried to use OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL')) hint and it worked great. It's also not a UDF column (http://techblog.elish.net/2010/10/nested-loops-join-no-join-predicate.html).

declare @StartDate date = '2022-12-08'
, @EndDate date = '2022-12-08'
, @OnlyActivated int = 0
, @partner int = 32558
, @timezone varchar(50) = 'Pacific Standard Time'
;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(tzi.[name], 'Pacific Standard Time')) AS DATETIMEOFFSET)
       ,[Activation Date] = CAST((r.ACTIVATION_DATE AT TIME ZONE ISNULL(tzi.[name], 'Pacific Standard Time')) AS DATETIMEOFFSET)
       ,[Cancellation Date] = CAST((r.CANCELLATION_DATE AT TIME ZONE ISNULL(tzi.[name], '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] tzi 
        ON tzi.[name] = ISNULL(@timezone, 'Pacific Standard Time') 
    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, CAST(LEFT(tzi.current_utc_offset, 3) AS INT), r.PURCHASE_DATE) BETWEEN @StartDate AND DATEADD(DAY, 1, @EndDate))
    OR (DATEADD(HOUR, CAST(LEFT(tzi.current_utc_offset, 3) AS INT), r.Cancellation_Date) BETWEEN @StartDate AND DATEADD(DAY, 1, @EndDate))))
    OR (@OnlyActivated = 1
    AND DATEADD(HOUR, CAST(LEFT(tzi.current_utc_offset, 3) AS INT), 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

Forgot to add Exec plan: Execution Plan

Solution

  • I would get rid of all those ISNULL's by pre-checking the passed in @timezone parameter and removing the join to the system table.

    Declare @timezone_input sysname = 'Pacific Standard Time';
Declare @timezone sysname = coalesce((Select name From sys.time_zone_info Where name = @timezone_input), 'Pacific Standard Time');

    If the passed in value is not a valid time zone in the table it will be defaulted to 'Pacific Standard Time'.

    With that you can remove the join - and the ISNULL around the conversion to the identified time zone.

       ,[Purchase Date] = CAST(r.PURCHASE_DATE AT TIME ZONE @timezone AS DATETIMEOFFSET)
   ,[Activation Date] = CAST(r.ACTIVATION_DATE AT TIME ZONE @timezone) AS DATETIMEOFFSET)
   ,[Cancellation Date] = CAST(r.CANCELLATION_DATE AT TIME ZONE @timezone) AS DATETIMEOFFSET)

    I would then convert the @StartDate and @EndDate parameters from the input values - to the same time zone that the dates have been stored. For example, if the passed in value is 'Eastern Standard Time' and the stored value is UTC:

    SET @StartDate = @StartDate AT TIME ZONE @timezone AT TIME ZONE 'UTC';
SET @Enddate = @EndDate AT TIME ZONE @timezone AT TIME ZONE 'UTC';

    And that removes the requirement to use DATEADD in the where clause.

    And finally, remove FORMAT and use CONVERT instead. Format can be up to 40x slower than the equivalent CONVERT function.