The following query is taking over 800ms to run, and returning 300 rows. When deployed to SQL Azure, it takes much longer on an affordable price tier.
SELECT Tests.Id, Tests.Title, Tests.AuthorId, Tests.[Status], Users.FirstName + ' ' + Users.LastName AS AuthorName, (SELECT COUNT(1) FROM Results LEFT JOIN Users ON Results.UserId = Users.Id WHERE Results.TestId = Tests.Id AND Results.MarkedBy IS NULL AND Results.QuestionNumber >= 1 AND EXISTS ( (SELECT ClassName FROM UserClasses WHERE UserClasses.UserId = Users.Id) INTERSECT (SELECT ClassName FROM TestClasses WHERE TestClasses.TestId = Tests.Id) INTERSECT (SELECT ClassName FROM UserClasses WHERE UserId = @teacherId) ) ) AS UnmarkedCount, (CASE WHEN EXISTS (SELECT 1 FROM Results WHERE Results.TestId = Tests.Id) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END ) AS AnyResults, (SELECT Stuff((SELECT ',' + ClassName FROM ( (SELECT ClassName FROM TestClasses WHERE TestClasses.TestId = Tests.Id) INTERSECT (SELECT ClassName FROM UserClasses WHERE UserId = @teacherId) ) x FOR XML PATH ('')),1,1,'') ) AS Classes FROM Tests INNER JOIN Users ON Tests.AuthorId = Users.Id WHERE Users.SchoolId = @schoolId AND Tests.Status <= 4
An overview of the schema:
Usersinclude students and teachers.
UserClassesmatches many users to many class names.
TestClassesmatches many tests to many class names.
- Each test in
Testscan have multiple
Results- one per question per student.
The query returns a list of tests, using subqueries to find:
UnmarkedCount: How many unmarked results exist for this test, where the intersection of the following is not empty:
- The classes of the student of this result
- The test's classes
- The teacher's classes
AnyResults: Are there any results for this test?
Classes: As a comma-separated list, which of the teacher's classes are assigned to this test?
Note that if we remove the condition where three queries are intersected, the execution time is reduced to 150ms. However, this logic is required.
How could this be improved?
Query Execution Plan
This is an extract from the query execution plan, where the heavy lifting seems to occur. I can't see anywhere suggesting indexes.
The procedure returns a list of all tests at a given school. For each test, it calculates:
- UnmarkedCount: How many results are not yet marked for students in classes which are both allocated to this test and taught by the current user?
- Classes: Which of the classes allocated to this test does the current user teach?