使用HibernateCritera API进行动态查询Oracle - 性能 [英] Dynamic query with HibernateCritera API & Oracle - performance

问题描述

我必须使用Hibernate并从Oracle检索数据，但问题是，传递给查询的参数数量并非总是相同。

对于为了简单起见，我们考虑下面的查询：

从TAB_1中选择COL_1，COL_2，...，COL_N，其中COL_1在（？，？，...？）

传递给in子句的参数数量在1到500之间。如果数字大约是1-50，它的工作速度非常快，但对于200，它需要几秒钟执行查询（解析，创建解释计划，执行查询）。索引被创建和使用 - 它被检查。

查询是动态创建的，所以我使用Hibernate Criteria API。对于第一个查询（具有> 100个参数）需要3-5秒，但对于下一个查询，其工作更快（即使参数数量不同）。我想提高第一个查询的响应时间。在这种情况下，假设Hibernate是必须的，我该怎么做？

我想要移除这个动态查询，在xml文件中创建一些静态查询作为命名查询例如，这些查询将在开始时进行预编译）例如

<1>如果参数数小于50，则为一个查询。

在这种情况下，如果我们有30个参数比查询看起来像：

从TAB_1中选择COL_1，COL_2，...，COL_N其中COL_1在（PAR_1，PAR_2，...，PAR_30，-1，-1，...，-1？）

2）第二个如果数字是50到100等。

问题在于，使用命名查询和HQL并不那么简单（在JDBC中它会直截了当）。在HQL中，我们仅传递一个列表，并且我们没有在该列表中指定一些参数，即事实上只有一个查询

  
 myQuery.setParameterList（person_list，myList）
  

有什么办法可以解决这个问题吗？

顺便说一下，我认为解释计划是针对每个新查询执行的例如：（a）从TAB_1中选择COL_1，COL_2，...，COL_N，其中（？，？，...，？）中的COL_1 <100 （b）从TAB_1中选择COL_1，COL_2，...，COL_N，其中COL_1在（？，？，...，？， ）< 100> - 解释计划将不会被创建，因为它已经存在于缓存中

（c）从TAB_1中选择COL_1，COL_2，...，COL_N其中（？，？，...，？）中的COL_1< 120> - 应该创建解释计划（对于具有120个参数的查询，没有解释计划），但与（a）相比花费更少的时间，几乎相同（b）因此，如果在执行类似查询之前执行类似的查询，Oracle可能会更快地创建此计划。

这是什么原因？

解决方案

这里有几件事情。首先，你不能绑定一个IN列表，至少我确信你不能。我怀疑Hibernate正在使用某种把你的数组内容放到Oracle可以使用的静态列表中的技巧。其次，如果这个查询是用很多不同的参数执行的，你必须绑定变量，否则整个数据库的性能将受到影响。

也就是说，有一种方法可以使用Tom Kyte描述的'trick'来绑定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作为标记
 9 from（select' ，'||：txt ||'，'txt from dual）
 10级别连接< =长度（：txt） - 长度（替换（：txt，'，'，''））+ 1 
 11）
 12 select * 
 13 from all_users 
 14其中user_id in（select * from bound_inlist）; 
 
 USERNAME USER_ID创建
 ------------------------------ ----- ----- --------- 
 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占位符，您需要将列表转换为字符串，并将其绑定。

您确定查询时间的差异不是由于机器上的缓存或负载变化？解析查询需要一点时间，但几秒钟是很长时间的。

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

For the sake of simplicity let's consider the following query:

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

The number of parameters passed to in clause is between 1 and 500. If the number is about 1-50 it works quite fast, but for 200 it takes a few seconds to execute the query (parsing, creating explain plan, executing the query). Indexes are created and used - it was checked.

The query is created dynamicly so I use Hibernate Criteria API. For the first query (with > 100 parameters) it takes 3-5 seconds, but for the next one it works faster (even if the number of parameters varies). I would like to improve the response time for the first query. What can I do in that case assuming that Hibernate is a must?

I though about removig this dynamic query, creating a few static queries as named queries in xml file (in that case those queries will be precompiled at the beginning) For example

1) one query if the number of parameters is less then 50.

In this case if we have 30 parameters than the query would look like:

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 one if the number is between 50 and 100 etc.

The problem is that it's not so simple using named queries and HQL (in JDBC it would be straighforward). In HQL we passed only a list and we don't specify a number of parameters in that list i.e. In fact there is only one query

'from Person where id in (:person_list)'

myQuery.setParameterList("person_list", myList)

Is there any option to solve that?

By the way, I thought that the explain plan is executed for each new query so for example:

(a) select COL_1, COL_2, ..., COL_N from TAB_1 where COL_1 in (?, ?, ..., ?) <100> - explain plan must be created

(b) select COL_1, COL_2, ..., COL_N from TAB_1 where COL_1 in (?, ?, ..., ?) <100> - explain plan won't be created because it already exists in cache

(c) select COL_1, COL_2, ..., COL_N from TAB_1 where COL_1 in (?, ?, ..., ?) <120> - explain plan should be created (there is no explain plan for a query with 120 parameters) but it takes less time in comparison with (a), almost the same as (b) so probably Oracle can create this plan faster if a similar query was executed before

What is the reason for that?

解决方案

There are a couple of things here. First of all, you cannot bind an IN list, at least I am pretty sure you cannot. I suspect Hibernate is using some sort of trick you put your array contents into a static inlist Oracle can use.

Secondly if this query is executed with lots of different parameters, you must you bind variables or the performance of the entire database will suffer.

That said, there is a way to bind an IN list using a 'trick' which Tom Kyte describes on his blog -

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

The code in there looks like:

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

The part:

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

Is basically where your query goes. The bit above is the trick which splits the comma separated string into a list of values. Instead of binding a list into the :txt placeholder, you would need to convert the list to a string and just bind that.

Are you sure the difference in query times isn't due to caching or load variations on the machine? Parsing the query will take a little time, but several seconds is a long time.

这篇关于使用HibernateCritera API进行动态查询Oracle - 性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！