Nested loops join - no join predicate in query-CodePudding

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 (

CodePudding user response：

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.