I have a fairly complex query that joins several tables and views. It executes pretty quickly (~9 seconds for 2200 rows), except when I add a column from a view that is already joined in the query the query seems like it will never finish - I left it running for 33 minutes once and it had not completed.
SELECT table1.col1, table2.col2, view1.col3, view2.col4 FROM table1 INNER JOIN table2 ON table1.id = table2.id LEFT OUTER JOIN view1 ON view1.id = table1.id LEFT OUTER JOIN view2 ON view2.id = view1.id LEFT OUTER JOIN view3 ON CONVERT(CHAR(8),view1.DateRequired,112) + LEFT(view2.ItemNumber, 4) = view3.UID
The above will run fine, but when I add any column from view3 into the
SELECT list the query slows down to a crawl. I'm unable to get the full result set but if I limit the result to 50 rows it takes around 1 minute to run to completion compared to roughly 1 second with the original query.
Now, I know that the join for view3 is pretty complicated; it converts one value from a date to a string and then concatenates this to a value from another table and uses this resulting concatenated blob of info to join against the same style code in view3. However, I would expect that if this were a problem with the join itself then it would slow down the query when the view itself is joined, not just when columns from that view are added into the
I managed to run an execution plan when the extra column is added and it is absolutely colossal. I did manage to find one task that has a cost of 51%:
RID Lookup (Heap). I'm not au fait with execution plans so not sure what this means or how to resolve it.