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

fast:

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()))

slow:

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

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

fast:

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()))

https://www.brentozar.com/pastetheplan/?id=S1t8EDQ4V

slow:

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

https://www.brentozar.com/pastetheplan/?id=rJKzNvmEV