Why is FORCE JOIN option slowing down query and forcing execution plan to sort prior to joining tables?

Question

I'm linking Orders data with the Results data for those orders. Here's the SQL query for joining and selecting the

; with orders as (
    select o.*, otr.*, oo.*
    from Order o
    inner join OrderTransitionRecord otr
        on otr.Order_oid = o.ObjectID
    inner join OccurrenceOrder oo
        on oo.Order_oid = o.ObjectID
), results as (
    select ir.*, irsi.*
    from InvestigationResult ir
    left outer join InvestigationResultSuppInfo irsi
        on irsi.ObjectID = ir.ResultSuppInfo_oid
), anchor as (
    select orders.*, results.*
    from orders
    inner join results
        on results.Occurrence_oid = orders.OO_oid
        and results.IR_Patient_oid = orders.O_Patient_oid
        and results.IR_PatientVisit_oid = orders.O_PatientVisit_oid
)
select top 100000 *
from anchor

This takes ~35 seconds and follows this execution plan. If you look at the execution plan, you'll see that it first joins (OccurrenceOrder, OrderTransitionRecord) with InvestigationResult...and then joins that result with Order. My question is why doesn't it first join all three tables (Order, OccurrenceOrder, OrderTransitionRecord) that are inside the orders CTE prior to joining with the tables (InvestigationResult, InvestigationResultSuppInfo) that are in the results CTE?

I tried forcing the order of the joins by adding the OPTION(FORCE JOIN) option at the end of the select query. However, it slowed the query down by a factor of 2 (~75 seconds). Here's the execution plan for when I force the join order. If you take a look at it, you'll see that the join order is forced correctly, but it has to sort the results of clustered index scans on all 3 Order tables before joining them. Why does it need to do that?

What can I do to speed up my query? I've already made sure that the tables have indices for all the columns that are needed in all the joins. Any and all help would be much appreciated. Thanks

Original

Force Order

Answer 1

Okay, after heeding advice from the comments, I removed the top 10000 * from the select statement. After doing so, forcing the join order actually sped up the query as intended. It took 48 seconds (vs. 60 sec).

Here's the original execution plan.

And here's the forced join execution plan.