apache derby挂断执行查询 [英] apache derby hangs up executing query

问题描述

我非常感谢您的帮助。
我已经从客户那里得到了这个德比数据库，并且他们的应用程序基于Hibernate。我无法使其工作，因为Hibernate生成的查询会挂起整个数据库：

i'd really appreciate some help here. I've got this derby database from my customer, and their application based on Hibernate. I can't make it work because Hibernate generates query that hangs up the whole database:

select houses0_.STREET_ID as STREET5_1_, houses0_.ID as ID1_, houses0_.ID as ID42_0_, houses0_.NAME as NAME42_0_, houses0_.SHORT_NAME as SHORT3_42_0_, houses0_.INDEX as INDEX42_0_ from SRG.HOUSE_REFERENCE houses0_ where houses0_.STREET_ID in (select streets0_.ID from SRG.STREET_REFERENCE streets0_ where streets0_.TOWNSHIP_ID in (select townships0_.ID from SRG.TOWNSHIP_REFERENCE townships0_ where townships0_.CITY_ID='555'))

<我没有看到任何可疑的东西，但是当我用它启动derby时-derby却什么也没做。这是我的日志：

I dont see anything suspicious in it, but when i launch derby with it - derby just does nothing. Here are my logs:

 2011-10-31 21:58:10.328 GMT Thread[DRDAConnThread_3,5,main] (XID = 1152939), (SESSIONID = 1), (DATABASE = dev), (DRDAID = NF000001.FC97-434877518919679629{1}), Begin compiling prepared statement: select houses0_.STREET_ID as STREET5_1_, houses0_.ID as ID1_, houses0_.ID as ID42_0_, houses0_.NAME as NAME42_0_, houses0_.SHORT_NAME as SHORT3_42_0_, houses0_.INDEX as INDEX42_0_ from HOUSE_REFERENCE houses0_ where houses0_.STREET_ID in (select streets0_.ID from STREET_REFERENCE streets0_ where streets0_.TOWNSHIP_ID in (select townships0_.ID from TOWNSHIP_REFERENCE townships0_ where townships0_.CITY_ID='555')) :End prepared statement
2011-10-31 21:58:10.682 GMT Thread[DRDAConnThread_3,5,main] (XID = 1152939), (SESSIONID = 1), (DATABASE = dev), (DRDAID = NF000001.FC97-434877518919679629{1}), End compiling prepared statement: select houses0_.STREET_ID as STREET5_1_, houses0_.ID as ID1_, houses0_.ID as ID42_0_, houses0_.NAME as NAME42_0_, houses0_.SHORT_NAME as SHORT3_42_0_, houses0_.INDEX as INDEX42_0_ from SRG.HOUSE_REFERENCE houses0_ where houses0_.STREET_ID in (select streets0_.ID from SRG.STREET_REFERENCE streets0_ where streets0_.TOWNSHIP_ID in (select townships0_.ID from SRG.TOWNSHIP_REFERENCE townships0_ where townships0_.CITY_ID='555')) :End prepared statement
2011-10-31 21:58:10.758 GMT Thread[DRDAConnThread_3,5,main] (XID = 1152939), (SESSIONID = 1), (DATABASE = dev), (DRDAID = NF000001.FC97-434877518919679629{1}), Executing prepared statement: select houses0_.STREET_ID as STREET5_1_, houses0_.ID as ID1_, houses0_.ID as ID42_0_, houses0_.NAME as NAME42_0_, houses0_.SHORT_NAME as SHORT3_42_0_, houses0_.INDEX as INDEX42_0_ from SRG.HOUSE_REFERENCE houses0_ where houses0_.STREET_ID in (select streets0_.ID from SRG.STREET_REFERENCE streets0_ where streets0_.TOWNSHIP_ID in (select townships0_.ID from SRG.TOWNSHIP_REFERENCE townships0_ where townships0_.CITY_ID='555')) :End prepared statement

然后什么都没有。
我设置了一些属性：

And NOTHING after that. I've set some properties:

derby.drda.portNumber=1529

derby.locks.deadlockTrace=true
derby.locks.monitor=true

derby.locks.deadlockTimeout=1
derby.locks.waitTimeout=1

derby.language.logStatementText=false
derby.infolog.append=true

derby.stream.error.logSeverityLevel=0

derby.drda.logConnections=true

derby.language.logStatementText=true

derby.language.logQueryPlan=true

可以查看发生了什么，但是日志中没有任何内容，该查询只需要永恒即可执行，它可以通过休眠或squirrelsql或derby的ij工具完成。
如您所见，超时非常小，但是derby不会停止执行查询，因此它可以执行查询所需的任何操作。

to see whats going on, but there's nothing in logs, this query just takes eternity to execute, either its done from hibernate or squirrelsql or derby's ij tool. As you can see timeouts are pretty small but derby doesnt stop executing query, it leaves it to do whatever the query wants.

查询并不是那么复杂-我用join重写了它，花了1秒钟来执行。但不是那种形式-我不明白，为什么？

Query is not that complex - i rewrote it using joins and it took 1 sec to execute. But not in that form - i dont get it, why? Can you give me any hints?

UPD ：house_reference包含7508行，从house_reference中选择*不到一秒钟，street_reference包含〜44k行和子查询

UPD: house_reference contains 7508 rows, selecting * from house_reference takes less than a second, street_reference contains ~44k rows, and subquery

select streets0_.ID from STREET_REFERENCE streets0_ where streets0_.TOWNSHIP_ID in (select townships0_.ID from TOWNSHIP_REFERENCE townships0_ where townships0_.CITY_ID='52000001000')

在不到一秒的时间内返回了111行。因此，从带有111行的in子句的house_reference中选择*需花费 22 分钟的时间来执行，并返回0个结果。这对我来说似乎不行，但我不是数据库管理方面经验丰富的法官，这就是为什么我要问社区：）

returns 111 rows in less than a second. so selecting * from house_reference with in clause containing 111 rows takes 22 minutes to execute, and it returns 0 results. That doesnt look ok to me, but im not that experienced in database administration to judge, thats why im asking the community :)

推荐答案

指定 in（子查询）的查询不能总是用相应的联接替换。此过程称为扁平化，数据库必须遵循一些规则才能给出正确的答案。请参阅此文档： http://db.apache.org/derby/docs /10.8/tuning/ctuntransform36368.html

Queries specifying "in (subquery)" cannot always be replaced with a corresponding join. This process is called "flattening", and there are rules that the database has to follow in order to give the right answer. See this documentation: http://db.apache.org/derby/docs/10.8/tuning/ctuntransform36368.html

不幸的是，联接中的子查询存在严重的性能问题并不少见。我很高兴您找到了一种解决方法（假设您重写的查询实际上是您可以接受的解决方法）。

Unfortunately it's not uncommon to have significant performance problems with subqueries in joins. I'm glad you found a workaround (assuming that your re-written query is actually an acceptable workaround for you).

对象关系映射库可以生成一些复杂的查询。如果您可以使查询最终运行，则可以从查询计划中学到一些东西。

Object-relational mapping libraries can generate some complex queries. If you can get the query to eventually run, you may be able to learn something from the query plan.

另一种可能性是数据库统计信息不准确，从而导致了Derby选择一个糟糕的查询计划。最新版本的Derby改进了对重新计算统计信息的支持，这可以帮助优化程序选择更好的查询计划。

Another possibility is that the database statistics have become inaccurate, causing Derby to choose a poor query plan. Recent versions of Derby have improved support for re-computing the statistics, which can help the optimizer choose a better query plan.

Derby Tuning中包含大量信息 http://db.apache.org/derby/docs/10.8的指南/tuning/index.html

There is a wealth of information in the Derby Tuning guide at http://db.apache.org/derby/docs/10.8/tuning/index.html

这篇关于apache derby挂断执行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！