Dynamic query with HibernateCritera and Oracle APIs - performance

Question

I need to use Hibernate and retrieve data from Oracle, but the problem is that the number of parameters passed to the request is not always the same.

For simplicity, consider the following query:

select COL_1, COL_2, ..., COL_N from TAB_1, where COL_1 in (?,?, ...?)

The number of parameters transferred to the unit is from 1 to 500. If the number is about 1-50, it works pretty quickly, but 200 takes a few seconds to complete the request (parsing, creating an explanation plan, fulfilling the request). Indexes are created and used - it has been checked.

The request is created dynamically, so I use the Hibernate API. For the first request (s> 100 parameters) it takes 3-5 seconds, but for the next one it works faster (even if the number of parameters changes). I would like to improve response time for the first request. What can I do in this case, if I assume that Hibernate is mandatory?

At least I’m about deleting this dynamic query by creating several static queries as named queries in an XML file (in this case these queries will be precompiled at the beginning) For example

1) one request if the number of parameters is less than 50.

In this case, if we have 30 parameters, than the request will look like this:

select COL_1, COL_2, ..., COL_N from TAB_1, where COL_1 in (PAR_1, PAR_2, ..., PAR_30, -1, -1, ..., -1?)

2) the second, if the number is from 50 to 100, etc.

, , 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 , , , , , "" , , , -1s . , , , , first - /.

? , ? , 30 120 ? : "... , id (...)" ? , - 30 120 Oracle (, ), , .