我在EF中有一个查询,它的性能非常糟糕,无法真正解释原因。查询大约需要30秒(!)返回20行数据。
编辑:我试图实现以下目标:CandidatesSignments是一种扩展的链接表,它包含关于哪个候选人属于哪个项目(CandidateId-ProjectId)和分数的信息。我只想返回得分最高的候选对象(使用相应的skip),其中要返回的对象类型不是完整的EntityType,而是一个较小的版本,它只获取两个字段(这就是为什么我选择不同的类型)
CompactProfile
).
var candidates = _db
.CandidateAssignments
.OrderByDescending(a => a.Score)
.Skip(skip)
.Take(20)
.Select(a => _db.Candidates.FirstOrDefault(c => c.Guid == a.CandidateId))
.Select(c => new CompactProfile
{
Id = c.Guid,
FirstName = c.FirstName,
LastName = c.LastName,
Image = c.Image,
City = c.City,
Company = c.Company,
Degree = c.Degree,
Haves = c.Haves,
JobTitle = c.JobTitle,
Languages = c.Languages,
Lattitude = c.Latitude,
Longitude = c.Longitude,
YearsOfExperience = c.YearsOfExperience,
ZipCode = c.ZipCode
})
.ToList();
候选表有大约60k行,这是一点,但不应该是一个有问题的大小。
我把索引放在
Candidate.Guid
使用
[Index]
属性(代码优先迁移)。
数据库托管在Azure上,因此我可以看到针对数据库执行的SQL查询:
SELECT
[Project1].[C1] AS [C1],
[Project1].[Guid] AS [Guid],
[Project1].[FirstName] AS [FirstName],
[Project1].[LastName] AS [LastName],
[Project1].[Image] AS [Image],
[Project1].[City] AS [City],
[Project1].[Company] AS [Company],
[Project1].[Degree] AS [Degree],
[Project1].[Haves] AS [Haves],
[Project1].[JobTitle] AS [JobTitle],
[Project1].[Languages] AS [Languages],
[Project1].[Latitude] AS [Latitude],
[Project1].[Longitude] AS [Longitude],
[Project1].[YearsOfExperience] AS [YearsOfExperience],
[Project1].[ZipCode] AS [ZipCode]
FROM ( SELECT
[Extent1].[Score] AS [Score],
[Limit1].[Guid] AS [Guid],
[Limit2].[FirstName] AS [FirstName],
[Limit3].[LastName] AS [LastName],
[Limit4].[Image] AS [Image],
[Limit5].[City] AS [City],
[Limit6].[Company] AS [Company],
[Limit7].[Degree] AS [Degree],
[Limit8].[Haves] AS [Haves],
[Limit9].[JobTitle] AS [JobTitle],
[Limit10].[Languages] AS [Languages],
[Limit11].[Latitude] AS [Latitude],
[Limit12].[Longitude] AS [Longitude],
[Limit13].[YearsOfExperience] AS [YearsOfExperience],
[Limit14].[ZipCode] AS [ZipCode],
1 AS [C1]
FROM [dbo].[CandidateAssignment] AS [Extent1]
OUTER APPLY (SELECT TOP (1) [Extent2].[Guid] AS [Guid]
FROM [dbo].[Candidate] AS [Extent2]
WHERE [Extent2].[Guid] = [Extent1].[CandidateId] ) AS [Limit1]
OUTER APPLY (SELECT TOP (1) [Extent3].[FirstName] AS [FirstName]
FROM [dbo].[Candidate] AS [Extent3]
WHERE [Extent3].[Guid] = [Extent1].[CandidateId] ) AS [Limit2]
OUTER APPLY (SELECT TOP (1) [Extent4].[LastName] AS [LastName]
FROM [dbo].[Candidate] AS [Extent4]
WHERE [Extent4].[Guid] = [Extent1].[CandidateId] ) AS [Limit3]
OUTER APPLY (SELECT TOP (1) [Extent5].[XingImage64] AS [XingImage64]
FROM [dbo].[Candidate] AS [Extent5]
WHERE [Extent5].[Guid] = [Extent1].[CandidateId] ) AS [Limit4]
OUTER APPLY (SELECT TOP (1) [Extent6].[City] AS [City]
FROM [dbo].[Candidate] AS [Extent6]
WHERE [Extent6].[Guid] = [Extent1].[CandidateId] ) AS [Limit5]
OUTER APPLY (SELECT TOP (1) [Extent7].[Company] AS [Company]
FROM [dbo].[Candidate] AS [Extent7]
WHERE [Extent7].[Guid] = [Extent1].[CandidateId] ) AS [Limit6]
OUTER APPLY (SELECT TOP (1) [Extent8].[Degree] AS [Degree]
FROM [dbo].[Candidate] AS [Extent8]
WHERE [Extent8].[Guid] = [Extent1].[CandidateId] ) AS [Limit7]
OUTER APPLY (SELECT TOP (1) [Extent9].[Haves] AS [Haves]
FROM [dbo].[Candidate] AS [Extent9]
WHERE [Extent9].[Guid] = [Extent1].[CandidateId] ) AS [Limit8]
OUTER APPLY (SELECT TOP (1) [Extent10].[JobTitle] AS [JobTitle]
FROM [dbo].[Candidate] AS [Extent10]
WHERE [Extent10].[Guid] = [Extent1].[CandidateId] ) AS [Limit9]
OUTER APPLY (SELECT TOP (1) [Extent11].[Languages] AS [Languages]
FROM [dbo].[Candidate] AS [Extent11]
WHERE [Extent11].[Guid] = [Extent1].[CandidateId] ) AS [Limit10]
OUTER APPLY (SELECT TOP (1) [Extent12].[Latitude] AS [Latitude]
FROM [dbo].[Candidate] AS [Extent12]
WHERE [Extent12].[Guid] = [Extent1].[CandidateId] ) AS [Limit11]
OUTER APPLY (SELECT TOP (1) [Extent13].[Longitude] AS [Longitude]
FROM [dbo].[Candidate] AS [Extent13]
WHERE [Extent13].[Guid] = [Extent1].[CandidateId] ) AS [Limit12]
OUTER APPLY (SELECT TOP (1) [Extent14].[YearsOfExperience] AS [YearsOfExperience]
FROM [dbo].[Candidate] AS [Extent14]
WHERE [Extent14].[Guid] = [Extent1].[CandidateId] ) AS [Limit13]
OUTER APPLY (SELECT TOP (1) [Extent15].[ZipCode] AS [ZipCode]
FROM [dbo].[Candidate] AS [Extent15]
WHERE [Extent15].[Guid] = [Extent1].[CandidateId] ) AS [Limit14]
) AS [Project1]
ORDER BY row_number() OVER (ORDER BY [Project1].[Score] DESC)
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
不幸的是,我的SQL技能还不足以识别这里的问题。
如何重建此查询以获得合理的性能?
