When To Break Down Complex Queries

Four problematic query patterns:

OR logic in the WHERE clause      
In this pattern, the condition on each side of the OR operator in the WHERE or JOIN clause evaluates different tables. This can be resolved by use of a UNION operator instead of the OR operator in the WHERE or JOIN clause.

Aggregations in intermediate results sets      
This pattern has joins on aggregated data sets, which can result in poor performance. This can be resolved by placing the aggregated intermediate result sets in temporary tables.

A large number of very complex joins      
This pattern has a large number of joins, especially joins on ranges, which can result in poor performance because of progressively degrading estimates of cardinality. This can be resolved by breaking down the query and using temporary tables.

A CASE clause in the WHERE or JOIN clause      
This pattern has CASE operators in the WHERE or JOIN clauses, which cause poor estimates of cardinality. This can be resolved by breaking down the cases into separate queries and using the Transact-SQL IF statement to direct the flow for the conditions.

https://blogs.msdn.microsoft.com/sqlcat/2013/09/09/when-to-break-down-complex-queries/