How fickle the SQL!
So I was optimizing a query that displays a timetable of television programming. I'm working to try to optimize it because its currently taking 3-10 seconds per execution, which is less than optimal, considering that the query needs to be made 48 times per pageload, to load 48 timetables.
The query looks something like this:
SELECT * FROM TimeSlotsInADay CROSS JOIN (
SELECT DISTINCT
dayofweek FROM Dashboard_TEST.dbo.broadcastDates
)
INNER JOIN ProgrammingData
Now normally you'd think that wouldn't be too bad. Basically we have a list of timeslots, and we make an entry for every timeslot, every day. But it turns out, the bottleneck according to the Execution Planner was actually the cross join. So I ran the subquery and found out that the subquery was returning days in reverse order, while all my Indexes on those day fields were set to ASC order. So basically when the JOIN happened on programming data, because the list was reversed, the index scanning ended up being equivalent to a table scan.
So I modified the list to ORDER BY on the subquery (which requires a TOP statement), so the Subquery now looks like:
SELECT
DISTINCT TOP 7 dayofweek FROM Dashboard_TEST.dbo.broadcastDates ORDER BY 1And now the entire parent query executes in under a second. Hope this helps someone