В процессе разработки программ с обращением к БД часто возникает проблема создания 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