Please help with these merge joins

NYdmx123 2017-11-15 08:49:43

This is taking over 20 to run please help

NYdmx123 2017-11-15 08:51:17
Any help would greatly be appreciated — trying to cut out some of these merge joins
SQL – 2016 — stats up to date and indexes rebuilt prior to running
SDyckes2 2017-11-16 23:27:57
There are a number of opportunities to increase performance due to extra work being done by SQL Server to return the data. This is evident in the execution plan on the Index Seeks or Scans with the exclamation point in yellow:

alt text

The Clustered Index Scan on *[Big_Table_Staging].[PK_Big_Table_Staging]* has this warning and has the highest estimated cost (check the Top Operations tab). The scan reads 18+ million records to return 1.1 million records. Finding the correct index will reduce the IO cost for these nodes. Rob Farley has a blog post about these warning that is helpful.

There are 2 Key Lookups on the REprocess_BTS_Items table that could be removed, though it does not look like it will have much effect on the overall performance of the query.

The Product related tables are returning more than 13 million records and are the largest costs of the query. These tables are referenced via the view, [v_ProductSimple], which appears to be returning every product in the system. Could a filer be applied here to reduce the amount of data that is being processed by the query?

With as many Joins and subqueries being used, you may consider breaking this down into smaller chunks and using temp tables to hold data. This may allow you to access a smaller subset of the data and process much quicker.

NYdmx123 2017-11-17 01:06:04
Thank you so much for taking the time to help

I seen the key lookups but like you said no real meat on that bone.

I know about the big table staging scan on 18 million records to get 1 million. I have a perfect index with a 100 score in plan explorer but due to the hash join being used its scanning the primary key.
Also there is a where clause on a date field for this table but those rows are not being filtered out till almost the end of the execution plan. Which is causing these million rows to be carried through the entire plan and than right at the end it filters down to a couple hundred.
It would help if they were filtered out at the start so they aren't being joined to all these other tables for no reason. I will look at those index warnings I did not see them before. I did see a warning on one of the first hash joins with the product tables which is causing 1.2 gb of spill over

SDyckes2 2017-11-17 02:31:47
Yes, I saw the spill as well. If you can reduce the 18M, then the spill may be eliminated.
NYdmx123 2017-11-17 03:01:32
I just dont get why the criteria on the date isnt being applied until almost the end of plan – is there anyway i can have that where clause exclude from big tabling staging right away
NYdmx123 2017-11-17 03:02:10
once again thank you so much – i have spent so many hours on this im at my wits end and its really affecting our business
SDyckes2 2017-11-17 17:49:33
I completely understand how you have spent many hours on this.

As complex as the where clause and the whole query is, I would spend my time on breaking the query down into the smaller parts, better understanding what each sub-query and join are doing.

If the smaller parts can be tuned, great! But I believe I would be spending time re-writing the query. Look at the most restrictive value in the where clause and building from there. I would be making use of temp tables to hold the data as I gather it, this would also clean up the query to make it easier to read and understand.

It is not a small project, but since it is affecting the business, it is worth the time and effort.