How can I reduce the cost of hash match?

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

 SELECT 
         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]
 
     FROM   
         [V_mp_Auto_Prices_Final_IncludingNew] bcap   
 Where    bcap.MerchantID = 'V15W'


tmpA19F.pesession (300.1 kB)
avatar image By Marekx371 1 asked Oct 19 at 08:27 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By SDyckes2 81 answered Oct 21 at 02:02 AM
more ▼
(comments are locked)
avatar image Marekx371 Oct 23 at 02:42 PM

I tried that but it still does a hash match join, Not sure why or what to do now.

avatar image Marekx371 Oct 23 at 02:50 PM

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'

avatar image Marekx371 Oct 23 at 02:59 PM

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

avatar image SDyckes2 Oct 23 at 03:09 PM

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.

avatar image Marekx371 Oct 23 at 03:31 PM

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 #] ,''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]

 FROM   
     [V_mp_Auto_Prices_Final_IncludingNew] bcap   

Where bcap.MerchantID = @MechantID '

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

avatar image SDyckes2 Oct 23 at 03:37 PM

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 Support@SentryOne.com so it can be investigated.

avatar image Marekx371 Oct 23 at 04:08 PM

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.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x117
x17
x12

asked: Oct 19 at 08:27 PM

Seen: 32 times

Last Updated: Oct 23 at 04:08 PM