Точный онлайн-запрос с объединениями выполняется более 15 минут

Question

Я использую следующий запрос:

set use-result-cache false

set use-http-cache false

create or replace table settings@inmemorystorage
as
select '29676ec4-61b5-45eb-a5a3-6feffe03d1d3' sor_id
,      '[[Exploded]]' exploded_signal_text
,      '{res:itgen_eol_sales_order}' entity_name_singular
,      '{res:itgen_eol_sales_orders}' entity_name_plural
from   me

select ...
from   settings@inmemorystorage stg
left
outer
join   ExactOnlineREST..salesorders sor 
on     sor.orderid = stg.sor_id
left 
outer
join   ExactOnlineREST..salesorderlines soe
on     soe.orderid = stg.sor_id
left                                                
outer
join   BillOfMaterialItemDetails bom 
on     bom.billofmaterialitemdetails_billofmaterial_item_id_attr_guid = soe.item
left 
outer
join   ExactOnlineREST..items itm
on     itm.ID = bom.item_id_attr_guid
left 
outer
join   ExactOnlineREST..itemsread itr
on     itr.code = bom.item_code_attr 
where  sor.orderid is not null
and    itm.class_10 in ('A', 'D', 'M', 'S', 'Z')

получить данные из Exact Online. В моей тестовой среде примерно 1 секунда применяется для того, чтобы применить анализ спецификаций к заказу на продажу (примерно 5 операций по API XML и REST Exact Online). Тем не менее, на сайте клиента он работает более 15 минут. Похоже, это связано с поиском предметов (статей), использованных в ведомости материалов; в моей тестовой среде примерно 100 наименований, тогда как на сайте клиента 250.000 наименований.

Однако этот запрос используется в интерактивной программе и должен выполняться в течение 2,5 секунд.

Я пытался объединить элементы readread и items, чтобы ограничить извлекаемые элементы, но они имеют разные поля, которые необходимы из обеих таблиц.

Как я могу оптимизировать этот запрос для более быстрого выполнения при большом объеме данных?

Answer 1

Проблема во втором запросе: есть много элементов, а API Exact Online имеют пропускную способность около 300 элементов в секунду. Так что это ушло навсегда без изменений.

Есть два альтернативных маршрута:

1. Оптимизировать запрос
2. Использовать кеширование

Оптимизация запросов обеспечивает высокую производительность и небольшое использование ресурсов при первом и последующем использовании. Использование кэширования улучшает время ответа при повторном использовании, но требует больше ресурсов, чем оптимизированный запрос.

Оптимизировать точный онлайн-запрос

Чтобы оптимизировать запрос, вам нужно будет указать оптимизатору, как обрабатывать объединения более правильно, поскольку в Exact Online по умолчанию отсутствуют статистические и справочные данные. Я бы добавил следующие подсказки:

select /*+ join_set(soe, orderid, 100) join_set(itm, id, 100) join_set(itr, code, 100) */ ...
from   settings@inmemorystorage stg
...

Первый намек join_set(soe, orderid, 100) указывает оптимизатору изменить алгоритм соединения с хэш-соединений на цикл по индексу для соединения с soe на orderid (правая сторона), когда на пути выполнения возвращено не более 100 строк, возвращенных с предыдущего шага. В этом случае будет ровно одна строка, возвращенная из settings, То же самое относится к объединению на itm а также itr,

Для большой среды Exact Online это гарантирует, что у вас всегда будет 5 поисков, если в заказе на продажу меньше 60 строк. Это обычно занимает 1 секунду.

Использовать кеширование (Data Cache)

Когда вы конфигурируете базу данных PostgreSQL, SQL Server, Oracle или MySQL в качестве поставщика базы данных Invantive Data Cache, вы можете получить результаты запоминания частей запросов в обычной базе данных. Оптимизатор автоматически запрашивает эту обычную базу данных, используя ANSI SQL, когда кэш данных все еще достаточно "свеж".

Например:

select /*+ ods(true, interval '7 days') */ ...

говорит оптимизатору использовать кеш данных для всех данных Exact Online, когда данные были помещены в кеш данных не более 7 дней назад. Когда он старше 7 дней, он создает новую версию, сохраняет ее в кэше данных и использует ее.

Когда вам нужно внести изменения в Exact Online практически в реальном времени, вам придется настроить репликацию данных. Затем он получает через веб-хуки все события вставки / обновления / удаления и применяет их к вашему кешу данных. Но кешу все еще может быть около 30 минут, так как распространение может занять некоторое время.

Производительность намного лучше, чем без кеша и без оптимизации. В целом, пропускная способность 250000 наименований будет в 1000 раз лучше, что сопоставимо с использованием 250 наименований. Учитывая типичный размер страницы 60 Exact Online, он будет восприниматься как 5 + 5 + 3 = 13 операций ввода-вывода, то есть примерно 2,6 секунды, что близко к заданным границам 2,5 секунды.

Обратите внимание, что используемая вами таблица спецификаций НИКОГДА не будет использовать поиск по индексу, поскольку в данный момент нет доступных индексов. Так что если у вас большой список материалов по всем продуктам, вы должны кешировать данные для разумной производительности.