Комментарии 36
Начать надо было с просмотра планов выполнения запросов, чтоб понять, что не так делает первый запрос, 100% можно изменить этот запрос, или даже просто расставить хинты оптимизатору, чтоб он отработал также как и итоговый вариант с шестью отдельными запросами, или даже быстрее.
А бывает, что просто достаточно пересобрать статистику, чтоб оптимизатор вправил себе мозги и перестроил план запроса на оптимальный.
Пересбор статистики реультатов не давал, это проверено. Изменить большой запрос, чтобы он заработал - быстро не получилось. А т.к вариант с несколькими ортаботал за приемлемое для нас время, остановились на нем. Нужно ведь учитывать и то, что невозможно одной задачей заниматься бесконечно, есть и другие...
Полностью поддердживаю. Без плана запроса разговор ни о чем.
Сознательно не приводил здесь планы запросов, статья не про оптимизацию как таковую, а про то, что не нужно бояться иногда отступать от общепринятых принципов построения sql запросов, если это позволяет решить задачу быстрее.
Я к тому, что БД, как вы указали - Оракл, и тут я на 100% поддержу мантру Тома Кайта (благо почти четверть века его периодически читаю), что запрос с join реально выигрышнее для этой БД. Что же касается, к примеру DB2 или в меньшей степени MySQL и ее форков, например, то да, там частенько ваш подход будет выгоднее, по крайней мере это совет из документации по ДБ2 (пруфоф не будет, кому надо нагуглят). Поэтому разговор о "не нужно бояться иногда отступать от общепринятых принципов построения sql запросов" - несостоятельный. У каждой реляционки по своему и общепринятого принципа нет. А в случае Оракла надо было действительно начинать рассказ с плана, ну это мое имхо.
Посмотрите на сами запросы, там предикаты джоинов заставляют плакать query planner.
Очень спорная статья.
Правильно написанный SQL с join, если БД правильно и оптимально индексирована, должен быть быстрее. Другое дело, что иногда написание именно правильного запроса является очень трудной и нетривиальной задачей.
Но для этого есть EXPLAIN и другие инструменты для оптимизации запросов.
Отдельными запросами можно решать проблемы производительности только если из за каких-то причин нельзя правильно индексировать БД. (Ну, например если приходится писать в базу очень часто).
Но все-равно, быстро не будет: У автора время сократилось с 2.5 часов до 3..5 минут, но ведь, это все-равно медленно.
Согласен. То что автор даже не упомянул планы, и вероятно не пытался их смотреть и делать выводы, сильно обесценивает данный текст.
Отдельными запросами можно решать проблемы производительности только если из за каких-то причин нельзя правильно индексировать БД. (Ну, например если приходится писать в базу очень часто).
Не, ну не обязательно. У меня был опыт, когда мы отчет, выполнявшийся часами в базе Sybase, оптимизировали до минут, при этом все расчеты выполнялись в кластере из Java машин. То есть, по сути. мы смогли данные кластеризовать, загрузить в память, и вычисления распараллелить. В итоге нагрузка выглядела так - минут пять данные грузились в кластер (уж сколько там было запросов, я не вспомню, но видимо по числу узлов кластера), за какое-то смешное время все рассчитывалось, и еще несколько минут отчет выгружался. Запросы, ясное дело, были тут совсем другие, простейшие.
Ну то есть, если у вас есть другой вычислительный ресурс (как у нас было, причем кластер мы собрали из каких-то совсем простых линукс машинок, помнится с гигабайтом памяти каждая всего - да, то было очень давно), то вы можете работу переложить на этот ресурс, и возможно сильно ускорить.
Не, ну не обязательно. У меня был опыт, когда мы отчет, выполнявшийся часами в базе Sybase, оптимизировали до минут, при этом все расчеты выполнялись в кластере из Java машин.
Ну, это уже принципиально другое дело. Я имел ввиду при прочих равных условиях. Конечно если у вас гигантская вычислительная мощь, это принципиально поменяет весь расклад.
Здесь как раз тот самый случай - с таблицами идет активная работа (каждые сутки добавляются около 10 млн в каждую, примерно столько же удаляются)
Отдельными запросами можно решать проблемы производительности только если из за каких-то причин нельзя правильно индексировать БД
Зачем так категорично? Бывают ситуации, когда надо, например, обработать данные перед фильтрацией. Простой пример - отдельный поисковый движок. Или, часть данных лежит в другом сервисе. Или, данные в столбце надо предварительно изменить. Например, нужно выбрать все записи, в которых цена, после применения скидки по сложной формуле, не уменьшится более чем на 50%. Нет, за счëт расширения СУБД, написания своих функций, это можно сделать. Но, в большинстве случаев, не нужно, поскольку планируемая нагрузка на инстанс с бэкендом, выше, чем планируемая нагрузка на инстанс с БД. Так что, дробление большого запроса на несколько мелких, остаëтся хорошим решением. Но не в случае данной статьи. В ней очень похоже на то, что автор не озаботился созданием индексов, или просто недостаточно хорошо спроектировал БД.
Невозможно оптимально индексировать БД под все задачи, в данном случае индексов для моего запроса нет, и не будет
Последний вариант ведь отличается от первого: таблицы читаются каждая в свой промежуток времени, результаты выполнения будут разные, если в таблицы постоянно что-то пишется. Рассуждение о прозводительности запросов в РСУБД без предоставления планов запросов - это чисто гадание: непонятно, почему стало быстрее, почему было медленно, какие ожидания по кардинальностям у планировщика и какие фактические кардинальности, и будет ли оно так же быстро через два месяца. Глядя только на код PL/SQL непонятно, почему обычный JOIN медленнее многократного выполнения одного запроса к таблице (на каждую итерацию цикла), если со статистикой все в порядке (тем более Oracle умеет TABLE ACCESS BY INDEX ROWID BATCHED, если в случае с PL/SQL выстрелил доступ по индексам).
Также вопрос к исходному запросу: зачем использовать JOIN для фильтрации, если в SQL для этих целей существует явный IN/EXISTS?
Join нужен когда нужны данные из таблицы, если не нужны и просто проверка на наличие то обходимся без него, допустим через exists. Другое дело что запрос нужно правильно составлять, но тут уж кто как умеет.
Ну не знаю, у меня нет слов ... блин деградация налицо. Надо материться, но наверное администрация будет против.
Момент первый: расуждаем про оптимизацию, но нет приложенных планов запросов. Ну как? Тем более запросы по сути копеечные и всего три таблицы.
Момент второй: по памяти, могу ошибаться, но условия вроде "upper(p2.attribute_1) like '%XX_BATCH_LOG%'" сразу говорят о проблеме, так как чуда нет и грозит full scan, так как записи фильтруются по вычислимому выражению от столбика. Вопрос, там реально разработчики "прикалывались" и писали большими и маленькими буквами от балды? Низя найти тот же фильтр, но без вычисления значения? И сразу может оказаться, что индекс стоит построить. Но тут можно только гадать, так как сикоко фильтруется через запросы без данных не видать. Ну и блин, судя по всему табличка xx_event_params - это некий параметризатор, и на фига по ней чего-то вычислять - не ясно. Внести все варианты и все - получите фильтр по чистому значению.
Более того, в "быстром запросе" у вас условия в виде attribute_1 like '%XX_IF_ERRORS%', т.е. никакого вычисления. Вот оптимизатор вас и возлюбил, применив индекс. Но это не точно, хотя принципиальная разница именно в этом
"Кроме того, объединение нескольких таблиц может приводить к блокировке большого объема данных на длительное время, что затруднит обновление данных другими операциями. "
Это что за такие блокировки? В Oracle читатели не блокируют никого.
Согласен со всеми предыдущими постами, дайте планы.
По факту в PL/SQL вариантами вы руками сделали Nested Loop, какие соединения в планах первых двух запросов.
И главное последний вариант:
where event_name = l_rec.event_name and (attribute_1 like '%XX_IF_ERRORS%' or attribute_1 is NULL)
второй вариант:
(upper(p.attribute_1) like '%' || t2.log_name || '%' or p.attribute_1 is NULL)
Т.е. upper есть в одном запросе и нет в другом, кроме того в первом запросе константа.
Итого: запросы не равнозначны, построен Nested Loop (на SQL это сделать можно, и непонятно почему не сделан), без планов статья смысла не имеет.
Я бы сказал, тут не статья, а наглядное пособие о том как делать не надо.
Если я правильно понимаю, есть ряд независимых таблиц, которые друг с другом никак не связаны, но зачем-то их пытаются связать. Соответственно никаких ключей там скорее всего нет. Оператор UNION нам как бы подсказывает, что идет не выборка, а сборка. Если на это всё еще накладывать условия и сортировки - это заставит базу выбирать все строки, складывать их у себя в уме и преобразовывать результат. Уже можно тушить свет и вся надежда только на мощь базы и умность оптимизатора.
Дальше мы по потенциально ОЧЕНЬ большому количеству строк делаем BULK COLLECT (что правильно), но без LIMIT - ау, память бесконечная?
А в варианте решения потабличном наоборот не делаем BULK и обрабатываем построчно, ну правильно, скорость то нам зачем.
Что можно делать - это нужно вникать в конкретику, но например объединить данные в одну таблицу, а системам вместо таблиц подпихнуть обновляемые view которые будут писать в эту таблицу. Фильтруйте записи до UNION, анализируйте ваши запросы по плану, используйте хинты.
Возможно вообще есть смысл ваши селекты делать во временную таблицу и работать уже с ней.
Но лучшее решение - позвать грамотного DBA который поможет и разобраться с вашим звпросом и грамотно спроектирует базу.
Есть задачи основные, а есть некая обвязка, которая облегчает сопровождение (но непосредсвтенн но не влияет на решение основных задач, а иногда и мешает, забирая под себя ресурсы). Это как раз такой случай - и переделывать БД под эту задачу никто не будет. поэтому и приходится искать альтернативные варианты решения.
Я только не понял, почему не был рассмотрен самый очевидный вариант - JOIN, GROUP BY, и только потом UNION ALL. Всё - в рамках одного запроса.
Убил upper(p3.attribute_1) like '%XX_IF_ERRORS%' - прощайте, индексы, здравствуй, фуллскан.
Ну и не понял, зачем вообще потребовались курсоры. Видимо, какие-то специфические оракловые заморочки...
Убил upper(p3.attribute_1) like '%XX_IF_ERRORS%' - прощайте, индексы, здравствуй, фуллскан
Есть исключение, когда тоже самое в выражении WHERE частичного индекса.
Но в статье нет ни индексов, ни планов запросов, что совершенно лишает её какой-либо ценности
Есть исключение, когда тоже самое в выражении WHERE частичного индекса.
Это да. Но вот я как-то даже представить не могу, что кто-то будет создавать индекс по указанному выражению - с учётом того, что у выражения возможны всего три (а с учётом предметной области - скорее два) значения, а их распределение может быть по смыслу достаточно равномерным, то есть полезность такого индекса получится околонулевая. А само выражение никак не тянет на "почти в каждом запросе".
Курсоры ладно, иногда нужны. Но в варианте где большой запрос, я так и не понял, зачем так делать:
открываем курсор по большому количеству данных
зачитываем все данные в RAM сервера БД. Скажите спасибо ещё что out-of-memory не словили, с большой вероятностью сервер БД полез во временное табличное пространство или своп.
а потом ещё и ничего не делаем с зачитанным массивом, просто return. Но тут я предполагаю что опечатка, имели ввиду return l_events_tab.
массив, естественно, обрабатывается, но это уже не предмет рассмотрения данной статьи. В данном случае виноват, пропущено многоточие между end loop и return
Суть в том, что массив (table of something) — это данные, хранящиеся в RAM сервера БД. Если в отведённый размер RAM не влезет, будет либо своппинг, либо использование временного табличного пространства, либо ошибка out of memory. В любом случае ничего хорошего. Делать массив размера, сопоставимого с количеством RAM, заведомо плохая идея.
Т.е. если у вас реальный код был написан действительно именно так, как в статье (затаскиваем весь результат выборки в массив и потом с ним работаем), то вполне возможно, что тормоза вообще даже не из-за джойнов и не из-за неэффективного запроса, а просто из-за того, что сервер БД ушёл своппиться.
В свое время периодически раскладывал написанные ранее портянки на несколько простых запросов. Средний выигрышь в скорости - 5 раз. Не говоря уже о задействованых ресурсах. Городить огород нужно только тогда, когда это реально нужно.
Вот вы сервак насилуете.
В первом приведенном "большом" запросе агрегацию с группировкой внесите в подзапрос.
Снаружи группировка не нужна. Тогда сервак поймет что от него хотят. И если не сложно, время выполнения одним исправленным запросом напишите.
Агрегация и группировка в подзапросе тоже проверялась, но когда нет необходимых индексов (а создание новых специально для этой задачи не предусматривается) - все равно работает медленно. Время - специально еще раз сегодня запустил, после 45 мин работы - снял задачу, дпльше не имеет смысла проверять.
Этот треш можно кратко пересказать: "Я умудрился написать настолько плохой запрос, что есть и другие плохонаписанные запросы, которые работают быстрее".
У меня есть пример получше, когда вместо одного сложного запроса стоило написать несколько поменьше: если вкратце, то часть запроса должна была использоваться несколько раз для разных наборов данных разного формата, используемых потом в коде в разных местах.
Чуть подробней. Клиенты посещают сеть организаций, нужно получить список визитов клиентов, отфильтрованный по определённым критериям, относящимся к разным аспектам - это сам по себе запрос с приличным набором джоинов. Далее нужно получить информацию в аггрегированном виде по найденным визитам (для запроса достаточно знать только список их id) о том, какие в рамках них услуги были назначены, какие получены, а от каких назначенных клиент отказался, какие не смог получить. Все эти списки услуг имели разные форматы. Далее нужно было в интерфейсе отобразить эти данные как по отдельности, связав с ранее полученными данными клиентов, так и вместе, скомпоновав по определённой логике.
Теоретически можно было бы упихать всё это в один запрос, но сведение в выдаче одного SQL-запроса разноформатных данных точно не приводит к удобству последующей работы с ними. Ещё можно было для каждого списка услуг данные по клиентам запрашивать заново в каждом запросе, но... зачем, если можно один раз?
Join или не Join, вот в чем вопрос…