При разработке отчетов (программ) SAP на языке ABAP для обращения к базе данных используются Open SQL-запросы. Синтаксис очень похож на SQL, но имеются некоторые отличия. Одно из таких отличий – возможность использования конструкции FOR ALL ENTRIES IN. Эта конструкция применяется в SELECT запросах до оператора WHERE. После неё указывается внутренняя таблица с данными, поля которой можно использовать в операторе WHERE в качестве условий выборки.

В этой статье я хочу рассказать о тонкостях работы этой конструкции: что происходит на уровне БД, об оптимизации запросов и о database-hints.

---

Очень часто программисты при написании кода на ABAP используют эту конструкцию. Она удобна, экономит время при разработке, но не все задумываются над тем, как она работает. И вот однажды, из-за возросшего объемы данных, наступает такой момент, когда написанная программа начинает «тормозить». Чаще всего проблема бывает в запросах к базе данных, и разработчик начинает оптимизировать их: добавляет индексы, убирает запросы из циклов и т.д. Но он практически никогда не обращает внимания на конструкцию FOR ALL ENTRIES IN, так как считает, что оптимизировать в ней нечего.

Что происходит на уровне БД

Давайте на простом примере проанализируем работу этой конструкции. Из таблицы BKPF выберем 1000 строк во внутреннюю таблицу LT_BKPF, а потом из таблицы BSIS выберем данные, используя конструкцию FOR ALL ENTRIES IN LT_BKPF. Аналогичный пример приведен в официальной справке SAP.

report z_test.

" объявляем переменные
"

data: begin of ls_bkpf,
         bukrs type bkpf-bukrs,
         belnr type bkpf-belnr,
      end of ls_bkpf.
data: lt_bkpf like table of ls_bkpf.

data: lt_bsis like table of bsis.

" выбираем данные из таблицы bkpf и помещаем их во внутреннюю таблицу lt_bkpf
"

select
  bukrs belnr
up to 1000 rows from
  bkpf
into corresponding fields of table
  lt_bkpf
where
  gjahr = '2013'.

check lines( lt_bkpf ) > 0.

" выбираем данные из таблицы BSIS. В FOR ALL ENTRIES IN передаем внутреннюю таблицу LT_BKPF
" 

select
  *
from
  bsis
into corresponding fields of table
  lt_bsis
for all entries in
  lt_bkpf
where
  bsis~bukrs = lt_bkpf-bukrs and
  bsis~belnr = lt_bkpf-belnr and
  bsis~gjahr = '2013'.

Для анализа работы конструкции будем использовать транзакцию ST05 – трассировка SQL-запросов.

В одном режиме запускаем ST05 и включаем трассировку (рис. 1):


Рис. 1 Запуск трассировки

В другом режиме выполняем нашу программу. После чего в ST05 выключаем трассировку и выводим результат (рис. 2, рис. 3, рис. 4), установив фильтр на таблицы BKPF и BSIS, чтобы отсеять ненужный нам мусор:


Рис. 2 Отключение трассировки


Рис. 3 Вывод результата трассировки


Рис. 4 Результат трассировки

Мы видим, что к таблице BKPF у нас отработал один запрос и вернул 1000 строк – здесь все нормально. А вот к таблице BSIS у нас выполнилось 100 запросов, да еще и каждый из них содержит 10 запросов, объединенных через конструкцию UNION ALL SELECT. Это, по сути, означает, что один Open SQL-запрос с FOR ALL ENTRIES IN превратился во время выполнения в 1000 отдельных запросов к базе данных. Если обобщенно – сколько записей во внутренней таблице FOR ALL ENTRIES IN, столько будет отдельных запросов к базе данных. Понятно, что при большом объеме данных это всё будет очень медленно работать.

Оптимизируем

Чтобы увеличить быстродействие, нам придется немного усложнить код нашей программы:

report z_test.

" объявляем переменные
"

data: begin of ls_bkpf,
         bukrs type bkpf-bukrs,
         belnr type bkpf-belnr,
      end of ls_bkpf.
data: lt_bkpf like table of ls_bkpf.
data: lt_bkpf_tmp like lt_bkpf.
field-symbols: <wa_bkpf> like ls_bkpf.

data: lt_bsis like table of bsis.

data: begin of ls_bukrs,
         bukrs type bukrs,
      end of ls_bukrs.
data: lt_bukrs like table of ls_bukrs.

" выбираем данные из таблицы BKPF и помещаем их во внутреннюю таблицу LT_BKPF
"

select
  bukrs belnr
up to 1000 rows from
  bkpf
into corresponding fields of table
  lt_bkpf
where
  gjahr = '2013'.

check lines( lt_bkpf ) > 0.

" получаем список уникальных БЕ во внутреннюю таблицу LT_BUKRS
"

loop at lt_bkpf assigning <wa_bkpf>.
  ls_bukrs-bukrs = <wa_bkpf>-bukrs.
  collect ls_bukrs into lt_bukrs.
endloop.

" для каждой БЕ выполняем запрос
"

loop at lt_bukrs into ls_bukrs.
  " выбираем из LT_BKPF документы, относящиеся к определенной БЕ и помещаем в таблицу LT_BKPF_TMP
  "

  clear lt_bkpf_tmp.

  loop at lt_bkpf assigning <wa_bkpf> where bukrs = ls_bukrs-bukrs.
    append <wa_bkpf> to lt_bkpf_tmp.
  endloop.

  " выбираем данные из таблицы BSIS. В FOR ALL ENTRIES IN передаем внутреннюю таблицу LT_BKPF_TMP
  "

  select
   *
  from
    bsis
  appending corresponding fields of table
    lt_bsis
  for all entries in
    lt_bkpf_tmp
  where
    bsis~bukrs = ls_bukrs-bukrs and
    bsis~belnr = lt_bkpf_tmp-belnr and
    bsis~gjahr = '2013'.
endloop.

Распишу по шагам, что мы сделали:

1. Проанализировали внутреннюю таблицу LT_BKPF и поняли, что значения в колонке BUKRS, в основном, повторяются.
2. Сохранили все уникальные значения колонки BUKRS во внутреннюю таблицу LT_BUKRS.
3. Переделали запрос к таблице BSIS:
   
   • для каждого уникального значения BUKRS выполняем отдельный запрос в цикле, предварительно подготовив внутреннюю таблицу LT_BKPF_TMP с номерами документов для конкретной БЕ (BUKRS). Эту таблицу передаем в FOR ALL ENTRIES IN вместо LT_BKPF;
   • INTO CORRESPONDING FIELDS заменили на APPENDING CORRESPONDING FIELDS, чтобы на каждом шаге не затирать внутреннюю таблицу LT_BSIS, а добавлять в нее данные;
     
   • в блоке WHERE оставили только одно поле из таблицы LT_BKPF_TMP.
   
   

Посмотрим результат трассировки после такой модификации кода (рис. 5, рис. 6):


Рис. 5 Результат трассировки после оптимизации кода


Рис. 6 Результат трассировки после оптимизации кода (подробно)

Мы видим, что нам удалось избавиться от UNION ALL SELECT и теперь к таблице BSIS у нас 100 запросов с оператором IN (по 10 значений в запросе) вместо 1000 запросов, объединенных через UNION ALL SELECT.

Когда я оптимизировал подобный запрос, мне достигнутого результата было недостаточно, и я решил разобраться, почему именно по 10 значений передается в оператор IN. Оказалось, что это регулируется глобальным настроечным параметром SAP max_in_blocking_factor и влияет на все запросы, но, используя так называемые database-hints, можно эту настройку поменять для конкретного запроса непосредственно перед выполнением этого запроса. Для этого нужно в самом запросе дописать %_hints db2 '&max_in_blocking_factor 500&' или %_hints oracle '&max_in_blocking_factor 500&' в зависимости от СУБД:

select
   *
from
   bsis
appending corresponding fields of table
   lt_bsis
for all entries in
   lt_bkpf_tmp
where
   bsis~bukrs = ls_bukrs-bukrs and
   bsis~belnr = lt_bkpf_tmp-belnr and
   bsis~gjahr = '2013'
   %_hints db2 '&max_in_blocking_factor 500&'.

После добавления database-hints имеем следующий результат (рис. 7):


Рис. 7 Результат трассировки после добавления database-hints

Видно, что теперь к таблице BSIS у нас всего 2 запроса с оператором IN (по 500 значений в запросе) вместо 1000 запросов.

Внимание! Настройку max_in_blocking_factor нужно изменять аккуратно: чем выше значение, тем больше требуется оперативной памяти для хранения результата. В каждом частном случае нужен индивидуальный подход, чтобы выбрать золотую середину между производительностью и потреблением ресурсов.

А стоит ли овчина выделки?

В большинстве случаев много маленьких запросов будут выполняться дольше, чем один большой запрос. Даже на нашем искусственном примере, где выборка осуществляется по индексируемым полям, мы получили почти четырехкратный выигрыш в скорости выполнения. Особый выигрыш в производительности получится, если в выборке (в блоке WHERE) участвуют не входящие в индекс поля. На моей практике встречались отчеты, время работы которых удалось уменьшить с 50 минут до 30 секунд без существенной корректировки кода и добавления новых индексов.

Резюмируем

1. При использовании конструкции FOR ALL ENTRIES IN LOCAL_TABLE стремитесь сделать так, чтобы в блоке WHERE использовалось только одно поле из внутренней таблицы LOCAL_TABLE.
2. Всегда проверяйте, чтобы таблица LOCAL_TABLE была не пустая, иначе условия с полями этой таблицы будут игнорироваться.
3. Используйте database-hints очень аккуратно.
4. Оптимизировать запросы таким образом есть смысл, только если какая-либо колонка внутренней таблицы LOCAL_TABLE содержит много повторяющихся значений, иначе нет никакого смысла.
5. Преждевременная оптимизация – корень всех зол (с) Дональд Кнут. Оптимизировать нужно только тогда, когда это действительно потребуется, но способы лучше знать заранее.