isuckatsql (10/4/2013)
In testing the sub queries proved faster than Joins, as did the MAXDOP and NOLOCK.
So you don't mind if your results are inaccurate. Have you confirmed with the users that this report is for that they're also fine with their reports potentially being wrong?
I have researched a ton of data on the internet regarding query performance, and hired a couple of very senior SQL DBA's to also help with query performance and optimization, and this is what i have ended up with.
At risk of sounding rude and arrogant, if this is what those 'very senior' DBAs came up with, I would question just how senior they are, because the posted query has a some rather basic performance problems with it (the hints, the catch-all query), not to mention a demonstrated lack of understanding on what a CTE is (looking in the catalog views for a CTE).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability