Strange SQL generated by Linq2SQL
I have a linq query that looks like this: (part of a larger query, but this demonstrates the problem)
from guarantee in tblGuarantees
from nextExptDev in
(from gd in tblGuaranteeDevaluations
where gd.fkGuaranteeId == guarantee.pkGuaranteeId &&
gd.Date == null
orderby gd.ExpectedDate ascending
select new
{
gd.Sum,
gd.CurrencyId,
gd.ExpectedDate
}).Take(1).DefaultIfEmpty()
select new
{
guarantee.pkGuaranteeId,
nextExptDev.Sum,
nextExptDev.CurrencyId,
nextExptDev.ExpectedDate
}
It generates the following SQL:
SELECT [t0].[pkGuaranteeId],
[t3].[Sum] AS [Sum],
[t3].[CurrencyId] AS [CurrencyId],
[t3].[ExpectedDate] AS [ExpectedDate2]
FROM [dbo].[tblGuarantee] AS [t0]
CROSS APPLY ((SELECT NULL AS [EMPTY]) AS [t1]
OUTER APPLY (SELECT TOP (1) [t2].[Sum],
[t2].[CurrencyId],
[t2].[ExpectedDate]
FROM [dbo].[tblGuaranteeDevaluation] AS [t2]
WHERE ( [t2].[fkGuaranteeId] = [t0].[pkGuaranteeId] )
AND ( [t2].[Date] IS NULL )
ORDER BY [t2].[ExpectedDate]) AS [t3])
ORDER BY [t3].[ExpectedDate] -- Why here?
My question is, why is it the last one ORDER BY
there? In my big question that really hurts performance and I can't figure out why this is needed. Any hint to write this better is also appreciated.
source to share
In the request, you place an order in
from gd in tblGuaranteeDevaluations
where gd.fkGuaranteeId == guarantee.pkGuaranteeId &&
gd.Date == null
orderby gd.ExpectedDate ascending
This made the inner query order, in the inner block
SELECT TOP (1) [t2].[Sum], [t2].[CurrencyId], [t2].[ExpectedDate]
FROM [dbo].[tblGuaranteeDevaluation] AS [t2]
WHERE ([t2].[fkGuaranteeId] = [t0].[pkGuaranteeId]) AND ([t2].[Date] IS NULL)
ORDER BY [t2].[ExpectedDate]
But you are "joining" two different sets, the zero set and the inner block, to do this, to ensure ordering, the code must put a different order for the "join" result set, so the order in the outer set is automatic code generation, but since the set is already ordered, the latter order should not degrade performance.
source to share
What happens if you switch DefaultIfEmpty()
to a call Take(1)
? How about replacing both with a call FirstOrDefault
? How about using let nextExptDev = ...
instead from nextExptDev in ...
?
Try the latter for me ... It seems that placement order by
within the projection conveys the rest of the request that you want the whole thing to be ordered. Instead, see if you can just fetch it from an ordered source. The IE: from gd in tblGuaranteeDevaluations.OrderBy(t => t.ExpectedDate)
.
source to share