T-SQL, repeated same scalar subquery performance in views-CodePudding

Below is a simple query that retrieves Students and their exam results. The same student can take the same exam multiple times. The subqueries retrieve the latest exam results for each student. As you can see, the Line X (which retrieves the latest Exam ID) is exactly the same in every subquery for each row. How to store or cache the result of Line X to prevent three times execution for each row? I cannot use stored procedure or functions for this task, it has to be a VIEW for additional filtering.

SELECT S.*,
(
  SELECT COUNT(*) FROM ExamAnswers WHERE 
  IsCorrectAnswer IS NOT NULL AND
  IsCorrectAnswer = 1 AND 
  ExamID = 
  (SELECT TOP(1) ID FROM Exams E WHERE E.StudentID = S.ID ORDER BY ID DESC) --Line X
) CorrectAnswerCount,
(
  SELECT COUNT(*) FROM ExamAnswers EA WHERE 
  EA.IsCorrectAnswer IS NOT NULL AND
  EA.IsCorrectAnswer = 0 AND 
  EA.ExamID = 
  (SELECT TOP(1) ID FROM Exams E WHERE E.StudentID = S.ID ORDER BY ID DESC) --Line X
) WrongAnswerCount,
(
  SELECT COUNT(*) FROM ExamAnswers WHERE 
  IsCorrectAnswer IS NULL AND
  ExamID = 
  (SELECT TOP(1) ID FROM Exams E WHERE E.StudentID = S.ID ORDER BY ID DESC) --Line X
) UnansweredQuestionCount

FROM Students S

CodePudding user response：

You can do it like this

SELECT S.*,
       CA.*
FROM   Students S
       CROSS APPLY (SELECT SUM(CASE WHEN IsCorrectAnswer = 1 THEN 1 ELSE 0 END) AS CorrectAnswerCount,
                           SUM(CASE WHEN IsCorrectAnswer = 0 THEN 1 ELSE 0 END) AS WrongAnswerCount,
                           SUM(CASE WHEN IsCorrectAnswer IS NULL THEN 1 ELSE 0 END) AS UnansweredQuestionCount
                    FROM   ExamAnswers EA
                    WHERE  EA.ExamID = (SELECT TOP(1) ID
                                        FROM   Exams E
                                        WHERE  E.StudentID = S.ID
                                        ORDER  BY ID DESC)) CA

CodePudding user response：

What about this approach :

WITH
T AS
(
SELECT Student_id,
       SUM(CASE IsCorrectAnswer WHEN 1 THEN 1 END) AS COUNT_TRUE,
       SUM(CASE IsCorrectAnswer WHEN 0 THEN 1 END) AS COUNT_FALSE,
       SUM(CASE WHEN IsCorrectAnswer IS NULL THEN 1 END) AS COUNT_UNKNOWN
FROM   ExamAnswers AS EA 
WHERE  EA.ExamID = (SELECT MAX(ID) 
                    FROM   Exams E 
                    WHERE  E.StudentID = S.ID)
GROUP  BY Student_id
)
SELECT S.*, COUNT_TRUE, COUNT_FALSE, COUNT_UNKNOWN
FROM   Students AS S
       JOIN T ON S.ID = T.Student_id