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


enter image description here

Force Order

enter image description here

  • 3
    select top 100000 * from anchor won't give you deterministic results. What is the query supposed to be doing? Also table definitions including indexes? Aug 5, 2015 at 20:36
  • 1
    I assume the CTE is "collapsed" by the optimizer (SQL-Server's optimizer simplifies CTEs this way - in difference with Postgres where CTEs are an optimization barrier). Aug 5, 2015 at 20:43
  • 1
    And the way you have written your query, the columns involved in the last join 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, we have no idea which tables belong to, so I don't know how anyone can help you. Try rewriting the query without CTEs , with all the tables in one FROM clause and edit the question. Aug 5, 2015 at 20:44
  • 1
    That is good to know, @ypercube. Sorry, I was hesitant to post the entire table definitions without first getting permission from my boss. The O_, OO_, IR_ correspond to the aliased tables in the CTEs, but I admit that it was unclear. Thanks for your help.
    – Synergist
    Aug 5, 2015 at 21:04
  • 1
    @Synergist - These are just screen shots from SQL Sentry Plan Explorer TBH i wasn't expecting removing the TOP to improve things. Removing the row goal must have altered things in a lucky way. Aug 5, 2015 at 21:13

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.