CODEWITHSUNDEEP
sqlsql-serverquery-optimization
Anthony Liu
Anthony Liu

Reputation: 373

SQL Server Query Hints for EXISTS Clause

I'm trying to force SQL server to use a hash Match for exists clause as below.

Execution Plan

FROM 
    [Dbo].[360 Nrc Data] AS [nrc]
WHERE 
    [CALENDAR_DATE] BETWEEN @START_DATE AND @End_Date
    AND EXISTS (SELECT 1
                FROM [staging].[FACT_DTV_RETENTION_EVENT_STEP_1] AS [fact]
                WHERE [fact].[CD_PORTFOLIO_ID] = [nrc].[Cd_Portfolio_Id]
                  AND [fact].[Event_Date] = [nrc].[CALENDAR_DATE])
GROUP BY 
    [Calendar_Date], [Cd_Portfolio_Id])

The normal way to force query hint is as below but Left Semi Join is more efficient than has join

[_fact_date_and_portfolio] AS [fact]    
     inner hash join    

     [dbo].[360 TA Offer] AS [offr]

Upvotes: 3

Views: 1720

Answers (1)

Martin Smith
Martin Smith

Reputation: 453990

There is no hint syntax to specify the physical join type for a specific semi join or anti semi join operator.

But as this plan only has one join you can just add OPTION (HASH JOIN) to the end of the query. The hint applies to all joins but there aren't any others.

Upvotes: 4

Related Questions