I have a query that's returning the TOP N records after some slightly ugly CTE joining.
- I found that if I ran TOP 2431 records it would take ~30 seconds ...
- But if I ran the TOP 2432 records (i.e. 1 additional record) it only took <2 seconds ... :(
Eventually I realised this was because the statistics were out of date, and I updated the stats, cleared the plan caches, and got it working sensibly ... 2431 records returns in <2 seconds.
But then I tried a couple of other counts and found that 1500 records is also broken (29 seconds), and that the query plan is still broken in exactly the same way, despite now having completely fresh stats.
The difference between the fast and slow query execution plans appears to be roughly "Hash Match" (fast) vs "Lazy Table Spool"(slow):
Fast:
Slow:
(The feed coming in from below is identical in each plan)
The Index scanned on EnquiryLegs is the Booking column; FK to the Bookings table.
This was exactly the same difference previously in 2431 (slow; Table Spool) vs 2432 (fast; Hash Match)
What's going on, why does it use a so-much-worse Exec.Plan., and how do I fix it?
Full query.
Yes, the CTE is gross, and I have plans to fix that separately. But clearly the query can run fine (well ... adequately .. 1.5 seconds is still very sad, but acceptable to the client for the time permitted) ... it just isn't doing so all the time
WITH EarliestDepartureDates AS (SELECT EnquiryLegs.Booking,
MIN(DATETIMEFROMPARTS(
Legs.DepartureYear,
Legs.DepartureMonth,
ISNULL(l.DepartureDay, 1),
ISNULL(l.DepartureTime, 0) / 100,
ISNULL(l.DepartureTime, 0) % 100, 0, 0)) AS [Date]
FROM dbo.EnquiryLegs
JOIN dbo.Legs ON Legs.ID = EnquiryLegs.ID
GROUP BY EnquiryLegs.Booking)
SELECT TOP 1500 Bookings.ID, Bookings.foo, ...
, CustomerContacts.ID, CustomerContacts.bar ...
, BaseContacts.FriendlyId, BaseContacts.baz...
FROM Bookings
LEFT JOIN CustomerContacts ON CustomerContacts.ID = Bookings.CustomerContact
LEFT JOIN BaseContacts ON BaseContacts.ID = CustomerContacts.ID
INNER JOIN EarliestDepartureDates ON EarliestDepartureDates.Booking = Bookings.ID
ORDER BY Bookings.CreatedAt DESC
"Updating the stats":
sp_updatestats
UPDATE STATISTICS Bookings WITH FULLSCAN
UPDATE STATISTICS EnquiryLegs WITH FULLSCAN
UPDATE STATISTICS BaseLegs WITH FULLSCAN
DBCC FREEPROCCACHE;
DBCC FREESYSTEMCACHE ('SQL Plans');