EF query very slow

Question

I have a query in EF which performs very badly and cannot really explain why. The query takes about 30s (!) to return 20 rows of data.

EDIT: I am trying to achieve the following: CandidateAssignments is a kind of extended link table and contains information on which candidate belongs to which project (CandidateId - ProjectId) and with what score. I would like to simply return the top scored candidates (with the according skip), where the object type to return is not the full EntityType, but a smaller version which only fetches a couple of fields (that's why I select to a different typ 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();

The Candidates table has ~60k rows which is a bit, but shouldn't be a problematic magnitude at all.

I have placed an Index on Candidate.Guid using the [Index] attribute (code first migrations).

The database is hosted on Azure, so I can see the SQL query which is executed against the database:

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

Unfortunately my SQL skills aren't good enough to identify the problem here.

How can I rebuild this query to achieve a reasonable performance?

Answer 1

In addition to Janne's answer, here ist the LINQ version, which cuts the query time down to around 200ms:

var candidates = _db.CandidateAssignments
            .OrderByDescending(a => a.Score)
            .Skip(skip)
            .Take(20)
            .Join(_db.Candidates, a => a.CandidateId, c => c.Guid, (a, c) => new CompactProfile
            {
                Id = c.Guid,
                FirstName = c.FirstName,
                LastName = c.LastName,
                Image = c.XingImage64,
                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();

Answer 2

I would guess your subqueries executed against the table scan is the reason. I guess you could try something like this to get the same results (top 20 candidates based on their assignments?)

var candidates = (from a in _db.CandidateAssignments
    join c in _db.Candidates on c.Guid equals a.CandidateId
    orderby a.Score Descending
    select c)
    .Skip(skip)
    .Take(20)
    .Select(c => ...)
    .ToList();