I have a rather large query that is running very very slow on a DB with very little data (the highest rowcount for the table this query accesses is 8K, and most of the filters are on IDs with EF navigation properties, the only parameter passed is one single guid).
It was running silky smooth but after a few change (sadly we changed a "lot" today to rework it into a single query while before it was over 30 in cshtml with lazy loading and is now all preloaded at the start of the file) it now runs at a snails pace (timeouts at 30sec and it takes 34). Running it in SQL server management studio provides the same runtime (34 sec), doing it multiple times doesn't speed it up, showing the execution plan, waiting a bit, then running it again and at some point it goes from 34 to 0 seconds.
We have ruled out the following:
Query plan execution caching (How can I clear the SQL Server query cache?) : time to execute doesn't change after flushing the plans, once it's fast it stays fast
Result isn't cached : changing the guid (which change the values of everything else) doesn't change things, once it's fast it stays fast
SQL Server Installation issue : when this happened on my coworker's PC i grabed the latest version and reproduced the same issue
Issues with the computer : nothing taking CPU time except SQL server, nothing else opened, both computers ran just fine and aren't related (not on the same network/ not on a domain etc), something that would effect them both negatively at the same instant can be ruled out.
We are at loss because it's really hard to debug "why" it's slow to begin with when it ends up executing instantaneously out of nowhere.
I'm not sure the query is that useful hence why i post it last, I renamed some tables (from T0 T1 etc) to make it easier to read but it's far from easy to digest. You'll find bellow both the C# code used to generate it and the SQL result that takes 34 seconds, again and again, until it takes 0!
LINQ (ef core latest, 3.1) :
Context.Orders
.Where(o => o.UniqueId == new Guid("MY GUID HERE"))
.SelectMany(o => o.Products)
.Where(FranceMontgolfieres.Models.Mappings.ProductOrder.IsTicketExpression)
.Select(po => new
{
po.UniqueId,
ProductId = po.Product.Id,
ProductName = po.Product.Name,
AllPassengerAreRegistered = po.Passengers.All(pa => pa.Passenger != null),
passengers = po.Passengers
.Select(pa => new
{
pa.UniqueId,
IsRegistered = pa.Passenger != null,
pa.Passenger.FirstName,
pa.Passenger.LastName,
FullName = pa.Passenger.FirstName + " " + pa.Passenger.LastName,
HasFlown = pa.Passenger.Manifests
.Any(m => m.History
.Any(h => h.Status == PassengerManifestStatus.Flown)),
HasPendingFlight = pa.Passenger.Manifests
.Any(m => m.History
.Any(h => h.Status == PassengerManifestStatus.Incoming)),
HasPendingReport = pa.Passenger.Manifests
.Any(m => m.History
.Any(h => h.Status == PassengerManifestStatus.Report)),
Manifests = pa.Passenger.Manifests
.Select(m => new
{
manifest = new
{
HasFlown = m.History
.Any(h => h.Status == PassengerManifestStatus.Flown),
HasPendingFlight = m.History
.Any(h => h.Status == PassengerManifestStatus.Incoming),
HasPendingReport = m.History
.Any(h => h.Status == PassengerManifestStatus.Report),
m.Manifest.StartDate,
m.Manifest.FlightBase.Name,
m.Manifest.FlightBase.Location,
m.Manifest.State
},
history = m.History
.Select(h => new
{
h.Date,
h.Status
})
})
}),
PendingReservationExists = po.PendingReservation != null
})
.ToList();
What we run before the query (still takes 0 sec after) to make sure it's not being cached:
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE;
GO
DBCC FREESYSTEMCACHE("ALL");
GO
DBCC FREESESSIONCACHE;
GO
DBCC FLUSHAUTHCACHE;
GO
SQL Query :
SELECT [ProductOrders_p1].[UniqueId],
[Products_p2].[Id],
[TranslatableString_t].[Id],
[TranslatableString_t].[MainPageCompanyDetailId],
[TranslatableString_t].[Text],
CASE
WHEN NOT EXISTS (
SELECT 1
FROM [ProductOrderPassenger] AS [p]
LEFT JOIN [Passengers] AS [p0] ON [p].[Id] = [p0].[ProductOrderPassengerId]
WHERE ([ProductOrders_p1].[Id] = [p].[ProductOrderId]) AND [p0].[Id] IS NULL) THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END,
CASE
WHEN [PendingReservations_p3].[Id] IS NOT NULL THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END,
[o].[Id],
[ProductOrders_p1].[Id],
[t3].[UniqueId],
[t3].[c],
[t3].[FirstName],
[t3].[LastName],
[t3].[c0],
[t3].[c1],
[t3].[c2],
[t3].[c3],
[t3].[Id],
[t3].[c4],
[t3].[c00],
[t3].[c10],
[t3].[StartDate],
[t3].[Id0],
[t3].[MainPageCompanyDetailId],
[t3].[Text],
[t3].[Id00],
[t3].[MainPageCompanyDetailId0],
[t3].[Text0],
[t3].[State],
[t3].[ManifestId],
[t3].[PassengerId],
[t3].[Id1],
[t3].[Date],
[t3].[Status],
[t3].[Id2]
FROM [Orders] AS [o]
INNER JOIN [ProductOrders] AS [ProductOrders_p1] ON [o].[Id] = [ProductOrders_p1].[OrderId]
LEFT JOIN [Products] AS [Products_p2] ON [ProductOrders_p1].[ProductId] = [Products_p2].[Id]
LEFT JOIN [TranslatableStrings] AS [TranslatableString_t] ON [Products_p2].[NameId] = [TranslatableString_t].[Id]
LEFT JOIN [PendingReservations] AS [PendingReservations_p3] ON [ProductOrders_p1].[Id] = [PendingReservations_p3].[ProductOrderId]
LEFT JOIN (
SELECT [ProductOrderPassengers_p4].[UniqueId], CASE
WHEN [Passengers_p5].[Id] IS NOT NULL THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS [c], [Passengers_p5].[FirstName], [Passengers_p5].[LastName], ([Passengers_p5].[FirstName] + N' ') + [Passengers_p5].[LastName] AS [c0], CASE
WHEN EXISTS (
SELECT 1
FROM [ManifestPassengers] AS [m]
WHERE ([Passengers_p5].[Id] IS NOT NULL AND ([Passengers_p5].[Id] = [m].[PassengerId])) AND EXISTS (
SELECT 1
FROM [ManifestPassengerHistories] AS [m0]
WHERE (([m].[ManifestId] = [m0].[ManifestPassengerManifestId]) AND ([m].[PassengerId] = [m0].[ManifestPassengerPassengerId])) AND ([m0].[Status] = 5))) THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS [c1], CASE
WHEN EXISTS (
SELECT 1
FROM [ManifestPassengers] AS [m1]
WHERE ([Passengers_p5].[Id] IS NOT NULL AND ([Passengers_p5].[Id] = [m1].[PassengerId])) AND EXISTS (
SELECT 1
FROM [ManifestPassengerHistories] AS [m2]
WHERE (([m1].[ManifestId] = [m2].[ManifestPassengerManifestId]) AND ([m1].[PassengerId] = [m2].[ManifestPassengerPassengerId])) AND ([m2].[Status] = 1))) THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS [c2], CASE
WHEN EXISTS (
SELECT 1
FROM [ManifestPassengers] AS [m3]
WHERE ([Passengers_p5].[Id] IS NOT NULL AND ([Passengers_p5].[Id] = [m3].[PassengerId])) AND EXISTS (
SELECT 1
FROM [ManifestPassengerHistories] AS [m4]
WHERE (([m3].[ManifestId] = [m4].[ManifestPassengerManifestId]) AND ([m3].[PassengerId] = [m4].[ManifestPassengerPassengerId])) AND ([m4].[Status] = 2))) THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS [c3], [ProductOrderPassengers_p4].[Id], [t2].[c] AS [c4], [t2].[c0] AS [c00], [t2].[c1] AS [c10], [t2].[StartDate], [t2].[Id] AS [Id0], [t2].[MainPageCompanyDetailId], [t2].[Text], [t2].[Id0] AS [Id00], [t2].[MainPageCompanyDetailId0], [t2].[Text0], [t2].[State], [t2].[ManifestId], [t2].[PassengerId], [t2].[Id1], [t2].[Date], [t2].[Status], [t2].[Id2], [ProductOrderPassengers_p4].[ProductOrderId]
FROM [ProductOrderPassenger] AS [ProductOrderPassengers_p4]
LEFT JOIN [Passengers] AS [Passengers_p5] ON [ProductOrderPassengers_p4].[Id] = [Passengers_p5].[ProductOrderPassengerId]
LEFT JOIN (
SELECT CASE
WHEN EXISTS (
SELECT 1
FROM [ManifestPassengerHistories] AS [m5]
WHERE (([m8].[ManifestId] = [m5].[ManifestPassengerManifestId]) AND ([m8].[PassengerId] = [m5].[ManifestPassengerPassengerId])) AND ([m5].[Status] = 5)) THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS [c], CASE
WHEN EXISTS (
SELECT 1
FROM [ManifestPassengerHistories] AS [m6]
WHERE (([m8].[ManifestId] = [m6].[ManifestPassengerManifestId]) AND ([m8].[PassengerId] = [m6].[ManifestPassengerPassengerId])) AND ([m6].[Status] = 1)) THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS [c0], CASE
WHEN EXISTS (
SELECT 1
FROM [ManifestPassengerHistories] AS [m7]
WHERE (([m8].[ManifestId] = [m7].[ManifestPassengerManifestId]) AND ([m8].[PassengerId] = [m7].[ManifestPassengerPassengerId])) AND ([m7].[Status] = 2)) THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS [c1], [m9].[StartDate], [t0].[Id], [t0].[MainPageCompanyDetailId], [t0].[Text], [t1].[Id] AS [Id0], [t1].[MainPageCompanyDetailId] AS [MainPageCompanyDetailId0], [t1].[Text] AS [Text0], [m9].[State], [m8].[ManifestId], [m8].[PassengerId], [m9].[Id] AS [Id1], [m10].[Date], [m10].[Status], [m10].[Id] AS [Id2]
FROM [ManifestPassengers] AS [m8]
INNER JOIN [Manifests] AS [m9] ON [m8].[ManifestId] = [m9].[Id]
LEFT JOIN [FlightBases] AS [f] ON [m9].[FlightBaseId] = [f].[Id]
LEFT JOIN [TranslatableStrings] AS [t0] ON [f].[NameId] = [t0].[Id]
LEFT JOIN [TranslatableStrings] AS [t1] ON [f].[LocationId] = [t1].[Id]
LEFT JOIN [ManifestPassengerHistories] AS [m10] ON ([m8].[ManifestId] = [m10].[ManifestPassengerManifestId]) AND ([m8].[PassengerId] = [m10].[ManifestPassengerPassengerId])
) AS [t2] ON [Passengers_p5].[Id] = [t2].[PassengerId]
) AS [t3] ON [ProductOrders_p1].[Id] = [t3].[ProductOrderId]
WHERE ([o].[UniqueId] = 'd26013b9-b9bc-4497-b351-544739851d3d') AND EXISTS (
SELECT 1
FROM [ProductProductComponents] AS [p6]
INNER JOIN (
SELECT [p7].[Id], [p7].[DescriptionId], [p7].[Discriminator], [p7].[DisplayDescriptionInProduct], [p7].[FunctionnalName], [p7].[HTPrice], [p7].[IsArchived], [p7].[IsChildrenPrice], [p7].[IsOptionnal], [p7].[IsSelectedInBasketByDefault], [p7].[IsShipping], [p7].[NameId], [p7].[ParentProductComponentId], [p7].[ShowInBasket], [p7].[ShowInInvoice], [p7].[VATRate], [p7].[AllowPartnerBase], [p7].[AllowWeekends], [p7].[ValidForTicks]
FROM [ProductComponents] AS [p7]
WHERE [p7].[Discriminator] IN (N'ProductComponent', N'FlightProductComponent')
) AS [t4] ON [p6].[ProductComponentId] = [t4].[Id]
WHERE ([Products_p2].[Id] IS NOT NULL AND ([Products_p2].[Id] = [p6].[ProductId])) AND ([t4].[Discriminator] = N'FlightProductComponent'))
ORDER BY [o].[Id], [ProductOrders_p1].[Id], [t3].[Id], [t3].[ManifestId], [t3].[PassengerId], [t3].[Id1], [t3].[Id2]
