ISNULL, COALESCE и производительность
Пересказ статьи Erik Darling. ISNULL, COALESCE, And Performance
ANSI Бландарт
Иногда имеются веские основания использовать либо COALESCE, либо ISNULL в силу того, что они имеют разные возможности, поведение и поддержку в разных базах данных.
Однако isnull имеет несколько особых возможностей, представляющихся интересными, несмотря на её ограничения: только два аргумента, специфичен для SQL Server и, эх... мы не всегда можем указать три причины, как однажды сказал один мудрый человек.
Одна вещь делает isnull интересной в определенных сценариях. Давайте рассмотрим пару.
Во-первых, нам потребуется индекс.
Вот несколько запросов, для которых он понадобится.
Первый запрос использует ISNULL, а второй - COALESCE.
Знаю я, знаю - я много раз здесь говорил, чтобы вы не использовали ISNULL в предложении WHERE.
Обычно, когда вы оборачиваете столбец функцией, как в данном случае, случаются плохие вещи. Поиск превращается в сканирование, вино - в воду, проблемы - в табуляторы, а лицо, которое вы запомнили с прошлого раза, превращается в ноябрьского Джека О’Лантерна.
Но в данном случае, завернутый в нашем предложении WHERE столбец, который является первый столбцом в индексе, не допускает NULL-значений.
Оптимизатор SQL Server, имея это в виду, производит план с поиском в индексе.
Проверка на NULL отбрасывается, и все заканчивается поиском значений CreationDate, о чем мы беспокоились, и остаточным предикатом на VoteTypeId.
Красота
Второй запрос, который использует coalesce, имеет несколько отличий. Давайте посмотрим план.
Вместо 157 мс этот запрос выполняется дольше одной минуты на пять секунд. Все время тратится на Top > Index Scan. У нас больше нет поиска по индексу.
Отметим, что предикат на CreationDate является полным выражением CASE, проверяющим на NULL. Это мог быть хороший сценарий, если бы мы имели поиск чего-то, однако без надлежащего индексирования и написания запросов это беда.
Причина изменения запроса заключается в том, что оптимизатор решил, что применение Row Goal будет лучше. Вот почему мы имеем Nested Loops Join и Top > Index Scan. Это не очень хорошо работает.
Это не единственный случай, когда это происходит, но, вероятно, худший.
Вы можете наблюдать это в шаблоне, против которого я часто выступаю, использование Left Join для нахождения несуществующих строк:
Здесь не так плохо, но все равно заметно.
План с ISNULL выглядит примерно так:
Быстро
При 163 миллисекундах тут не на что жаловаться.
Версия с COALESCE значительно хуже, всего за 1,5 секунды.
Задыхается!
В SQL Server использование функций в предложении WHERE обычно находится в черном списке. В небольшом числе случаев использование встроенной функции isnull приводит к лучшей производительности по сравнению с coalesce для столбцов, не допускающих NULL-значений.
Конечно, этого шаблона обычно следует избегать. На столбцах с NULL это всегда будет плохо. Конечно, это имеет наибольшее значение, когда функция приводит к невозможному поиску в индексе, возможному в других случаях, и мы можем использовать только сканирование индекса для поиска строк.
Еще одно соображение - это когда мы можем сначала обратиться к очень ограниченному набору строк. Скажем, мы можем сократить количество (только для объяснения) примерно до 1000 строк, например, таким предикатом Score> 10000.
Для оставшихся 1000 строк маловероятно, что дополнительный предикат, подобные которому мы здесь видели, драматически сказался бы на времени выполнения относительно простого запроса.
Однако они могут привести к плохим оценкам числа строк для более сложных запросов.
Одна вещь делает isnull интересной в определенных сценариях. Давайте рассмотрим пару.
Первый сценарий
Во-первых, нам потребуется индекс.
CREATE INDEX party
ON dbo.Votes
(CreationDate, VoteTypeId)
INCLUDE
(UserId);Вот несколько запросов, для которых он понадобится.
SELECT TOP (10)
u.DisplayName
FROM dbo.Users AS u
WHERE NOT EXISTS
(
SELECT
1/0
FROM dbo.Votes AS v
WHERE v.UserId = u.Id
AND v.VoteTypeId IN (1, 2, 3)
AND ISNULL(v.CreationDate, '19000101') > '20131201'
)
ORDER BY u.CreationDate DESC;
SELECT TOP (10)
u.DisplayName
FROM dbo.Users AS u
WHERE NOT EXISTS
(
SELECT
1/0
FROM dbo.Votes AS v
WHERE v.UserId = u.Id
AND v.VoteTypeId IN (1, 2, 3)
AND COALESCE(v.CreationDate, '19000101') > '20131201'
)
ORDER BY u.CreationDate DESC;Первый запрос использует ISNULL, а второй - COALESCE.
Знаю я, знаю - я много раз здесь говорил, чтобы вы не использовали ISNULL в предложении WHERE.
Обычно, когда вы оборачиваете столбец функцией, как в данном случае, случаются плохие вещи. Поиск превращается в сканирование, вино - в воду, проблемы - в табуляторы, а лицо, которое вы запомнили с прошлого раза, превращается в ноябрьского Джека О’Лантерна.
Но в данном случае, завернутый в нашем предложении WHERE столбец, который является первый столбцом в индексе, не допускает NULL-значений.
Оптимизатор SQL Server, имея это в виду, производит план с поиском в индексе.
Проверка на NULL отбрасывается, и все заканчивается поиском значений CreationDate, о чем мы беспокоились, и остаточным предикатом на VoteTypeId.
Красота
Второй запрос, который использует coalesce, имеет несколько отличий. Давайте посмотрим план.
Вместо 157 мс этот запрос выполняется дольше одной минуты на пять секунд. Все время тратится на Top > Index Scan. У нас больше нет поиска по индексу.
Отметим, что предикат на CreationDate является полным выражением CASE, проверяющим на NULL. Это мог быть хороший сценарий, если бы мы имели поиск чего-то, однако без надлежащего индексирования и написания запросов это беда.
Причина изменения запроса заключается в том, что оптимизатор решил, что применение Row Goal будет лучше. Вот почему мы имеем Nested Loops Join и Top > Index Scan. Это не очень хорошо работает.
Это не единственный случай, когда это происходит, но, вероятно, худший.
Второй сценарий
Вы можете наблюдать это в шаблоне, против которого я часто выступаю, использование Left Join для нахождения несуществующих строк:
SELECT TOP (10)
u.DisplayName
FROM dbo.Users AS u
LEFT JOIN dbo.Votes AS v
ON v.UserId = u.Id
AND v.VoteTypeId IN (1, 2, 3)
AND ISNULL(v.CreationDate, '19000101') > '20131201'
WHERE v.Id IS NULL
ORDER BY u.CreationDate DESC;
SELECT TOP (10)
u.DisplayName
FROM dbo.Users AS u
LEFT JOIN dbo.Votes AS v
ON v.UserId = u.Id
AND v.VoteTypeId IN (1, 2, 3)
AND COALESCE(v.CreationDate, '19000101') > '20131201'
WHERE v.Id IS NULL
ORDER BY u.CreationDate DESC;Здесь не так плохо, но все равно заметно.
План с ISNULL выглядит примерно так:
Быстро
При 163 миллисекундах тут не на что жаловаться.
Версия с COALESCE значительно хуже, всего за 1,5 секунды.
Задыхается!
Что мы узнали
В SQL Server использование функций в предложении WHERE обычно находится в черном списке. В небольшом числе случаев использование встроенной функции isnull приводит к лучшей производительности по сравнению с coalesce для столбцов, не допускающих NULL-значений.
Конечно, этого шаблона обычно следует избегать. На столбцах с NULL это всегда будет плохо. Конечно, это имеет наибольшее значение, когда функция приводит к невозможному поиску в индексе, возможному в других случаях, и мы можем использовать только сканирование индекса для поиска строк.
Еще одно соображение - это когда мы можем сначала обратиться к очень ограниченному набору строк. Скажем, мы можем сократить количество (только для объяснения) примерно до 1000 строк, например, таким предикатом Score> 10000.
Для оставшихся 1000 строк маловероятно, что дополнительный предикат, подобные которому мы здесь видели, драматически сказался бы на времени выполнения относительно простого запроса.
Однако они могут привести к плохим оценкам числа строк для более сложных запросов.
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой