BSavoie (9/29/2016)
...Many of our queries could care less about the many statuses for a given Order, all they care about is the current status. For these queries, having to join Orders & OrderStatuses has become much, much too slow.
...
Have you investigated the cause? Is there an index on the OrderStatus table to support these queries?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden