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 JOIN) which is 77% cost.

This query is taking 34 minutes to return all the data. We need to optimize this query.

Columnstore indexes are on both tables. Using this query in an SSAS Tabular model.

All the necessary filters are applied in the WHERE clause.