Need to Optimize the query – using DATE functions in the joins
FROM [BASE].[FACT].[USDM_ASST] FASST WITH (NOLOCK) LEFT OUTER JOIN [BASE].[FACT].[SFDC_ASST] RMAST WITH (NOLOCK) ON RMAST.[SRL_NBR] = FASST.[ASST_ID] AND (RMAST.[SHIP_DT] >= DATEADD(DAY, -2, FASST.[SHIP_UTC_DT]) OR RMAST.[SHIP_DT] <= DATEADD(DAY, 2, FASST.[SHIP_UTC_DT])) FASST has 24 million records and RMAST has 31 million records. When I checked the execution plan of this query, I saw a HASH MATCH (LEFT OUTER […]