Сегодня оптимизировал запрос, который до моего вмешательства работал 4 часа. Проблема была в том, что в нем не правильно использовалась функция isnull. Это очень удобная функция, особенно, если использовать ее в блоке SELECT, но ее нужно аккуратно использовать в блоке WHERE.
Я оптимизировал функцию, которая получала в качестве параметра необходимый тип данных. Если переменная равна NULL, то функция должна вернуть все данные.
Чтобы проще было следить за тем, что я говорю, давайте представим себе следующий запрос:
declare @city varchar(100); set @city = null; select FirstName, LastName, City from Members where isnull(@city, City) = City
Я упростил функцию для простого запроса, чтобы проще было разбирать его. Если переменная @city равна null, то isnul(@city, City) вернет значение City для текущей строки и оно конечно же будет равно City, а значит запрос вернет содержимое всей базы. Если City установлен в какой-то город, то запрос вернет только этот город. Красиво? Да, но очень медленно.
Как показал мой пример, функция isnul не дает SQL серверу эффективно использовать индексы в этом месте. Сервер практически скаровал таблицу, а у меня были не люди в таблицы, а транзакции. Моя таблица транзакций состоит из миллиардов записей со сложными связями на другие таблицы, в том числе и на себя и запрос Server уходил в серьезные 4-х часовые раздумия. Никакие индексы не помогали.
Не используйте такую конструкцию на больших таблицах. Лучше написать:
declare @city varchar(100); set @city = null; select FirstName, LastName, City from Members where (@city = City or @city is null)
Если City не равен null, то будет так же возвращен только этот город, иначе вся таблица. Но такое банальное изменение позволило запросу, который я оптимизировал, выполняться меньше 30 минут. Подобные прикола как isnull(@city, City) = City удобны и будут работать быстро в секции SELECT, но не в WHERE. По крайней мере по моим наблюдениям.
Понравилось? Кликни Лайк, чтобы я знал, какой контент более интересен читателям. Заметку пока еще никто не лайкал и ты можешь быть первым
Очень дельный совет, на будущие нужно будет запомнить
Как поакзывает практика использование or замедляет процесс выбора во много раз. Может попробовать использовать конструкцию
declare @city varchar(100);
set @city = '';
select FirstName, LastName, City
from Members
where @city in ('', City)
?
Хотите найти еще что-то интересное почитать? Можно попробовать отфильтровать заметки на блоге по категориям.
Программист, автор нескольких книг серии глазами хакера и просто блогер. Интересуюсь безопасностью, хотя хакером себя не считаю
Copyright © 2024 Михаил Флёнов. Тираж 1 штука. Отпечатано на ноутбуке. А за права отвечаю, или постараюсь.
А здесь еще и копирайт поставлю: Professional Web Development.
