I have 2 select, the first is superfast, and the second is 10x more slower. It seems to be first join everything from the table, than running the startdate filter, and the first case it is filtering for the id first and only than running the select. And yes got it, first he is running the subselect, sees that it is only 400 record, and doing the rest. So how can I hint that without this workaround to run that start date filter first?

  • Tourneys table: 1M records (has index on StartDate)
  • TourneyPlayers table: 2M records (has index on TourneyId ofc)
  • Tourneys after the start date filter: 400 records


select *
from Tourneys t right join TourneyPlayers tp on t.Id = tp.TourneyId 
where 1=1 
  and t.id in (select id from Tourneys where StartDate > DATEADD(hour,-1,GETDATE()))



select * 
from Tourneys t, TourneyPlayers tp
where 1=1
  and t.StartDate > DATEADD(hour,-1,GETDATE())
  and t.Id = tp.TourneyId


  • 1
    Edit the question please and add the CREATE TABLE statements including all indexes, for both tables. Feb 2, 2019 at 10:32
  • 1
    If you can provide the actual execution plan of the slow query, even better. Feb 2, 2019 at 10:33
  • 1
    Upload your actual plans and add the links to your question.
    – Dan Guzman
    Feb 2, 2019 at 13:36
  • 1
    You should provide actual execution plan - not estimated Feb 2, 2019 at 19:17
  • 1
    Any update on providing the XML for the actual plan? That will allow us to see if things like estimated row count for t.StartDate > DATEADD(hour,-1,GETDATE()) is correct Feb 5, 2019 at 13:30

1 Answer 1


Your two queries are fundamentally different. The fast query is right-joining the TourneyPlayers table to Tourney, and then filtering. Based on the order of operations, it is likely seeking records on Tourney first, then seeking records on TourneyPlayers that match the TourneyId using the correct index.

In the slow query, you're inner joining the tables, so the engine is scanning the TourneyPlayers clustered index and seeking the Tourney records using the date field index. It then has to combine these record sets to produce the final result set.

You need to determine which join you require for the relationship between your tables and the result set you need, then from there you can construct your query correctly and assess your indexing strategy for these tables.

For example, do you want to return every Tourney record regardless of whether there is an associated TourneyPlayer record? Do you want every TourneyPlayer record, regardless of whether there is an associated Tourney record? Do you want only those Tourney records that have an associated TourneyPlayer record?

Once you've answered this question, you will have the answer for your join and can proceed from there with query design and index optimisation.

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.