Related to this question: In what order does execution on WHERE and ON clauses work?
I was reading this page about APPLY:
And I don't understand how the CROSS APPLY, TOP, and DESC are making this query faster.
Original query:
SELECT s.StartedAt, s.EndedAt, c.AirTime
FROM dbo.Commercials s JOIN dbo.Calls c
ON c.AirTime >= s.StartedAt AND c.AirTime < s.EndedAt
WHERE c.AirTime BETWEEN '20080701' AND '20080701 03:00'
Faster query:
SELECT s.StartedAt, s.EndedAt, c.AirTime
FROM dbo.Calls c CROSS APPLY(
SELECT TOP 1 s.StartedAt, s.EndedAt FROM dbo.Commercials s
WHERE c.AirTime >= s.StartedAt AND c.AirTime < s.EndedAt
ORDER BY s.StartedAt DESC) AS s
WHERE c.AirTime BETWEEN '20080701' AND '20080701 03:00'
I don't know what specific question to ask, because I don't get it.
Your second query is likely faster because you are limiting the result set to join against to a maximum of 1 row; the first row. There is little to no computation or join matching required for that. In this case, your
CROSS APPLY
is acting like a singular function.