SQL-Ex blog

Я уверен, что вы уже знакомы с основами 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


• COALESCE


• CHOOSE

Давайте рассмотрим их по очереди.

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. Если посмотреть внимательно, эта часть внутреннего запроса также появляется дважды.

На заметку

Теперь, когда все секреты раскрыты, что мы узнали?

1. SQL CASE ведет себя иначе, когда используются агрегатные функции. Будьте внимательны при передаче аргументов в агрегатные функции типа MIN, MAX или COUNT.


2. Простое выражение CASE будет оцениваться несколько раз. Имейте это в виду и избегайте передачи подзапроса. Хотя это синтаксически корректно, это повредит производительности.


3. IIF, CHOOSE и COALESCE имеют грязные секреты. Помните это, передавая значения в эти функции. Это вызовет преобразование к SQL CASE. В зависимости от значений вы можете получить ошибку или ухудшить производительность.

Надеюсь, что различие в поведении SQL CASE было полезным для вас.