我该如何提高这个LINQ的性能
本文关键字:LINQ 性能 何提高 | 更新日期: 2023-09-27 17:59:44
更新多亏了@usr,我只需更改就可以将时间缩短到大约3秒
.Select(
log => log.OrderByDescending(
d => d.DateTimeUTC
).FirstOrDefault()
)
至
.Select(
log => log.OrderByDescending(
d => d.Id
).FirstOrDefault()
)
我有一个数据库,有两个表——日志和收集器——我正在使用实体框架读取它们。共有86条收集器记录,每条记录都有50000多条相应的日志记录。
我想获得每个收集器的最新日志记录,这可以用这个SQL 轻松完成
SELECT CollectorLogModels_1.Status, CollectorLogModels_1.NumericValue,
CollectorLogModels_1.StringValue, CollectorLogModels_1.DateTimeUTC,
CollectorSettingsModels.Target, CollectorSettingsModels.TypeName
FROM
(SELECT CollectorId, MAX(Id) AS Id
FROM CollectorLogModels GROUP BY CollectorId) AS RecentLogs
INNER JOIN CollectorLogModels AS CollectorLogModels_1
ON RecentLogs.Id = CollectorLogModels_1.Id
INNER JOIN CollectorSettingsModels
ON CollectorLogModels_1.CollectorId = CollectorSettingsModels.Id
这大约需要2秒钟才能执行。
我能用LINQ得到的最接近的是下面的
var logs = context.Logs.Include(co => co.Collector)
.GroupBy(
log => log.CollectorId, log => log
)
.Select(
log => log.OrderByDescending(
d => d.DateTimeUtc
).FirstOrDefault()
)
.Join(
context.Collectors,
(l => l.CollectorId),
(c => c.Id),
(l, c) => new
{
c.Target,
DateTimeUTC = l.DateTimeUtc,
l.Status,
l.StringValue,
CollectorName = c.TypeName
}
).OrderBy(
o => o.Target
).ThenBy(
o => o.CollectorName
)
;
这会产生我想要的结果,但执行大约需要35秒。
这将成为以下SQL
SELECT
[Distinct1].[CollectorId] AS [CollectorId],
[Extent3].[Target] AS [Target],
[Limit1].[DateTimeUtc] AS [DateTimeUtc],
[Limit1].[Status] AS [Status],
[Limit1].[StringValue] AS [StringValue],
[Extent3].[TypeName] AS [TypeName]
FROM (SELECT DISTINCT
[Extent1].[CollectorId] AS [CollectorId]
FROM [dbo].[CollectorLogModels] AS [Extent1] ) AS [Distinct1]
OUTER APPLY (SELECT TOP (1) [Project2].[Status] AS [Status], [Project2].[StringValue] AS [StringValue], [Project2].[DateTimeUtc] AS [DateTimeUtc], [Project2].[CollectorId] AS [CollectorId]
FROM ( SELECT
[Extent2].[Status] AS [Status],
[Extent2].[StringValue] AS [StringValue],
[Extent2].[DateTimeUtc] AS [DateTimeUtc],
[Extent2].[CollectorId] AS [CollectorId]
FROM [dbo].[CollectorLogModels] AS [Extent2]
WHERE [Distinct1].[CollectorId] = [Extent2].[CollectorId]
) AS [Project2]
ORDER BY [Project2].[DateTimeUtc] DESC ) AS [Limit1]
INNER JOIN [dbo].[CollectorSettingsModels] AS [Extent3] ON [Limit1].[CollectorId] = [Extent3].[Id]
ORDER BY [Extent3].[Target] ASC, [Extent3].[TypeName] ASC
如何使性能更接近于单独使用SQL可以实现的性能?
在原始SQL中,您可以从与MAX(ID)不同的行中选择集合DateTimeUTC。这可能是个bug。外汇基金没有这个问题。它在语义上并不相同,这是一个更难查询的问题。
如果将EF查询重写为在结构上与SQL查询相同,那么您将获得相同的性能。我看不出英孚会不支持的。
计算具有EF的max(id)
,并在此基础上加入。
我遇到了完全相同的问题,我通过添加索引解决了它。
我的一个查询需要45秒才能完成,我设法在不到一秒钟的时间内完成了。