Динамический запрос с HibernateCritera API & Oracle - производительность

Question

Я должен использовать Hibernate и получать данные из Oracle, но проблема в том, что количество параметров, передаваемых в запрос, не всегда одинаково.

Для простоты рассмотрим следующий запрос:

выберите COL_1, COL_2, ..., COL_N из TAB_1, где COL_1 в (?,?, ...?)

Число параметров, передаваемых в предложении, находится в диапазоне от 1 до 500. Если число составляет около 1-50, оно работает довольно быстро, но для 200 требуется несколько секунд для выполнения запроса (разбор, создание плана объяснения, выполнение запроса), Индексы созданы и использованы - это было проверено.

Запрос создается динамически, поэтому я использую Hibernate Criteria API. Для первого запроса (с> 100 параметрами) это занимает 3-5 секунд, но для следующего запроса он работает быстрее (даже если количество параметров меняется). Я хотел бы улучшить время ответа на первый запрос. Что я могу сделать в этом случае, если Hibernate является обязательным?

Я хотел бы удалить этот динамический запрос, создав несколько статических запросов в виде именованных запросов в файле XML (в этом случае эти запросы будут предварительно скомпилированы в начале). Например,

1) один запрос, если количество параметров меньше 50.

В этом случае, если у нас 30 параметров, запрос будет выглядеть так:

выберите COL_1, COL_2, ..., COL_N из TAB_1, где COL_1 в (PAR_1, PAR_2,..., PAR_30, -1, -1,..., -1?)

2) второй, если число от 50 до 100 и т. Д.

Проблема в том, что не так просто использовать именованные запросы и HQL (в JDBC это было бы просто). В HQL мы передали только список, и мы не указываем количество параметров в этом списке, т.е. фактически есть только один запрос

'from Person where id in (:person_list)'

myQuery.setParameterList("person_list", myList)

Есть ли вариант, чтобы решить это?

Кстати, я думал, что план объяснения выполняется для каждого нового запроса, например:

(a) выберите COL_1, COL_2, ..., COL_N из TAB_1, где COL_1 в (?,?, ...,?) <100> - должен быть создан план объяснения

(b) выберите COL_1, COL_2, ..., COL_N из TAB_1, где COL_1 в (?,?, ...,?) <100> - план объяснения не будет создан, поскольку он уже существует в кэше

(c) выберите COL_1, COL_2, ..., COL_N из TAB_1, где COL_1 в (?,?, ...,?) <120> - должен быть создан план объяснения (нет плана объяснения для запроса с 120 параметрами), но это занимает меньше времени по сравнению с (a), почти так же, как (b), поэтому, возможно, Oracle сможет создать этот план быстрее, если аналогичный запрос был выполнен ранее

В чем причина этого?

Answer 1

Здесь есть пара вещей. Прежде всего, вы не можете связать список IN, по крайней мере, я уверен, что вы не можете. Я подозреваю, что Hibernate использует какую-то хитрость, когда вы помещаете содержимое массива в статический список, который может использовать Oracle.

Во-вторых, если этот запрос выполняется с большим количеством различных параметров, необходимо связать переменные, иначе производительность всей базы данных пострадает.

Тем не менее, есть способ связать список IN, используя "трюк", который Том Кайт описывает в своем блоге -

http://tkyte.blogspot.com/2006/01/how-can-i.html

Код там выглядит так:

ops$tkyte@ORA10GR2> with bound_inlist
2  as
3  (
4  select
5    substr(txt,
6           instr (txt, ',', 1, level  ) + 1,
7           instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
8           as token
9    from (select ','||:txt||',' txt from dual)
10  connect by level <= length(:txt)-length(replace(:txt,',',''))+1
11  )
12  select *
13    from all_users
14   where user_id in (select * from bound_inlist);

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM                                  5 30-JUN-05
OPS$TKYTE                             104 20-JAN-06

Часть:

12  select *
13    from all_users
14   where user_id in (select * from bound_inlist);

В основном, где ваш запрос идет. Бит выше - это хитрость, которая разбивает строку через запятую в список значений. Вместо того, чтобы связывать список в заполнитель: txt, вам нужно преобразовать список в строку и просто связать это.

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

Answer 2

Я работал с IN(...) запросы, имеющие до 1000 идентификаторов в этом списке; Я могу гарантировать вам, что для анализа / подготовки / кэширования оператора не требуется несколько секунд.

Hibernate действительно автоматически расширяет заданный вами список параметров, используя фактическое количество элементов в передаваемом вами списке, поэтому, если вы действительно хотите сохранить его "фиксированным" на определенном уровне, все, что вам нужно сделать, это добавить достаточно -1 для конец. Тем не менее, это, безусловно, не проблема, тем более что мы говорим об ускорении первого выполнения запроса - все равно никакие операторы не были подготовлены / кэшированы.

Вы смотрели на планы выполнения ваших запросов? Оба через план объяснения и Autotrace включены? Различаются ли они, когда в вашем списке 30 элементов и 120 элементов? Действительно ли ваш реальный запрос выглядит как "выбрать... из таблицы, где идентификатор в (...)", который вы опубликовали, или он более сложный? Я готов поспорить, что где-то между 30 и 120 элементами Oracle решает (возможно, по ошибке), что будет быстрее не использовать индекс, поэтому вы видите увеличение времени.