Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query that was generated by C#/Linq-To-Entities code. As with this kind of query, it looks seriously ugly and I doubt it would help to include it here. But I have run it through a query profiler and found that 50.5% of the processing time is happening in a "Nested Loops (Inner Join)" step, which is joining a clustered index seek on a2_AssessmentQuestion (0.3%) and a clustered index scan on the same table (13.1%).

I confess I don't truly understand what this "nested loops join" is doing, but why would the query be doing a scan on the table right after doing an index seek, when the fields it's looking up are available directly through another index on exactly the required fields? And yes, I have run update statistics a2_AssessmentQuestion, to no effect.

Not being an expert in understanding the query plan, I'd appreciate if you could prod me for answers to relevant questions; I might just not know enough to have supplied you with enough useful info.

Thanks!

enter image description here

EDIT: FWIW, here's the query:

SELECT 
[Project6].[ID] AS [ID], 
[Project6].[ID2] AS [ID1], 
[Project6].[ID1] AS [ID2], 
[Project6].[ID3] AS [ID3], 
[Project6].[ID5] AS [ID4], 
[Project6].[ID7] AS [ID5], 
[Project6].[ID6] AS [ID6], 
[Project6].[ID8] AS [ID7], 
[Project6].[ID9] AS [ID8], 
[Project6].[ID11] AS [ID9], 
[Project6].[ID10] AS [ID10], 
[Project6].[ID12] AS [ID11], 
[Project6].[ID13] AS [ID12], 
[Project6].[Number] AS [Number], 
[Project6].[ScholarAssessmentID] AS [ScholarAssessmentID], 
[Project6].[C1] AS [C1], 
[Project6].[GradingStyle] AS [GradingStyle], 
[Project6].[PartID] AS [PartID], 
[Project6].[C2] AS [C2], 
[Project6].[ID4] AS [ID13], 
[Project6].[Description] AS [Description], 
[Project6].[Version] AS [Version], 
[Project6].[DifficultyLevel] AS [DifficultyLevel], 
[Project6].[AssessmentQuestionID] AS [AssessmentQuestionID], 
[Project6].[GradingDueDate] AS [GradingDueDate], 
[Project6].[C3] AS [C3], 
[Project6].[AssessmentFormat] AS [AssessmentFormat], 
[Project6].[C4] AS [C4], 
[Project6].[Structure] AS [Structure], 
[Project6].[Title] AS [Title], 
[Project6].[C5] AS [C5], 
[Project6].[AssessmentFormat1] AS [AssessmentFormat1], 
[Project6].[C6] AS [C6], 
[Project6].[Label] AS [Label]
FROM ( SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[ScholarAssessmentID] AS [ScholarAssessmentID], 
    [Extent1].[AssessmentQuestionID] AS [AssessmentQuestionID], 
    [Join1].[ID1] AS [ID1], 
    [Join1].[ID2] AS [ID2], 
    [Extent4].[ID] AS [ID3], 
    [Extent4].[PartID] AS [PartID], 
    [Extent4].[Number] AS [Number], 
    [Extent4].[DifficultyLevel] AS [DifficultyLevel], 
    [Extent4].[Structure] AS [Structure], 
    [Extent4].[GradingStyle] AS [GradingStyle], 
    [Extent5].[ID] AS [ID4], 
    [Extent5].[Description] AS [Description], 
    [Extent5].[Version] AS [Version], 
    [Extent6].[ID] AS [ID5], 
    [Extent6].[GradingDueDate] AS [GradingDueDate], 
    [Extent6].[Title] AS [Title], 
    [Join6].[ID3] AS [ID6], 
    [Join6].[ID4] AS [ID7], 
    [Extent9].[ID] AS [ID8], 
    [Extent10].[ID] AS [ID9], 
    [Extent10].[AssessmentFormat] AS [AssessmentFormat], 
    [Join10].[ID5] AS [ID10], 
    [Join10].[ID6] AS [ID11], 
    [Extent13].[ID] AS [ID12], 
    [Extent14].[ID] AS [ID13], 
    [Extent14].[AssessmentFormat] AS [AssessmentFormat1], 
     CAST( [Extent4].[Points] AS bigint) AS [C1], 
     CAST( [Extent1].[PointsAwarded] AS decimal(19,0)) AS [C2], 
     CAST( [Extent6].[DueDate] AS datetime2) AS [C3], 
     CAST( [Extent4].[Number] AS bigint) AS [C4], 
    [Join14].[Label] AS [Label], 
    CASE WHEN ([Extent4].[RubricWeight] IS NOT NULL) THEN  CAST( [Extent4].[RubricWeight] AS int) ELSE 0 END AS [C5], 
    CASE WHEN ([Join14].[ScholarAssessmentAnswerID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C6]
    FROM            [dbo].[a2_ScholarAssessmentAnswer] AS [Extent1]
    INNER JOIN  (SELECT [Extent2].[ID] AS [ID1], [Extent2].[AssessmentID] AS [AssessmentID], [Extent3].[ID] AS [ID2]
        FROM  [dbo].[a2_ScholarAssessment] AS [Extent2]
        LEFT OUTER JOIN [dbo].[a2_ScholarFNPAssessment] AS [Extent3] ON [Extent2].[ID] = [Extent3].[ID] ) AS [Join1] ON [Extent1].[ScholarAssessmentID] = [Join1].[ID1]
    INNER JOIN [dbo].[a2_AssessmentQuestion] AS [Extent4] ON [Extent1].[AssessmentQuestionID] = [Extent4].[ID]
    LEFT OUTER JOIN [dbo].[a2_AssessmentQuestionType] AS [Extent5] ON [Extent4].[QuestionTypeID] = [Extent5].[ID]
    INNER JOIN [dbo].[a2_AssessmentPart] AS [Extent6] ON [Extent4].[PartID] = [Extent6].[ID]
    INNER JOIN  (SELECT [Extent7].[ID] AS [ID3], [Extent7].[AssessmentID] AS [AssessmentID], [Extent8].[ID] AS [ID4]
        FROM  [dbo].[a2_ScholarAssessment] AS [Extent7]
        LEFT OUTER JOIN [dbo].[a2_ScholarFNPAssessment] AS [Extent8] ON [Extent7].[ID] = [Extent8].[ID] ) AS [Join6] ON [Extent1].[ScholarAssessmentID] = [Join6].[ID3]
    INNER JOIN [dbo].[a2_Assessment] AS [Extent9] ON [Join6].[AssessmentID] = [Extent9].[ID]
    LEFT OUTER JOIN [dbo].[a2_AssessmentType] AS [Extent10] ON [Extent9].[AssessmentTypeID] = [Extent10].[ID]
    INNER JOIN  (SELECT [Extent11].[ID] AS [ID5], [Extent11].[AssessmentID] AS [AssessmentID], [Extent12].[ID] AS [ID6]
        FROM  [dbo].[a2_ScholarAssessment] AS [Extent11]
        LEFT OUTER JOIN [dbo].[a2_ScholarFNPAssessment] AS [Extent12] ON [Extent11].[ID] = [Extent12].[ID] ) AS [Join10] ON [Extent1].[ScholarAssessmentID] = [Join10].[ID5]
    INNER JOIN [dbo].[a2_Assessment] AS [Extent13] ON [Join10].[AssessmentID] = [Extent13].[ID]
    LEFT OUTER JOIN [dbo].[a2_AssessmentType] AS [Extent14] ON [Extent13].[AssessmentTypeID] = [Extent14].[ID]
    LEFT OUTER JOIN  (SELECT [Extent15].[ScholarAssessmentAnswerID] AS [ScholarAssessmentAnswerID], [Extent16].[Label] AS [Label]
        FROM  [dbo].[a2_ScholarAssessmentAnswerSelection] AS [Extent15]
        INNER JOIN [dbo].[a2_AssessmentAnswers] AS [Extent16] ON [Extent15].[AssessmentAnswerID] = [Extent16].[ID] ) AS [Join14] ON [Extent1].[ID] = [Join14].[ScholarAssessmentAnswerID]
    WHERE (1538 = [Join1].[AssessmentID]) AND ( NOT EXISTS (SELECT 
        1 AS [C1]
        FROM ( SELECT 
            [Extent18].[ID] AS [ID]
            FROM  [dbo].[a2_AssessmentQuestion] AS [Extent17]
            INNER JOIN [dbo].[a2_AssessmentQuestion] AS [Extent18] ON [Extent17].[PartID] = [Extent18].[PartID]
            WHERE [Extent1].[AssessmentQuestionID] = [Extent17].[ID]
        )  AS [Project1]
        WHERE ( NOT EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[a2_ScholarAssessmentAnswer] AS [Extent19]
            WHERE ([Extent1].[ScholarAssessmentID] = [Extent19].[ScholarAssessmentID]) AND ([Extent19].[AssessmentQuestionID] = [Project1].[ID])
        )) OR (CASE WHEN ( EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[a2_ScholarAssessmentAnswer] AS [Extent20]
            WHERE ([Extent1].[ScholarAssessmentID] = [Extent20].[ScholarAssessmentID]) AND ([Extent20].[AssessmentQuestionID] = [Project1].[ID])
        )) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[a2_ScholarAssessmentAnswer] AS [Extent21]
            WHERE ([Extent1].[ScholarAssessmentID] = [Extent21].[ScholarAssessmentID]) AND ([Extent21].[AssessmentQuestionID] = [Project1].[ID])
        )) THEN cast(0 as bit) END IS NULL)
    ))
)  AS [Project6]
ORDER BY [Project6].[ID] ASC, [Project6].[ID2] ASC, [Project6].[ID1] ASC, [Project6].[ID3] ASC, [Project6].[ID5] ASC, [Project6].[ID7] ASC, [Project6].[ID6] ASC, [Project6].[ID8] ASC, [Project6].[ID9] ASC, [Project6].[ID11] ASC, [Project6].[ID10] ASC, [Project6].[ID12] ASC, [Project6].[ID13] ASC, [Project6].[ID4] ASC, [Project6].[C6] ASC

I tried to include the execution plan, but that sent me over the 30,000 character limit.

The crazy thing is that I'm supplying AssessmentID = 1538, which is the most useful limiting information in the query, but the execution plan is all but ignoring this fact, and scanning almost every other joined table, only filtering down by AssessmentID kind of as an afterthought...

share|improve this question
1  
Could you post the query and the execution plan (XML)? –  Blaž Dakskobler Jun 1 at 11:31
    
@BlažDakskobler, I posted the query, but the execution plan xml is too big to include in the question –  Shaul Behr Jun 1 at 11:53
    
Can you dump it somewhere else and share the link? –  Blaž Dakskobler Jun 1 at 12:01
    
@BlažDakskobler here –  Shaul Behr Jun 1 at 12:07
    
ID is the clustered index for the a2_AssessmentQuestionID table? –  Blaž Dakskobler Jun 1 at 12:36

1 Answer 1

up vote 2 down vote accepted

The nested loop is happening at this part, where you're joining the a2_AssessmentQuestion to itself:

SELECT
   [Extent18].[ID] AS [ID]
FROM
   [dbo].[a2_AssessmentQuestion] AS [Extent17]
   INNER JOIN [dbo].[a2_AssessmentQuestion] AS [Extent18]
      ON [Extent17].[PartID] = [Extent18].[PartID]
WHERE
   [Extent1].[AssessmentQuestionID] = [Extent17].[ID]

The where condition is not applied at this part, as you can see from the whole query you posted.

Looking at the predicates and outputs of the nested loop and the clustered index scan, I suggest you try to create a nonclustered index on a2_AssessmentQuestion.PartID.

Nested loop details from the execution plan Clustered index scan details from the execution plan

share|improve this answer
    
I already have an index: CREATE UNIQUE NONCLUSTERED INDEX [IX_a2_AssessmentQuestion] ON [dbo].[a2_AssessmentQuestion] ([PartID] ASC, [Number] ASC) INCLUDE ([ID],[QuestionTypeID],[QuestionCategoryID],[SubjectCategoryID],[AssessmentTypeS‌​trategyID],[Label],[DifficultyLevel],[Structure],[GradingStyle],[Points],[Penalty‌​Points],[RubricWeight],[AllowPartialCredit],[IsDeleted],[Version]) WHERE ([IsDeleted]=(0)) ON [PRIMARY] –  Shaul Behr Jun 1 at 12:47
    
... but now that I look a little closer, I think I've seen this problem before... it's the Where IsDeleted=0 that's throwing the optimizer –  Shaul Behr Jun 1 at 12:48
    
I might be getting this wrong concerning the nested loop mechanics, but try the index I suggested nevertheless. The index you mention here is a filtered index and it's not getting picked up (you're not filtering in IsDeleted on the query). –  Blaž Dakskobler Jun 1 at 12:50
    
... and verified. I removed the Where and made the index non-unique, and suddenly the query executes in no time. I want to enforce uniqueness where IsDeleted is false, so I made a new unique index for that. Thank you for your help! –  Shaul Behr Jun 1 at 12:51
    
BTW, I have run into this problem before, even when I explicitly filter the query by the same where clause as the index: the optimizer simply ignores the index! –  Shaul Behr Jun 1 at 12:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.