0

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

  • 1
    Edit the question please and add the CREATE TABLE statements including all indexes, for both tables. – ypercubeᵀᴹ Feb 2 at 10:32
  • 1
    If you can provide the actual execution plan of the slow query, even better. – ypercubeᵀᴹ Feb 2 at 10:33
  • 1
    Upload your actual plans and add the links to your question. – Dan Guzman Feb 2 at 13:36
  • Thx, I did it, and thx for the hint for the webpage! – Visky Máté Feb 2 at 18:42
  • 1
    You should provide actual execution plan - not estimated – Martin Smith Feb 2 at 19:17
0

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, privacy policy and cookie policy

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