Need help tuning this large complex query. Fresh out of ideas!
2015-01-27 18:54:59
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.
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
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.
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