T-SQL视图中重复标量子查询的性能优化：如何缓存重复执行的子查询结果

Great question—those repeated correlated subqueries are definitely a performance killer, since they run once per row per column they're used in. Since you're limited to views, you can use either a Common Table Expression (CTE) or OUTER APPLY to calculate each student's latest exam ID just once, then reuse that result for all your statistics. Here's how to implement both approaches:

Method 1: Using a CTE to Precompute Latest Exams #

First, we'll create a CTE that gets the most recent exam ID for each student. This runs once upfront, not per row/column:

CREATE VIEW StudentLatestExamStats AS
WITH LatestStudentExams AS (
    SELECT 
        E.StudentID,
        E.ID AS LatestExamID
    FROM (
        SELECT 
            StudentID,
            ID,
            -- Assign row number per student, ordered by exam ID descending (newest first)
            ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY ID DESC) AS RowNum
        FROM Exams
    ) E
    WHERE E.RowNum = 1 -- Keep only the latest exam per student
)
SELECT 
    S.*,
    -- Use ISNULL to handle students with no exams (returns 0 instead of NULL)
    ISNULL(EA.CorrectAnswerCount, 0) AS CorrectAnswerCount,
    ISNULL(EA.WrongAnswerCount, 0) AS WrongAnswerCount,
    ISNULL(EA.UnansweredQuestionCount, 0) AS UnansweredQuestionCount
FROM Students S
-- Left join to retain students who haven't taken any exams
LEFT JOIN LatestStudentExams LSE ON LSE.StudentID = S.ID
-- Join pre-aggregated exam answer stats to avoid per-row calculations
LEFT JOIN (
    SELECT 
        ExamID,
        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
    GROUP BY ExamID
) EA ON EA.ExamID = LSE.LatestExamID;

Method 2: Using OUTER APPLY for Row-Level Reuse #

If you prefer a more concise approach, OUTER APPLY lets you fetch the latest exam ID once per student, then immediately use that ID to calculate the stats in a second apply:

CREATE VIEW StudentLatestExamStats AS
SELECT 
    S.*,
    ISNULL(EA.CorrectAnswerCount, 0) AS CorrectAnswerCount,
    ISNULL(EA.WrongAnswerCount, 0) AS WrongAnswerCount,
    ISNULL(EA.UnansweredQuestionCount, 0) AS UnansweredQuestionCount
FROM Students S
-- Get the latest exam for each student (runs once per student, not 3x)
OUTER APPLY (
    SELECT TOP(1) ID AS LatestExamID
    FROM Exams E
    WHERE E.StudentID = S.ID
    ORDER BY E.ID DESC
) LSE
-- Calculate stats using the pre-fetched latest exam ID
OUTER 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 = LSE.LatestExamID
) EA;

Why This Works #

• In your original query, the subquery marked "Line X" is a correlated subquery—it runs once for every row in Students, three times per row (once for each count column).
• With both methods above, we fetch the latest exam ID once per student (either upfront in the CTE, or once per row via APPLY), then reuse that ID to compute all three statistics in a single pass (or via a pre-aggregated set of answers).
• Using LEFT JOIN/OUTER APPLY ensures we don't lose students who haven't taken any exams (their stats will show 0 instead of NULL, thanks to ISNULL).

Performance Tips #

• Make sure you have indexes on Exams(StudentID, ID DESC)—this will speed up fetching the latest exam per student.
• Add an index on ExamAnswers(ExamID, IsCorrectAnswer) to optimize the aggregated stats calculation.

内容的提问来源于stack exchange，提问作者Tamerlan Rustambayli