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!
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…
Best Answer
The nested loop is happening at this part, where you're joining the
a2_AssessmentQuestionto itself: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.