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...
IDis the clustered index for thea2_AssessmentQuestionIDtable?