Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

T-SQL, repeated same scalar subquery performance in views

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

>Solution :

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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 
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading