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.
Using Scott's answer I created a non-clustered index on the row that was causing the expensive RID lookup. It improved performance quite a lot - down from over a minute to around 30 seconds to return the 50 row limit.
I think Nic is right in his suggestion that the datatype conversion from date to char(8) in the join condition is causing the bulk of the cost when querying. Going to work on another way to join view3.