T-SQL, повторяет ту же производительность скалярного подзапроса в представлениях

ProgramBox

T-SQL, повторяет ту же производительность скалярного подзапроса в представлениях

Post author:admin
Запись опубликована:9 января, 2022
Post category:Вопросы по программированию

#sql-server #performance #tsql #subquery

Вопрос:

Ниже приведен простой запрос, который извлекает студентов и их результаты экзаменов. Один и тот же студент может сдавать один и тот же экзамен несколько раз. Подзапросы извлекают последние результаты экзамена для каждого студента. Как вы можете видеть, строка X (которая извлекает последний идентификатор экзамена) точно такая же в каждом подзапросе для каждой строки. Как сохранить или кэшировать результат строки X, чтобы предотвратить трехкратное выполнение для каждой строки? Я не могу использовать хранимую процедуру или функции для этой задачи, это должно быть ПРЕДСТАВЛЕНИЕ для дополнительной фильтрации.

 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

1. Вы могли бы, конечно, создать функцию, но она все равно будет выполняться 3 раза, так что это было бы только для ясности и СУХОГО рассмотрения, я сомневаюсь, что эти усилия будут стоить вашего времени, но мне любопытно посмотреть, знаком ли кто-нибудь с тем, как вы действительно можете это сделать, в чем я сильно сомневаюсь 🙂

2. IsCorrectAnswer IS NOT NULL AND IsCorrectAnswer = 1 должно быть просто написано IsCorrectAnswer = 1 — если оно соответствует тому, что гарантированно соответствует IS NOT NULL

3. Примечание: То, что подзапрос появляется в запросе три раза, не обязательно означает, что он выполняется три раза. Обычно оптимизатор должен это заметить, и результат будет использован повторно (если он не найдет совершенно другого способа). Помните, что SQL является декларативным, а не императивным. Если вы сомневаетесь, проверьте план.

4. @stickybit — Оптимизатор SQL Server практически никогда (если вообще когда-либо) не выполняет рефакторинг выражений общих подзапросов для объединения аналогичных подзапросов. Это имеет место, даже если они появляются только один раз в тексте запроса, например, в COALESCE((sub_query), 1) котором sub_query в плане выполнения будет два экземпляра дерева

Ответ №1:

Ты можешь сделать это вот так

 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

1. Я думаю, следует использовать OUTER APPLY . Если только OP не подтвердит, что у всех студентов будут строки в ответах на экзамен

2. @Белка — нет. Это скалярная совокупность, поэтому всегда возвращает одну строку, даже если таблица была пустой. Смотрите Забаву со скалярными и векторными агрегатами

3. Ты прав. Не заметил совокупности

Ответ №2:

Как насчет такого подхода :

 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

1. Это совсем не имеет той же семантики, что и исходный запрос. Для каждого студента необходимо найти соответствующий экзамен для этого студента с наивысшим идентификатором и возвращаемыми значениями ExamAnswers для этого экзамена в той же строке, что и информация о студенте. Ваш запрос не делает ничего даже отдаленно похожего

2. Извините, я исправляю это

3. Это все равно не сработает — у начального CTE есть WHERE E.StudentID = S.ID , но там нет такого псевдонима, как S в области видимости

4. Со всеми столбцами, имеющими одинаковое имя ID, ID, ID…. очень трудно ответить на ваш запрос. Пожалуйста, исправьте свой пост с определением DDL ваших таблиц…

Метки: T-SQL, повторяет ту же производительность скалярного подзапроса в представлениях