Need help tuning this large complex query. Fresh out of ideas!

dwj119 2015-01-27 18:54:59

link textI'm having trouble tuning this large complex query, it is easy to identify the problem area in plan explorer, but can't come up with any tuning ideas at this point. Perhaps I have been staring at it to long! Any help would be greatly appreciated!
link text

dwj119 2015-02-05 18:30:49
Maybe you guys could help me with a smaller piece of the pie. I'm looking for ways to tune this specific piece of the query attached to the question.
dwj119 2015-02-05 18:34:09
I thought maybe a CTE could be used to filter the right table expression but I can't seem to get the results I want
HarryAir 2015-02-26 08:58:15
Probably does not affect total situation but view has UNION not UNION ALL. If you need all the data from every view then you should use UNION ALL. Sometimes I have noticed that UNION is taking more time because it has to do Distinct over whole dataset.
dwj119 2015-01-28 14:36:54
SQLkiwi, thanks for taking a look. The query is generated by EF, but the views were written by a SQL developer. We have total freedom to rewrite or take another approach, such as a stored proc(s). The main view is a union all of three other views. The query is an attempt to find all changes for a particular document. The main view is [vw_his_inventory_master_icn_and_stock_changes] whic is:

SELECT * FROM his.vw_his_inventory_master_changes
UNION ALL
SELECT * FROM his.vw_his_inventory_control_number_changes
UNION ALL
SELECT * FROM his.vw_his_stock_number_changes

I've attached the view defintions of the nested views.link text