question

I am having performance issue on one of my view, issue is described as below; Original View CREATE VIEW TestView AS SELECT Name, Account Number, ProcessDate, Amount FROM TABLE1 The above was working fine, Modified View CREATE VIEW TestView AS SELECT Name, Account Number, ProcessDate, Amount FROM TABLE1 UNION ALL SELECT Name, Account Number, ProcessDate, Amount FROM TABLE2 After the modification the view is performing very poorly unexpected behavior. Issue: When I try to select the information using the views, Which is taking long time to produce the results(30+mins) using the UNION ALL on the view, without Union All and the second table the results are coming quickly. Below are approaches taken to improve the view: Verified the Indexes, Both tables are having Clustered Index on ProcessDate and Non Clustered Index on Account_Number and ProcessDate. To use the proper indexes during the data retrieval, I have created the separate views for both table and combined both and derived the new view. Then used the combined view, there is no improvements. Checked the execution plan of the existing view(TestView), Clustered Index Scan is appearing on the TABLE1 with cost of 99% and ProcessDate clustered index is used scan all the rows. Applied the Update Statistics to the Table1. Instructed the view to use the indexes forcibly using index hints. Now I am struck to improve the performance of the view and defrag percentage of the table is morethan 30% on TABLE1 and it has 80+ million records Please let me know your suggestions to improve the view.