Join или не Join, вот в чем вопрос…

В процессе разработки программ с обращением к БД часто возникает проблема создания SQL запроса по большому количеству таблиц. Существует два варианта: один сложный запрос с большим количеством Join’ов и условий или несколько простых sql запросов с последовательным применением результата обработанного запроса к следующим запросам.

Очевидный выбор начинающего разработчика – конечно первый вариант. Это классика sql запросов – Том Кайт [1]: «При разработке приложений баз данных я использую очень простую мантру: если можно, сделай это с помощью одного оператора SQL».

Рассмотрим эту проблему более подробно.

Основным аргументом при выборе одного запроса является скорость работы. При одном запросе к базе данных вместо нескольких происходит сокращение количества обращений к серверу, сокращение сетевого трафика. В сложном запросе имеется больше информации для оптимизатора запросов базы данных, благодаря этому повышается результативность оптимизации.  Но даже если запрос построится действительно оптимально, в такой стратегии есть недостатки. Сложные запросы труднее поддаются отладке.  Кроме того, объединение нескольких таблиц может приводить к блокировке большого объема данных на длительное время, что затруднит обновление данных другими операциями.

При выборе второго варианта с несколькими более простыми запросами облегчается отладка, т.к. каждый запрос может быть протестирован отдельно, но, обычно, большое количество запросов к базе данных приводит к снижению производительности. Однако последнее утверждение не всегда правильное. Хочу показать пример из практики работы с ORACLE на PL/SQL, в котором производительность созданных  запросов, сформированных по второму принципу, превосходит сложный запрос в несколько раз.

Есть три таблицы xx_log_msg, xx_batch_log, xx_if_errors с информацией о процессах (логи) в большой системе. Не спрашивайте, почему логи разбросаны по разным таблицам, так сложилось исторически, для разных классов приложений – разные таблицы для хранения логов. В целом та информация, которая нас интересует, в них однотипная: идентификатор процесса, дата-время создания записи (log_name, event_name).

Каждая из таблиц содержит информацию примерно о 25-30 тыс. процессов, в каждой таблице около 100 млн записей. Необходимо собрать и обработать информацию об основных процессах, которых порядка 100 (список выбираемых процессов хранится в отдельной таблице xx_event_params) из всех трех таблиц. Необходимо сгруппировать записи по идентификатору процесса, получить время начала и окончания работы, количество записей в логе и т.п.

Решение задачи одним большим запросом c объединением таблиц и inner join, на основе  которого строится курсор и производится выборка данных:

 cursor log_cur is      select log_name, event_name, min(log_date) as min_date, max(log_date) as max_date, count(*) as rec_count      from (select 'XX_LOG_MSG' as log_name, m.interface as event_name, m.dtime as log_date            from xx_log_msg m            inner join xx_event_params p1 on p1.event_name = m.interface and (upper(p1.attribute_1) like '%XX_LOG_MSG%' or p1.attribute_1 is NULL)            union all             select 'XX_BATCH_LOG', b.event_name, b.creation_date             from xx_batch_log b            inner join xx_event_params p2 on p2.event_name = b.event_name and (upper(p2.attribute_1) like '%XX_BATCH_LOG%' or p2.attribute_1 is NULL)            union all            select  'XX_IF_ERRORS', e.program_name, e.err_date            from xx_if_errors e           inner join xx_event_params p3 on p3.event_name = e.program_name and (upper(p3.attribute_1) like '%XX_IF_ERRORS%' or p3.attribute_1 is NULL)           ) t      group by log_name, event_name;    TYPE t_rec IS RECORD (table_name varchar2(255)                         ,event_name varchar2(255)                         ,min_date date                         ,max_date date                         ,rec_count number);    TYPE t_tbl IS TABLE OF t_rec;    l_events_tab t_tbl := t_tbl();  begin    open log_cur;    fetch  log_cur bulk collect into l_events_tab;    close  log_cur;    return;  end;  

В реальной нагруженной системе, когда с таблицами идет активная работа (каждые сутки добавляются около 10 млн в каждую, примерно столько же удаляются) данный скрипт отрабатывал примерно за 2 часа 45 мин. Это достаточно долго, и было принято решение попробовать сократить время работы.  

Во втором варианте запроса вместо трех join’ов остался один, который уже применялся после агрегации данных. Таким образом курсор принял вид: 

cursor log_cur is    select log_name, event_name min_date, max_date, rec_count      from (select log_name, t.event_name, min(log_date) as min_date                ,max(log_date) as max_date, count(*) as rec_count          from (select 'XX_LOG_MSG' as log_name, m.interface as event_name                      ,m.dtime as log_date                from xx_log_msg m                union all                 select 'XX_BATCH_LOG', b.event_name, b.creation_date                 from xx_batch_log b                union all                 select  'XX_IF_ERRORS', e.program_name, e.err_date                 from xx_if_errors e) t          group by t.log_name, t.event_name) t2     inner join xx_event_params p on p.event_name = t2.event_name                 and (upper(p.attribute_1) like '%' || t2.log_name || '%' or p.attribute_1 is NULL);

 Время работы запроса сократилось, но незначительно. Однако запрос без join отрабатывал за несколько минут несмотря на то, что возвращал на несколько порядков больше записей (около 40000).

Поэтому появился вариант с разделением запросов, когда для каждой таблицы создается отдельный запрос, с минимумом ограничений (только группировка) и уже внутри каждого курсорного цикла проверяется условие выборки (отдельным запросом) и выбираются только те записи, которые удовлетворяют условиям. Причем делается это для каждой исходной таблицы отдельным курсором, а результат собирается в одну общую коллекцию. Таким образом, вместо одного мы получили шесть отдельных SQL запросов: 

declare    l_count number;    TYPE t_rec IS RECORD (table_name varchar2(255)                         ,event_name varchar2(255)                         ,min_date date                         ,max_date date                         ,rec_count number);    TYPE t_tbl IS TABLE OF t_rec;    l_events_tab t_tbl := t_tbl();    cursor log_1 is      select 'XX_LOG_MSG' as table_name             ,interface as event_name             ,min(dtime) as min_date             ,max(dtime) as max_date             ,count(*) as rec_count      from xx_log_msg      group by interface;    cursor log_2 is      select 'XX_BATCH_LOG' as table_name            ,event_name            ,min(creation_date) as min_date            ,max(creation_date) as max_date            ,count(*) as rec_count      from xx_batch_log      group by event_name;    cursor log_3 is      select 'XX_IF_ERRORS' as table_name            ,program_name as event_name            ,min(err_date) as min_date            ,max(err_date) as max_date            ,count(*) as rec_count      from xx_if_errors      group by program_name;  begin      for l_rec in log_1 loop        begin          select count(*)          into l_count          from xx_event_params          where event_name = l_rec.event_name          and (attribute_1 like '%XX_LOG_MSG%' or attribute_1 is NULL);        exception when others then          continue;        end;        if l_count > 0        then          l_events_tab.extend;          l_events_tab(l_events_tab.last) := l_rec;        end if;      end loop;         for l_rec in log_2 loop        begin          select count(*)          into l_count          from xx_event_params          where event_name = l_rec.event_name          and (attribute_1 like '%XX_BATCH_LOG%' or attribute_1 is NULL);        exception when others then          continue;        end;        if l_count > 0         then          l_events_tab.extend;          l_events_tab(l_events_tab.last) := l_rec;        end if;      end loop;         for l_rec in log_3 loop        begin          select count(*)          into l_count          from xx_event_params          where event_name = l_rec.event_name          and (attribute_1 like '%XX_IF_ERRORS%' or attribute_1 is NULL);        exception when others then          continue;        end;        if l_count > 0          then          l_events_tab.extend;          l_events_tab(l_events_tab.last) := l_rec;        end if;      end loop;    return;  end;

И несмотря на то, что вместо одного запроса работают шесть запросов последовательно, такой вариант отрабатывает в реальной системе за 3-5 минут. Таким образом, практика показывает, что использование одного, но сложного запроса не всегда оправдано, несколько последовательных запросов могут давать значительный выигрыш в скорости обработки данных. Поэтому для достижения результата не нужно бояться иногда отступать от общепринятых принципов построения sql запросов. 

Список литературы:

1. Oracle для профессионалов. Пер. с англ./Том Кайт — СПб.: ООО «ДиаСофт», 2003. — 672 с. ISBN 5-93772-072-5