How can I reduce the cost of hash match?

Marekx371 2017-10-19 20:27:54

Basically I view that is made from a view that are mode from other views .
Trying to optimize it but the hash match are very expensive, I was able to replace scans with seek but after I did that hash match got very expensive.
Replacing scans with seek did help reduce time from 5 minutes to 2 minutes and 30 seconds.
But I'm not sure how to reduce cost of hash match .
here is a select statement

        Itemid as [SELLER PART #]
        ,'' as [NE ITEM #]
        ,'USD' as CURRENCY
        ,'' as MSRP
        , '' as MAP
        , '' as [CHECKOUT MAP]
        ,CONVERT(decimal(10,2),ROUND(bcap.AutoPrice,2)) AS [SELLING PRICE]
        ,OurQTY as Inventory
        ,'' as [Fulfillment Option]
        ,'' as Shipping
        ,'TRUE' as [ACTIVATION MARK]
        [V_mp_Auto_Prices_Final_IncludingNew] bcap   
Where   bcap.MerchantID = 'V15W'
SDyckes2 2017-10-21 02:02:44
The Hash Match that is the Top Operator may be able to be tuned by identifying a better JOIN operator for the tables or by indexing the proper columns and sort order for the JOIN.

Consider an index on the ItemID from the Products table or reworking an existing index to sort the ItemID and try to get the same sort order on the KitItemSKU from the AdminKits table.

Marekx371 2017-10-23 14:42:26
I tried that but it still does a hash match join, Not sure why or what to do now.
Marekx371 2017-10-23 14:50:55
when I try to get actual plan I get This message

'A query plan was not collected at the time of statement execution Estimated Plan Generation is only available for non-dynamic root statements on SQL server 2005 and higher'

Marekx371 2017-10-23 14:59:27
turns out is a sub query join in one of the views so I'm going to create another view and join to that view instead of sub query
SDyckes2 2017-10-23 15:09:13
I do not see the values for the parameters @1 or @2, it looks like the statement in the plan is part of a larger stored procedure or block of code. Is this query part of a Dynamic SQL Statement?

You can copy the query from the Text Data tab, past it in the Command Text tab, replace the @1 and @2 variables with the values, then you can get an Actual Plan for this specific query.

Since you have so many joins in the views and sub-views, you could consider re-writing the query, only pulling from the tables you need the data or, as you said, create a view for you to use for this query allowing you to use better Join columns and possibly reduce the amount of data to parse for your return.

Marekx371 2017-10-23 15:31:46
This is the statement in the proc, I tried running like that in command and the whole sentry crashes, I can only get Estimated plan.

and using a view instead of sub query didnt help

Declare @SqlRun nvarchar(max)

Declare @MechantID varchar(100)
Select @MechantID = MerchantID from mpAccounts where SiteID = @Siteid

Set @sqlRun = 'SELECT
Itemid as [SELLER PART #]
,"" as [NE ITEM #]
,"" as MSRP
, "" as MAP
, "" as [CHECKOUT MAP]
,CONVERT(decimal(10,2),ROUND(bcap.AutoPrice,2)) AS [SELLING PRICE]
,OurQTY as Inventory
,"" as [Fulfillment Option]
,"" as Shipping

    [V_mp_Auto_Prices_Final_IncludingNew] bcap

Where bcap.MerchantID = @MechantID '

Print @sqlrun
Exec sys.sp_executesql @sqlRun, N'@MechantID VARCHAR(100)', @MechantID=@MechantID

SDyckes2 2017-10-23 15:37:50
I did not expect the View to work better than the Sub-Query.

I would run the statement without using Dynamic SQL, see if you can get an actual plan. If Plan Explorer continues to crash, submit a ticket to so it can be investigated.

Marekx371 2017-10-23 16:08:52
I tried running without dynamic sql but I still get the same message and I think the reason behind that is one of the views uses a cross apply to UDF and I cant exclude it.