Simplify a complex query II
I guess there are a few ways to tackle this, depending on what you are allowed to do. IF you just need to understand it better then I'd begin by looking at each of the sub selects and see how they work/perform on their own.
I'd then begin to join them back together bit by bit to see how things looked then and get an idea of how it all works together.
The first step though for me would be to check when the statistics were last updated as the estimate and actual values are way out and simply updating the stats may give you a decent improvement.
Let me know if I'm on the right tracks to what you're trying to achieve.
P.S. for testing it could be easier to work with if the more complex sub queries are put into temp tables, this may or may not be the way to do it in production but it would probably make it easier to work through 🙂