使用 HibernateCritera API 进行动态查询甲骨文——性能

发布于 2024-08-11 03:49:29 字数 1410 浏览 10 评论 0原文

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

为了简单起见，我们考虑以下查询：

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

传递给 in 子句的参数数量在 1 到 500 之间。如果数字约为 1-50，则运行速度相当快，但对于 200，执行查询需要几秒钟（解析、创建解释计划、执行查询）。索引已创建并使用 - 已检查。

查询是动态创建的，因此我使用 Hibernate Criteria API。对于第一个查询（具有 > 100 个参数），需要 3-5 秒，但对于下一个查询，它的运行速度更快（即使参数数量不同）。我想改进第一个查询的响应时间。假设 Hibernate 是必须的，那么在这种情况下我能做什么？

我想删除这个动态查询，在 xml 文件中创建一些静态查询作为命名查询（在这种情况下，这些查询将在开始时预编译）例如

1) 如果参数数量少于 50，则一个查询

。如果我们有 30 个参数，则查询将如下所示：

select COL_1, COL_2, ..., COL_N from TAB_1 where COL_1 in (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) select COL_1, COL_2, ..., COL_N from TAB_1 where COL_1 in (?, ?, ..., ?) < ；100> - 必须创建解释计划

(b) 从 TAB_1 选择 COL_1、COL_2、...、COL_N，其中 (?, ?, ..., ?) <100> 中的 COL_1 - 不会创建解释计划，因为它已存在于缓存中

(c) select COL_1, COL_2, ..., COL_N from TAB_1 where COL_1 in (?, ?, ..., ?) <120>; - 应该创建解释计划（对于具有 120 个参数的查询，没有解释计划），但与 (a) 相比，它花费的时间更少，几乎与 (b) 相同，因此如果类似的查询，Oracle 可能可以更快地创建此计划之前就被处决了，

原因是什么？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

萌能量女王 2024-08-18 03:49:29

这里有几件事。首先，你不能绑定 IN 列表，至少我很确定你不能。我怀疑 Hibernate 正在使用某种技巧，您可以将数组内容放入 Oracle 可以使用的静态列表中。

其次，如果使用许多不同的参数执行此查询，则必须绑定变量，否则整个数据库的性能将受到影响。

也就是说，有一种方法可以使用 Tom Kyte 在他的博客上描述的“技巧”来绑定 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 占位符中，而是需要将列表转换为字符串并进行绑定。

您确定查询时间的差异不是由于计算机上的缓存或负载变化造成的吗？解析查询将花费一点时间，但几秒钟是很长的时间。

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.

回复收藏 0 原文

亂 2024-08-18 03:49:29

我使用过 IN(...) 查询，该查询在该列表中最多包含 1000 个 id；我可以向你保证，解析/准备/缓存一条语句不需要几秒钟。

Hibernate 确实会使用您传递的列表中的实际元素数量自动扩展您指定的参数列表，因此，如果您确实想将其“固定”在某个级别，您需要做的就是附加足够的 -1 到结束。然而，这肯定不是问题，特别是因为我们正在讨论加快第一次查询运行的速度 - 无论如何，还没有准备/缓存任何语句。

您是否查看了查询的执行计划？通过解释计划和自动跟踪都启用了吗？当列表中有 30 个元素和 120 个元素时，它们有区别吗？您的实际查询是否真的类似于您发布的“select ... from table where id in (...)”，还是更复杂？我愿意打赌，在 30 到 120 个元素之间，Oracle 决定（可能是错误的）不使用索引会更快，这就是您看到时间增加的原因。

回复收藏 0 原文

~没有更多了~