SQL CASE: знать и избегать малоизвестных неприятностей
Пересказ статьи Edwin Sanchez. SQL CASE: Know and Avoid 3 Lesser-Known Hassles
Неприятности с CASE? Действительно?
Нет, пока вы не столкнетесь с 3 неприятными проблемами, которые могут вызвать ошибки во время выполнения и ухудшить производительность.
Если вы пролистаете подзаголовки, чтобы увидеть эти проблемы, я не буду вас винить. Читатели, к которым я также отношусь, нетерпеливы.
Я уверен, что вы уже знакомы с основами SQL CASE, поэтому я не буду мучит вас длинным введением. Давайте углубимся в понимание того, что происходит под капотом.
Выражения в SQL CASE по большей части оцениваются последовательно или слева направо. Хотя совсем другое дело, когда они используются с агрегатными функциями. Давайте рассмотрим пример:
Вышеприведенный код выглядит обычно. Если я спрошу вас, какой результат будет получен, вы, вероятно, ответите 1. Визуальная проверка скажет нам это, поскольку переменная @value установлена в 0. Если @value равна 0, то результат равен 1.
Но не в этом случае. Вот действительный результат, полученный в SQL Server Management Studio:
Но почему?
Когда условное выражение использует агрегатные функции типа MAX() в SQL CASE, они оцениваются в первую очередь. Таким образом, MAX(1/@value) вызывает ошибку деления на нуль, поскольку @value равна 0.
Ситуация становится еще более неприятной, когда скрыта. Я объясню это позже.
Ну и что?
Хороший вопрос. Действительно, здесь вообще нет никаких проблем, если вы используете литералы или простые выражения. Но если вы используете подзапросы в качестве условного выражения, вы сильно удивитесь.
Прежде, чем проверять пример ниже, лучше восстановить отсюда копию базы данных. Мы будем использовать её в последующих примерах.
Теперь рассмотрим следующий очень простой пример:
Очень простой, правда? Он возвращает 1 строку с одним столбцом данных. STATISTICS IO показывает минимальное число логических чтений.
Рис.1. Логические чтения таблицы SportsCars до использования запроса в качестве подзапроса в SQL CASE
Замечание для непосвященных. Чем больше логических чтений, тем медленнее запрос. О логических чтениях можно почитать здесь.
План выполнения тоже показывает простой процесс:
Рис.2. План выполнения для запроса к SportsCar до его использования как подзапроса в SQL CASE
Давайте теперь поместим этот запрос в выражение CASE:
Скрестите пальцы, поскольку сейчас логические чтения увеличатся в 4 раза.
Рис.3. Логические чтения после использования подзапроса в SQL CASE
Удивительно! По сравнению всего с двумя логическими чтениями на рис.1 мы получили в 4 раза больше. Таким образом, запрос стал в 4 раза медленнее. Как это могло произойти? Мы видим подзапрос только в одном месте.
Но это не конец истории. Посмотрите план выполнения:
Рис.4. План выполнения после использования простого запроса в качестве выражения подзапроса в SQL CASE
Мы видим 4 экземпляра операторов Top и Index Scan на рис.4. Если каждый Top и Index Scan потребляет 2 логических чтения, это объясняет, почему число логических чтений стало 8 на рис.3. И, поскольку каждый Top и Index Scan имеют 25% стоимости, это подтверждает сказанное.
Но это еще не все. Свойства оператора Compute Scalar показывают, как обрабатывается весь оператор.
Рис.5. Свойства Compute Scalar показывают 4 выражения CASE WHEN
Мы видим 3 выражения CASE WHEN в свойстве Defined Values оператора Compute Scalar. Это выглядит так, как будто простое выражение CASE стало поисковым выражением CASE типа:
Хорошее исправление? Давайте посмотрим логические чтения в STATISTICS IO:
Рис.6. Логические чтения после извлечения подзапроса из выражения CASE
Мы видим меньше логических чтений в модифицированном запросе. Извлечение подзапроса и присвоение результата переменной получается значительно лучше. Что насчет плана выполнения? Посмотрите ниже:
Рис.7. План выполнения после извлечения подзапроса из выражения CASE
Оператор Top и Index Scan появляются однажды, а не 4 раза. Замечательно!
На заметку: Не используйте подзапрос в качестве условия в операторе CASE. Если необходимо получить значение, поместите сначала результат подзапроса в переменную. Затем используйте эту переменную в выражении CASE.
Есть секрет, и SQL CASE имеет к нему отношение. Если вы не знаете, как ведут себя эти 3 функции, вы не будете знать, что совершаете ошибку, которую мы пытались избежать в пунктах №1 и №2 выше. Вот они:
Давайте рассмотрим их по очереди.
Я использовал Immediate IF, или IIF, в Visual Basic и Visual Basic for Applications. Это является также эквивалентом тернарного оператор в C#: <условие> ? <результат, если истинно> : <результат, если ложно>.
Эта функция принимает условие и возвращает 1 из 2 аргументов в зависимости от результатов условия. И эта функция также имеется в T-SQL.
Но это просто обертка более длинного выражения CASE. Откуда нам это известно? Давайте проверим пример.
Результатом этого запроса является 'No'. Однако проверьте план выполнения, а также свойства Compute Scalar.
Рис.8. IIF оказывается CASE WHEN в плане выполнения
Поскольку IIF является CASE WHEN, как вы думаете, что произойдет, если выполнить что-то подобное этому?
Будет получена ошибка деления на нуль, если @noOfPayments равен нулю. То же самое происходило в первом случае, рассмотренном ранее.
Вы можете спросить, что вызывает эту ошибку, поскольку результатом запроса является TRUE, и должно получиться 83333.33. Опять вернитесь к случаю 1.
Таким образом, если вы столкнулись с такой ошибкой при использовании IIF, виноват SQL CASE.
COALESCE - это также сокращенная форма выражения SQL CASE. Она оценивает список значений и возвращает первое не-NULL значение. Вот пример, который показывает, что подзапрос вычисляется дважды.
Давайте посмотрим план выполнения и свойство Defined Values оператора Compute Scalar.
Рис.9. COALESCE преобразуется в SQL CASE в плане выполнения
Разумеется SQL CASE. Нигде не упоминается COALESCE в окне Defined Values. Это доказывает тайный секрет этой функции.
Но это не все. Сколько раз вы увидели [Vehicles].[dbo].[Styles].[Style] в окне Defined Values? ДВАЖДЫ! Это согласуется с официальной документацией Microsoft. Представьте, что один из аргументов в COALESCE является подзапросом. Тогда получаем удвоение логических чтений и замедление выполнения.
Наконец, CHOOSE. Она подобна функции CHOOSE в MS Access. Она возвращает одно значение из списка значений на основе позиции индекса. Она также действует как индекс массива.
Давайте посмотрим, сможем ли мы получить трансформацию в SQL CASE в примере. Проверьте нижеприведенный код:
Это наш пример с CHOOSE. Теперь давайте посмотрим план выполнения и свойство Defined Values в операторе Compute Scalar:
Рис.10. Как видно в плане выполнения CHOOSE преобразуется в SQL CASE
Вы видите ключевое слово CHOOSE в окне Defined Values на рис.10? Как насчет CASE WHEN?
Подобно предыдущим примерам, эта функция CHOOSE есть просто оболочка для более длинного выражения CASE. И поскольку запрос имеет 2 пункта для CHOOSE, ключевые слова CASE WHEN появляются дважды. Смотрите в окне Defined Values красные прямоугольники.
Однако CASE WHEN появлялось более двух раз. Это происходит из-за выражения CASE во внутреннем запросе CTE. Если посмотреть внимательно, эта часть внутреннего запроса также появляется дважды.
Теперь, когда все секреты раскрыты, что мы узнали?
Надеюсь, что различие в поведении SQL CASE было полезным для вас.
1. SQL CASE не всегда оценивается последовательно
Выражения в SQL CASE по большей части оцениваются последовательно или слева направо. Хотя совсем другое дело, когда они используются с агрегатными функциями. Давайте рассмотрим пример:
-- сначала оценивается агрегатная функция и генерирует ошибку
DECLARE @value INT = 0;
SELECT CASE WHEN @value = 0 THEN 1 ELSE MAX(1/@value) END;
Вышеприведенный код выглядит обычно. Если я спрошу вас, какой результат будет получен, вы, вероятно, ответите 1. Визуальная проверка скажет нам это, поскольку переменная @value установлена в 0. Если @value равна 0, то результат равен 1.
Но не в этом случае. Вот действительный результат, полученный в SQL Server Management Studio:
Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.
Но почему?
Когда условное выражение использует агрегатные функции типа MAX() в SQL CASE, они оцениваются в первую очередь. Таким образом, MAX(1/@value) вызывает ошибку деления на нуль, поскольку @value равна 0.
Ситуация становится еще более неприятной, когда скрыта. Я объясню это позже.
2. Простое выражение SQL CASE оценивается многократно
Ну и что?
Хороший вопрос. Действительно, здесь вообще нет никаких проблем, если вы используете литералы или простые выражения. Но если вы используете подзапросы в качестве условного выражения, вы сильно удивитесь.
Прежде, чем проверять пример ниже, лучше восстановить отсюда копию базы данных. Мы будем использовать её в последующих примерах.
Теперь рассмотрим следующий очень простой пример:
SELECT TOP 1 manufacturerID FROM SportsCars
Очень простой, правда? Он возвращает 1 строку с одним столбцом данных. STATISTICS IO показывает минимальное число логических чтений.
Рис.1. Логические чтения таблицы SportsCars до использования запроса в качестве подзапроса в SQL CASE
Замечание для непосвященных. Чем больше логических чтений, тем медленнее запрос. О логических чтениях можно почитать здесь.
План выполнения тоже показывает простой процесс:
Рис.2. План выполнения для запроса к SportsCar до его использования как подзапроса в SQL CASE
Давайте теперь поместим этот запрос в выражение CASE:
-- Использование подзапроса в SQL CASE
DECLARE @manufacturer NVARCHAR(50)
SET @manufacturer = (CASE (SELECT TOP 1 manufacturerID FROM SportsCars)
WHEN 6 THEN 'Alfa Romeo'
WHEN 21 THEN 'Aston Martin'
WHEN 64 THEN 'Ferrari'
WHEN 108 THEN 'McLaren'
ELSE 'Others'
END)
SELECT @manufacturer;
Анализ
Скрестите пальцы, поскольку сейчас логические чтения увеличатся в 4 раза.
Рис.3. Логические чтения после использования подзапроса в SQL CASE
Удивительно! По сравнению всего с двумя логическими чтениями на рис.1 мы получили в 4 раза больше. Таким образом, запрос стал в 4 раза медленнее. Как это могло произойти? Мы видим подзапрос только в одном месте.
Но это не конец истории. Посмотрите план выполнения:
Рис.4. План выполнения после использования простого запроса в качестве выражения подзапроса в SQL CASE
Мы видим 4 экземпляра операторов Top и Index Scan на рис.4. Если каждый Top и Index Scan потребляет 2 логических чтения, это объясняет, почему число логических чтений стало 8 на рис.3. И, поскольку каждый Top и Index Scan имеют 25% стоимости, это подтверждает сказанное.
Но это еще не все. Свойства оператора Compute Scalar показывают, как обрабатывается весь оператор.
Рис.5. Свойства Compute Scalar показывают 4 выражения CASE WHEN
Мы видим 3 выражения CASE WHEN в свойстве Defined Values оператора Compute Scalar. Это выглядит так, как будто простое выражение CASE стало поисковым выражением CASE типа:
DECLARE @manufacturer NVARCHAR(50)
SET @manufacturer = (CASE
WHEN (SELECT TOP 1 manufacturerID FROM SportsCars) = 6 THEN 'Alfa Romeo'
WHEN (SELECT TOP 1 manufacturerID FROM SportsCars) = 21 THEN 'Aston Martin'
WHEN (SELECT TOP 1 manufacturerID FROM SportsCars) = 64 THEN 'Ferrari'
WHEN (SELECT TOP 1 manufacturerID FROM SportsCars) = 108 THEN 'McLaren'
ELSE 'Others'
END)
SELECT @manufacturer;
Хорошее исправление? Давайте посмотрим логические чтения в STATISTICS IO:
Рис.6. Логические чтения после извлечения подзапроса из выражения CASE
Мы видим меньше логических чтений в модифицированном запросе. Извлечение подзапроса и присвоение результата переменной получается значительно лучше. Что насчет плана выполнения? Посмотрите ниже:
Рис.7. План выполнения после извлечения подзапроса из выражения CASE
Оператор Top и Index Scan появляются однажды, а не 4 раза. Замечательно!
На заметку: Не используйте подзапрос в качестве условия в операторе CASE. Если необходимо получить значение, поместите сначала результат подзапроса в переменную. Затем используйте эту переменную в выражении CASE.
Эти три встроенные функции тайно преобразуются в SQL CASE
Есть секрет, и SQL CASE имеет к нему отношение. Если вы не знаете, как ведут себя эти 3 функции, вы не будете знать, что совершаете ошибку, которую мы пытались избежать в пунктах №1 и №2 выше. Вот они:
Давайте рассмотрим их по очереди.
IIF
Я использовал Immediate IF, или IIF, в Visual Basic и Visual Basic for Applications. Это является также эквивалентом тернарного оператор в C#: <условие> ? <результат, если истинно> : <результат, если ложно>.
Эта функция принимает условие и возвращает 1 из 2 аргументов в зависимости от результатов условия. И эта функция также имеется в T-SQL.
Но это просто обертка более длинного выражения CASE. Откуда нам это известно? Давайте проверим пример.
SELECT IIF((SELECT Model FROM SportsCars WHERE SportsCarID = 1276) =
'McLaren Senna', 'Yes', 'No');
Результатом этого запроса является 'No'. Однако проверьте план выполнения, а также свойства Compute Scalar.
Рис.8. IIF оказывается CASE WHEN в плане выполнения
Поскольку IIF является CASE WHEN, как вы думаете, что произойдет, если выполнить что-то подобное этому?
DECLARE @averageCost MONEY = 1000000.00;
DECLARE @noOfPayments TINYINT = 0; -- умышленно вызвать ошибку
SELECT IIF((SELECT Model FROM SportsCars WHERE SportsCarID = 1276) = 'SF90 Spider', 83333.33,MIN(@averageCost / @noOfPayments));
Будет получена ошибка деления на нуль, если @noOfPayments равен нулю. То же самое происходило в первом случае, рассмотренном ранее.
Вы можете спросить, что вызывает эту ошибку, поскольку результатом запроса является TRUE, и должно получиться 83333.33. Опять вернитесь к случаю 1.
Таким образом, если вы столкнулись с такой ошибкой при использовании IIF, виноват SQL CASE.
COALESCE
COALESCE - это также сокращенная форма выражения SQL CASE. Она оценивает список значений и возвращает первое не-NULL значение. Вот пример, который показывает, что подзапрос вычисляется дважды.
SELECT
COALESCE(m.Manufacturer + ' ','') + sc.Model AS Car
FROM SportsCars sc
LEFT JOIN Manufacturers m ON sc.ManufacturerID = m.ManufacturerID
Давайте посмотрим план выполнения и свойство Defined Values оператора Compute Scalar.
Рис.9. COALESCE преобразуется в SQL CASE в плане выполнения
Разумеется SQL CASE. Нигде не упоминается COALESCE в окне Defined Values. Это доказывает тайный секрет этой функции.
Но это не все. Сколько раз вы увидели [Vehicles].[dbo].[Styles].[Style] в окне Defined Values? ДВАЖДЫ! Это согласуется с официальной документацией Microsoft. Представьте, что один из аргументов в COALESCE является подзапросом. Тогда получаем удвоение логических чтений и замедление выполнения.
CHOOSE
Наконец, CHOOSE. Она подобна функции CHOOSE в MS Access. Она возвращает одно значение из списка значений на основе позиции индекса. Она также действует как индекс массива.
Давайте посмотрим, сможем ли мы получить трансформацию в SQL CASE в примере. Проверьте нижеприведенный код:
;WITH McLarenCars AS
(
SELECT
CASE
WHEN sc.Model IN ('Artura','Speedtail','P1/ P1 GTR','P1 LM') THEN '1'
ELSE '2'
END AS [type]
,sc.Model
,s.Style
FROM SportsCars sc
INNER JOIN Styles s ON sc.StyleID = s.StyleID
WHERE sc.ManufacturerID = 108
)
SELECT
Model
,Style
,CHOOSE([Type],'Hybrid','Gasoline') AS [type]
FROM McLarenCars
Это наш пример с CHOOSE. Теперь давайте посмотрим план выполнения и свойство Defined Values в операторе Compute Scalar:
Рис.10. Как видно в плане выполнения CHOOSE преобразуется в SQL CASE
Вы видите ключевое слово CHOOSE в окне Defined Values на рис.10? Как насчет CASE WHEN?
Подобно предыдущим примерам, эта функция CHOOSE есть просто оболочка для более длинного выражения CASE. И поскольку запрос имеет 2 пункта для CHOOSE, ключевые слова CASE WHEN появляются дважды. Смотрите в окне Defined Values красные прямоугольники.
Однако CASE WHEN появлялось более двух раз. Это происходит из-за выражения CASE во внутреннем запросе CTE. Если посмотреть внимательно, эта часть внутреннего запроса также появляется дважды.
На заметку
Теперь, когда все секреты раскрыты, что мы узнали?
- SQL CASE ведет себя иначе, когда используются агрегатные функции. Будьте внимательны при передаче аргументов в агрегатные функции типа MIN, MAX или COUNT.
- Простое выражение CASE будет оцениваться несколько раз. Имейте это в виду и избегайте передачи подзапроса. Хотя это синтаксически корректно, это повредит производительности.
- IIF, CHOOSE и COALESCE имеют грязные секреты. Помните это, передавая значения в эти функции. Это вызовет преобразование к SQL CASE. В зависимости от значений вы можете получить ошибку или ухудшить производительность.
Надеюсь, что различие в поведении SQL CASE было полезным для вас.
Обратные ссылки
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой