SQL является стандартным языком для работы с реляционными базами данных (РБД) и поддерживается большинством систем управления базами данных (СУБД).
SQL позволяет выполнять сложные запросы для выборки данных.
Есть множество материалов для изучения основ использования РБД и SQL-запросов.
Уважаемый читатель, вежливо отправляю Вас читать учебник! :)
А если без шуток, то просто не хочется долго затягивать со скучной теорией.
Очень хочется скорее перейти к практике.
Сегодня предлагаю сравнить, какой вариант запроса (с объединением или с оконной функцией) будет выполняться быстрее?
Предусловия для эксперимента
1.PostgreSQL.
2.Таблица истории обработки задач (примерно 70 тыс. задач, 500тыс. строк).
3. Количество параметров в каждой строке - около 15 параметров.
4. В ходе эксперимента настройки БД и размер таблицы остаются неизменны.
5. Железо, достаточное для обычного тестового окружения среднестатистической ИТ компании.
6. Задачи могут ходить по 3м состояниям ("Назначена", "В обработке", "Завершена"), состояния могут повторяться.
Есть несколько причин для повторения состояний:
- Сохранение данных в процессе обработки задачи: состояние задачи "В обработке" и не меняется, но создается новая запись в таблице;
- Переназначение задачи другому оператору: состояние задачи "Назначена" и не меняется, но создается новая запись в таблице;
- Горе-оператор не справился (бросил обрабатывать задачу): задача возвращается из состояния "В обработке" в состояние "Назначена".
Пример последовательности состояний (сортировка по дате и времени события):
01.06.2023 10:20 Назначена,
01.06.2023 10:25 Назначена,
01.06.2023 11:00 В обработке,
01.06.2023 11:15 Назначена,
01.06.2023 11:30 В обработке,
01.06.2023 11:35 В обработке,
01.06.2023 11:55 В обработке,
01.06.2023 12:00 Завершена.
Задача
Определить дату и время начала последней итерации обработки задачи.
01.06.2023 11:30 - вот эту дату и время мы хотим получить.
Решение
Возможно, как минимум 2 варианта решения:
1.Объединение (join) + группировка (group by)
Если коротко, находим последнее событие когда задача находилась в состоянии «Назначена», а затем получаем дату время первого попадания задачи в состояние «В обработке».
2.Oконные функции (window)
Здесь же мы получаем список состояний задачи, когда текущее состояние отличается от предыдущего. А затем получаем дату и время, когда задача последний раз перешла в состояние «В обработке».
Каждый запрос был запущен в двух вариациях: с получением данных по одной единственной задаче (закомментированные строки) и по всем задачам в таблице.
Результаты
А сейчас давайте сравним полученные результаты:
Я не претендую на высокую точность сравнения. У меня использовано очень небольшое количество замеров. Также результаты могут отличаться в зависимости от многих фактов (железо, настройки БД и т.п.).
Тем не менее примерно можно оценить какой вариант работает эффективнее.
1.Получено среднее плановое время выполнения запросов на основе анализа Explain PostgreSQL.
2.Получено реальное среднее время выполнения запросов на основе 10 замеров. Результаты замеров попадают в средний плановый диапазон.
3.Варианты запросов эквивалентны друг другу, возвращают идентичный результат. Но вариант с оконной функцией работает быстрее, чем вариант объединения с группировкой (как для одной заявки, так и для полной выборки задач).
4.Выполнено сравнение работы запроса для одной задачи и для большой выборки (около 70 тыс. задач). Можно заметить, что просадка в скорости выполнения в зависимости от объема получаемых данных при использовании оконной функции меньше, нежели с использованием объединения с группировкой.
Обязательно like, подписка. Будет интересно!
Telegram: lingvodog Дзен: psychodog