Почему плохо использовать выражения CASE в предложении WHERE
Пересказ статьи Erik Darling. Why CASE Expressions Are Bad In The Where Clause
Выключить и включить
Я провел много времени, исправляя подобные запросы, поэтому подумал, что должен написать об этом в надежде, что смогу помочь кому-то сэкономить время и нервы.
Очевидно, что это также распространяется и на предложения соединения. Выражение CASE там имеет столько же шансов вызвать проблемы производительности.
Этот шаблон всплывает в некоторых запросах ORM, которые я также наблюдал, но я не смог выяснить код, который его вызывал.
Давайте посмотрим, почему это вызывает проблемы!
Чтобы дать нашему запросу наилучшие шансы, давайте создадим некоторые индексы.
И когда это сделано, давайте рассмотрим пример простого запроса.
План обнаруживает типичные учебные симптомы потери возможности использовать индекс: сканирование индекса с предикатом, несмотря на наличие совершенно поискового индекса:
Позор! Но мы можем сделать еще хуже.
Если мы включим новый столбец в выражение CASE, теперь из таблицы Users, предикат будет применяться в реально неудачном месте плана запроса.
Теперь вся фильтрация происходит в соединении, и запрос, который выполнялся примерно 1 секунду, теперь занимает 5 секунд.
Если вы пишете подобные запросы, вы накликаете беду.
Если мы выразим эту логику без выражения CASE, производительность станет много лучше. Шокирует, правда?
Этот запрос занимает около 240 миллисекунд, что является существенным улучшением.
Теперь мы можем выполнять поиск в нашем суперполезном индексе на таблице Posts. Теперь я знаю, о чем вы думаете - мы попадаем в кластерный индекс таблицы Users - это должно быть ужасно.
Но нет; поскольку Nested Loops Join является одним из вариантов Apply, имеет больше смысла использовать его для поиска единственного id, и оценивать предикат по Reputation, во-вторых.
Тракторный прицеп
Чтобы дать нашему запросу наилучшие шансы, давайте создадим некоторые индексы.
CREATE NONCLUSTERED INDEX p
ON dbo.Posts
(
PostTypeId,
Score,
OwnerUserId
);
CREATE NONCLUSTERED INDEX u
ON dbo.Users
(
Reputation
)
INCLUDE
(
DisplayName
);И когда это сделано, давайте рассмотрим пример простого запроса.
SELECT
u.Id,
u.DisplayName,
s = SUM(p.Score)
FROM dbo.Posts AS p
JOIN dbo.Users AS u
ON p.OwnerUserId = u.Id
WHERE 1 =
CASE
WHEN p.PostTypeId = 1
AND p.Score >= 10
THEN 1
ELSE 0
END
GROUP BY
u.Id,
u.DisplayName
HAVING SUM(p.Score) > 10000
ORDER BY s DESC;План обнаруживает типичные учебные симптомы потери возможности использовать индекс: сканирование индекса с предикатом, несмотря на наличие совершенно поискового индекса:
Позор! Но мы можем сделать еще хуже.
Хуже
Если мы включим новый столбец в выражение CASE, теперь из таблицы Users, предикат будет применяться в реально неудачном месте плана запроса.
SELECT
u.Id,
u.DisplayName,
s = SUM(p.Score)
FROM dbo.Posts AS p
JOIN dbo.Users AS u
ON p.OwnerUserId = u.Id
WHERE 1 =
CASE
WHEN p.PostTypeId = 1
AND p.Score >= 10
AND u.Reputation > 5000
THEN 1
ELSE 0
END
GROUP BY
u.Id,
u.DisplayName
HAVING SUM(p.Score) > 10000
ORDER BY s DESC;Теперь вся фильтрация происходит в соединении, и запрос, который выполнялся примерно 1 секунду, теперь занимает 5 секунд.
Если вы пишете подобные запросы, вы накликаете беду.
Почему бы не быть нормальным?
Если мы выразим эту логику без выражения CASE, производительность станет много лучше. Шокирует, правда?
SELECT
u.Id,
u.DisplayName,
s = SUM(p.Score)
FROM dbo.Posts AS p
JOIN dbo.Users AS u
ON p.OwnerUserId = u.Id
WHERE p.PostTypeId = 1
AND p.Score >= 10
AND u.Reputation > 5000
GROUP BY
u.Id,
u.DisplayName
HAVING SUM(p.Score) > 10000
ORDER BY s DESC;Этот запрос занимает около 240 миллисекунд, что является существенным улучшением.
Теперь мы можем выполнять поиск в нашем суперполезном индексе на таблице Posts. Теперь я знаю, о чем вы думаете - мы попадаем в кластерный индекс таблицы Users - это должно быть ужасно.
Но нет; поскольку Nested Loops Join является одним из вариантов Apply, имеет больше смысла использовать его для поиска единственного id, и оценивать предикат по Reputation, во-вторых.
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой