Help, SELECT * from view takes 2.5 hours

Calvin Jones 2015-08-04 16:19:10

This view contains 6 CTE's and several sub-queries. The query takes 2.5 hours, does 33,856,865 reads and returns 21,892 rows. In particular, the concern is the area of the execution plan where the top operation is a Hash Match (Inner Join) 19.0% which generates 11,590,598,636 rows.
Object24.Index26
Object27.Index27
Object26.Index28
Object24.Index29
Object25.Index30

Aaron Bertrand 2015-08-04 20:22:54
The query text is just SELECT * FROM dbo.View. Pretty hard to make any suggestions for the query itself unless you can post at least enough of the view definition (anonymized, if need be) that we can determine why those operators come into play that way.