How can I get a hash match without a hint?

SQLHammer 2016-12-07 14:43:00

I have a query which takes more than 30 seconds to run. The current version involves clustered index scans and merge joins (actual plan below). When I was trying to find a way to avoid one or both of the large clustered index scans I found that using a hash match operator produced significant performance gains. I have the execution plan using a query hint below.

I'd like to either make the plan either stick to a hash match and/or use the date parameters more effectively with seeks. There are few indexes on the table right now but I could create and index that may be necessary to support this query.

Any advice?

mergejoinquery.pesession

hashmatchquery.pesession

SQLHammer 2016-12-07 15:55:12
Based on Jason Kyle's advice I decided to pre-filter the Orders table with a temp table at the top and replace the Orders table reference in the larger query with the temp table.

The table:

SELECT *
INTO #orders
FROM dbo.Orders o
WHERE ( [o].[dtOrdered] IS NOT NULL )
    AND ( [o].[dtOrdered] >= @p7 )
    AND ( [o].[dtOrdered] <= @p8 )
SQLkiwi 2016-12-17 13:35:16
Storing the qualifying rows from Orders in a temporary table is certainly a good solution, given the dramatic reduction in rows (32 vs 865,787) at that point.

Some additional observations:

The join hint INNER HASH JOIN has the side effect of forcing the order of table accesses in the execution plan to match the written form of the query. This is the same as if OPTION (FORCE ORDER) had been added. It considerably reduces the tools available to the optimizer, and is normally to be avoided.

The residual predicate on the Orders table scan is:

[t0].[MerchantID]=[@p6] 
AND [t0].[dtOrdered]>=[@p7] 
AND [t0].[dtOrdered]<=[@p8] 
AND [t0].[dtDeleted] IS NULL 
AND [t0].[dtPickup] IS NOT NULL 
AND [t0].[dtOrdered] IS NOT NULL 
AND [t0].[LocationID]=[t0].[LocationID] 
AND 
(
    CONVERT_IMPLICIT(nvarchar(50),[t0].[OrderOrigin],0)=[@p4] 
    OR CONVERT_IMPLICIT(nvarchar(50),[t0].[OrderOrigin],0)=[@p3] 
    OR CONVERT_IMPLICIT(nvarchar(50),[t0].[OrderOrigin],0)=[@p2] 
    OR CONVERT_IMPLICIT(nvarchar(50),[t0].[OrderOrigin],0)=[@p1] 
    OR CONVERT_IMPLICIT(nvarchar(50),[t0].[OrderOrigin],0)=[@p0]
)
  1. The predicate dtOrdered IS NOT NULL is redundant. Removing it could benefit cardinality estimation.
  2. The predicate [t0].[LocationID]=[t0].[LocationID] is logically equivalent to [LocationID] IS NOT NULL.
  3. The implicit conversions on OrderOrigin will prevent an index being used, and may impact cardinality estimation. Change the data types of the parameters @p0 – @p4 to match the OrderOrigin column, presumably they should be varchar(50) (not nvarchar(1000)).