Query Optimization Help – Takes over an hour to run

There's no easy way to say this: you have just about every common performance anti-pattern here! Complex, non-simplifiable views, repeated non-inline function access, repeated view materialization, mismatched collations, joins and predicates involving complex (statistics-proof) expressions, huge query trees, "one query to rule them all", repeated query evaluations caused by over-use of CTEs, row-goal issues caused by TOP, and most likely execution-time sort and hash spills caused by poor estimations (caused by opaque and complex code), residual predicates…the list is long.

Some of the comments in the code seem to indicate someone is at least aware of some of the problems, but major performance improvements will require a transition to a more relational and optimizer-friendly architecture. I'm sorry if that all sounds a bit negative, but there is an awful lot of code and query tree there, so the best I can do is point to the fundamental causes. Addressing those will probably be painful, and perhaps not even practical, but there we are.