You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Amit Patel <ap...@us.axway.com> on 2006/10/11 20:48:18 UTC
Derby Optimizer Hangs
Hello,
I ran into a problem with Derby. When I issue a large SQL statement
with lots of WHERE conditions, the Derby optimizer looks like it's in an
infinite loop. A Thread dump of where it is stuck is shown below.
Also, a sample of the SQL that causes it to hang is also below.
Does anyone know if this is a bug or can I tune Derby to get around this
issue?
Thanks,
Amit Patel
Derby Version: 10.2.1.6
2006-10-11 18:30:03.666 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby -
10.2.1.6 - (452058): instance c065801d-010e-38a2-fae2-ffffb81c5a7c
on database directory C:\Source\CI5\Trunk\corelib\db\derby\Cyclone
Sample SQL that causes derby to hang:
SELECT DISTINCT t0.OID, t0.JDOVERSION, t0.BINARYCOLLABORATIONHASHCODE,
t0.CONSUMPTIONEXCHANGEPOINTOID, t0.CONSUMPTIONFILENAME,
t0.CONTENTMIMETYPE, t0.COREID, t0.CURRENTMESSAGEID,
t0.CURRENTPACKAGINGSTATEOID, t0.CURRENTSTATEOID, t0.DELIVEREDTIME,
t0.DIRECTION, t0.DOCUMENTCLASS, t0.ISDUPLICATE, t0.EDICONTROLID,
t0.EXPIRATIONTIME, t0.INTEGRATIONID, t0.ISCHILD, t0.ORIGINATIONTIME,
t0.PRODUCEDCOUNT, t0.PRODUCTIONFILENAME, t0.RECEIVERPARTYOID,
t0.RECEIVERPARTYID, t0.RECEIVERROUTINGID, t0.RECEIVERROUTINGIDTYPE,
t0.REFTOOID, t0.RESUBMITCOUNT, t0.RESUBMITINFO, t0.SENDERPARTYOID,
t0.SENDERPARTYID, t0.SENDERROUTINGID, t0.SENDERROUTINGIDTYPE, t0.TYPE
FROM DBA.MESSAGES t0 INNER JOIN DBA.CUSTOMDATA t1 ON t0.OID =
t1.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t2 ON t0.OID = t2.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t3 ON t0.OID = t3.MESSAGEOID INNER JOIN
DBA.CUSTOMDATA t4 ON t0.OID = t4.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t5
ON t0.OID = t5.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t6 ON t0.OID =
t6.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t7 ON t0.OID = t7.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t8 ON t0.OID = t8.MESSAGEOID INNER JOIN
DBA.CUSTOMDATA t9 ON t0.OID = t9.MESSAGEOID INNER JOIN DBA.CUSTOMDATA
t10 ON t0.OID = t10.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t11 ON t0.OID =
t11.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t12 ON t0.OID = t12.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t13 ON t0.OID = t13.MESSAGEOID INNER JOIN
DBA.MESSAGESTATES t14 ON t0.OID = t14.MESSAGEOID WHERE
(t0.RECEIVERROUTINGID = ? AND t0.SENDERROUTINGID = ? AND t0.DIRECTION =
? AND t0.ORIGINATIONTIME >= ? AND t0.ORIGINATIONTIME <= ? AND
t0.DOCUMENTCLASS = ? AND t0.CONTENTMIMETYPE = ? AND t1.NAME = ? AND
t1.DATAVALUE = ? AND t2.NAME = ? AND t2.DATAVALUE = ? AND t3.NAME = ?
AND t3.DATAVALUE = ? AND t4.NAME = ? AND t4.DATAVALUE = ? AND t5.NAME =
? AND t5.DATAVALUE = ? AND t6.NAME = ? AND t6.DATAVALUE = ? AND t7.NAME
= ? AND t7.DATAVALUE = ? AND t8.NAME = ? AND t8.DATAVALUE = ? AND
t9.NAME = ? AND t9.DATAVALUE = ? AND t10.NAME = ? AND t10.DATAVALUE = ?
AND t11.NAME = ? AND t11.DATAVALUE = ? AND t12.NAME = ? AND
t12.DATAVALUE = ? AND t13.NAME = ? AND t13.DATAVALUE = ? AND t14.TYPE =
? AND t14.URL = ?) ORDER BY t0.ORIGINATIONTIME DESC
Thread Dump of thread executing this query in Derby:
"JettySocketListener0-0" prio=6 tid=0x27939748 nid=0x838 runnable
[0x2a9ce000..0x2a9cfbe8]
at org.apache.derby.iapi.util.JBitSet.clearAll(Unknown
Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.initBaseT
ableVisitor(Unknown Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.valNodeRe
ferencesOptTable(Unknown Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.isQualifi
er(Unknown Source)
at
org.apache.derby.impl.sql.compile.PredicateList.hasOptimizableEquijoin(U
nknown Source)
at
org.apache.derby.impl.sql.compile.HashJoinStrategy.findHashKeyColumns(Un
known Source)
at
org.apache.derby.impl.sql.compile.HashJoinStrategy.feasible(Unknown
Source)
at
org.apache.derby.impl.sql.compile.FromTable.feasibleJoinStrategy(Unknown
Source)
at
org.apache.derby.impl.sql.compile.OptimizerImpl.costOptimizable(Unknown
Source)
at
org.apache.derby.impl.sql.compile.FromBaseTable.optimizeIt(Unknown
Source)
at
org.apache.derby.impl.sql.compile.ProjectRestrictNode.optimizeIt(Unknown
Source)
at
org.apache.derby.impl.sql.compile.OptimizerImpl.costPermutation(Unknown
Source)
at
org.apache.derby.impl.sql.compile.SelectNode.optimize(Unknown Source)
at
org.apache.derby.impl.sql.compile.DMLStatementNode.optimize(Unknown
Source)
at
org.apache.derby.impl.sql.compile.CursorNode.optimize(Unknown Source)
at
org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at
org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareI
nternalStatement(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
Source)
at
org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
- locked <0x0570e888> (a
org.apache.derby.impl.jdbc.EmbedConnection30)
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingCon
nection.java:382)
at
com.solarmetric.jdbc.PoolConnection.prepareStatement(PoolConnection.java
:114)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingCon
nection.java:380)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingCon
nection.java:380)
at
com.solarmetric.jdbc.LoggingConnectionDecorator$LoggingConnection.prepar
eStatement(LoggingConnectionDecorator.java:374)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingCon
nection.java:380)
at
kodo.jdbc.runtime.JDBCStoreManager$RefCountConnection.prepareStatement(J
DBCStoreManager.java:1645)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingCon
nection.java:366)
at
kodo.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:449)
at kodo.jdbc.sql.Select.execute(Select.java:1578)
at kodo.jdbc.sql.Select.execute(Select.java:1533)
at
kodo.jdbc.runtime.SelectResultObjectProvider.open(SelectResultObjectProv
ider.java:102)
at
com.solarmetric.rop.WindowResultList.<init>(WindowResultList.java:46)
at
kodo.jdbc.runtime.JDBCFetchConfigurationImpl.newResultList(JDBCFetchConf
igurationImpl.java:164)
at
kodo.runtime.CopyOnWriteFetchConfiguration.newResultList(CopyOnWriteFetc
hConfiguration.java:340)
at kodo.query.AbstractQuery.execute(AbstractQuery.java:1081)
at
kodo.query.AbstractQuery.executeWithMap(AbstractQuery.java:877)
at
com.cyclonecommerce.persistence.providers.jdo.JdoQuery.retrievePagedResu
lts(JdoQuery.java:201)
at
com.cyclonecommerce.persistence.query.AbstractQuery.executePagedQuery(Ab
stractQuery.java:156)
at
com.cyclonecommerce.persistence.PagedView.getPage(PagedView.java:82)
at
com.cyclonecommerce.ui.messages.MessageQuery.findMessages(MessageQuery.j
ava:1206)
at
com.cyclonecommerce.ui.messages.MessageMetadataQueryAction.serializedAct
(MessageMetadataQueryAction.java:97)
at
com.cyclonecommerce.ui.core.actions.CycloneAbstractAction.act(CycloneAbs
tractAction.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.ActTypeNode.invoke(Ac
tTypeNode.java:124)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.i
nvoke(PreparableMatchNode.java:130)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.MatchNode.invoke(Matc
hNode.java:107)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(P
ipelineNode.java:142)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(
PipelinesNode.java:92)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:234)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:176)
at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreePro
cessor.java:248)
at
org.apache.cocoon.components.treeprocessor.sitemap.MountNode.invoke(Moun
tNode.java:117)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.i
nvoke(PreparableMatchNode.java:130)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.MatchNode.invoke(Matc
hNode.java:107)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(P
ipelineNode.java:142)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(
PipelinesNode.java:92)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:234)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:176)
at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreePro
cessor.java:248)
at org.apache.cocoon.Cocoon.process(Cocoon.java:679)
at
org.apache.cocoon.servlet.CocoonServlet.service(CocoonServlet.java:1183)
at
javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at
org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428)
at
org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationH
andler.java:473)
at
org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568)
at
org.mortbay.http.HttpContext.handle(HttpContext.java:1530)
at
org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationCon
text.java:633)
at
org.mortbay.http.HttpContext.handle(HttpContext.java:1482)
at org.mortbay.http.HttpServer.service(HttpServer.java:909)
at
org.mortbay.http.HttpConnection.service(HttpConnection.java:817)
at
org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:983)
at
org.mortbay.http.HttpConnection.handle(HttpConnection.java:834)
at
org.mortbay.http.SocketListener.handleConnection(SocketListener.java:244
)
at
org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:359)
at
org.mortbay.util.ThreadPool$PoolThread.primRun(ThreadPool.java:537)
at
com.cyclonecommerce.lang.EventedThread.run(EventedThread.java:71)
RE: Derby Optimizer Hangs
Posted by Amit Patel <ap...@us.axway.com>.
Thanks Army.
It turns out you are right. I took several thread dumps and the
execution was in different places within the optimizer. It looks like
it's just spending a lot of time figuring out what's best. I'll try the
FIXED option, but I'm already in the process of re-writing the query to
lower the number of joins.
Amit
-----Original Message-----
From: Army [mailto:qozinx@gmail.com]
Sent: Wednesday, October 11, 2006 2:20 PM
To: Derby Discussion
Subject: Re: Derby Optimizer Hangs
Amit Patel wrote:
> I ran into a problem with Derby. When I issue a large SQL statement
> with lots of WHERE conditions, the Derby optimizer looks like it's in
an
> infinite loop.
Do you know how long the query was running before you took the thread
dump? If
you repeat the process of running the query and taking the thread dump,
do the
threads look the same every time?
> Does anyone know if this is a bug or can I tune Derby to get around
this
> issue?
It's hard to say with just the info given. From a quick glance it looks
like
your joining 14 tables in this query. If that's true, the Derby
optimizer is
going to try out all possible orderings for the 14 tables until it
either
finishes or "times out". There are known issues in this area that could
cause
the optimizer to spend a *lot* of time trying to figure out what the
best plan
is--see for example DERBY-1906, DERBY-1907, DERBY-1905. In such a case
it might
look like the optimizer has hung when in fact it's just busy trying out
all of
the various combinations.
Since you're using 10.2, one thing you can do is use optimizer overrides
to tell
the optimizer to skip optimization of join order--i.e. to just join the
tables
in the order in which they appear in the query. To do that you can
specify the
"joinOrder" optimizer override as follows:
SELECT DISTINCT t0.OID, t0.JDOVERSION,
t0.BINARYCOLLABORATIONHASHCODE,
...
FROM --DERBY-PROPERTIES joinOrder=FIXED
DBA.MESSAGES t0 INNER JOIN DBA.CUSTOMDATA t1 ON t0.OID =
t1.MESSAGEOID
...
Note that you must have an end-of-line after specifying the
joinOrder=FIXED
property.
If the query completes in a reasonable time with this property set, then
it's
probably the case that, in your original query, the optimizer is
spending time
trying out all of the various join orders for the 14 tables.
If the query still "hangs" with this property set, then it might be good
if you can:
1. Reduce the number of tables and/or WHERE predicates to find the
minimum
number of tables/predicates required to reproduce the hang.
2. Take note of how long the query actually runs. Is it minutes,
hours, days?
3. See the following wiki page for additional suggestions:
http://wiki.apache.org/db-derby/PerformanceDiagnosisTips
As a general rule, more info is better. The above wiki page describes
the kind
of information that is most helpful in debugging situations like these.
If you
can start there, someone (perhaps even you!) may be able to provide
further
insight as to what's happening in this query...
Army
Re: Derby Optimizer Hangs
Posted by Army <qo...@gmail.com>.
Amit Patel wrote:
> I ran into a problem with Derby. When I issue a large SQL statement
> with lots of WHERE conditions, the Derby optimizer looks like it's in an
> infinite loop.
Do you know how long the query was running before you took the thread dump? If
you repeat the process of running the query and taking the thread dump, do the
threads look the same every time?
> Does anyone know if this is a bug or can I tune Derby to get around this
> issue?
It's hard to say with just the info given. From a quick glance it looks like
your joining 14 tables in this query. If that's true, the Derby optimizer is
going to try out all possible orderings for the 14 tables until it either
finishes or "times out". There are known issues in this area that could cause
the optimizer to spend a *lot* of time trying to figure out what the best plan
is--see for example DERBY-1906, DERBY-1907, DERBY-1905. In such a case it might
look like the optimizer has hung when in fact it's just busy trying out all of
the various combinations.
Since you're using 10.2, one thing you can do is use optimizer overrides to tell
the optimizer to skip optimization of join order--i.e. to just join the tables
in the order in which they appear in the query. To do that you can specify the
"joinOrder" optimizer override as follows:
SELECT DISTINCT t0.OID, t0.JDOVERSION, t0.BINARYCOLLABORATIONHASHCODE,
...
FROM --DERBY-PROPERTIES joinOrder=FIXED
DBA.MESSAGES t0 INNER JOIN DBA.CUSTOMDATA t1 ON t0.OID = t1.MESSAGEOID
...
Note that you must have an end-of-line after specifying the joinOrder=FIXED
property.
If the query completes in a reasonable time with this property set, then it's
probably the case that, in your original query, the optimizer is spending time
trying out all of the various join orders for the 14 tables.
If the query still "hangs" with this property set, then it might be good if you can:
1. Reduce the number of tables and/or WHERE predicates to find the minimum
number of tables/predicates required to reproduce the hang.
2. Take note of how long the query actually runs. Is it minutes, hours, days?
3. See the following wiki page for additional suggestions:
http://wiki.apache.org/db-derby/PerformanceDiagnosisTips
As a general rule, more info is better. The above wiki page describes the kind
of information that is most helpful in debugging situations like these. If you
can start there, someone (perhaps even you!) may be able to provide further
insight as to what's happening in this query...
Army
RE: Derby Optimizer Hangs
Posted by Fabio Porto <fa...@gmail.com>.
Hi,
It is hard to say whether the query was really semantically wrong or not
(either Anding or Oring the predicates) without a complete table schema.
As it stands in Amit's message, it might want to pick
different subsets of CustomData on each sub query (as long as MESSAGEOID is
not a primary key of it). It would mainly indicate that the same messageOID
appears for different name/value pairs.
If this is the right interpretation, a simple statement like the one below
won't do it:
t1.Name in ('n1', 'n2',.,'nm') and t1.value in ('v1', 'v2','v3',..,'vm').
cheers,
Fabio.
_____
From: Michael Segel [mailto:msegel@segel.com]
Sent: jeudi, 12. octobre 2006 00:04
To: 'Derby Discussion'
Subject: RE: Derby Optimizer Hangs
That's good to know that it was auto gen'd code.
If someone sat down and wrote that by hand. It would be a case for
justifiable homicide. ;-)
I think a good rule of thumb is GIGO (Garbage In Garbage Out).
I mean just because you could write something that is syntactically correct,
doesn't mean that it's a good idea.
I think that Army did raise a good point.
More info is good.
Formatting the query so that its human readable really helps, as well as
adding an English description of what you are attempting to do.
I'm sure you're going to see something returned a tad bit faster. ;-)
-G
_____
From: Amit Patel [mailto:apatel@us.axway.com]
Sent: Wednesday, October 11, 2006 4:47 PM
To: Derby Discussion; msegel@segel.com
Subject: RE: Derby Optimizer Hangs
Yes, I'm in the process of ripping out the brain dead code that auto
generated that sql. The resulting sql will not need to do some many joins.
I guess my question/point was that even though the sql was terrible, why was
Derby Optimizer taking so long to chew on the sql.
Thanks for the sql advice.
Amit
_____
From: Michael Segel [mailto:msegel@segel.com]
Sent: Wednesday, October 11, 2006 2:43 PM
To: 'Derby Discussion'; msegel@segel.com
Subject: RE: Derby Optimizer Hangs
Just a follow up.
Looking at this, you could be trying to say
Join these two tables where (customerdata name = ? AND value = ?) OR
(customerdata name = ? AND value =?)
In that case you'll have a query that looks like
SELECT t0.*
FROM dba.messages t0
INNER JOIN dba.customdata t1 ON t0.oid = t1.messageoid
WHERE t0.<Column Name> = ?
.
AND( ( t1.name = ? AND t1.datavalue =?)
OR( t1.name = ? AND t1.datavalue =?)
OR(( t1.name = ? AND t1.datavalue =?)
.
)
ORDER BY .
Now I'm sure that there's a better way, but you are not doing so many joins.
HTH
-G
_____
From: Michael Segel [mailto:msegel@segel.com] On Behalf Of derby@segel.com
Sent: Wednesday, October 11, 2006 4:37 PM
To: 'Derby Discussion'
Subject: RE: Derby Optimizer Hangs
Well, with a lack of formatting its hard to read your query.
However. Lets clean it up.
Lets see if this works right.
SELECT DISTINCT t0.OID, t0.JDOVERSION, t0.BINARYCOLLABORATIONHASHCODE,
t0.CONSUMPTIONEXCHANGEPOINTOID,
t0.CONSUMPTIONFILENAME, t0.CONTENTMIMETYPE,
t0.COREID, t0.CURRENTMESSAGEID,
t0.CURRENTPACKAGINGSTATEOID,
t0.CURRENTSTATEOID, t0.DELIVEREDTIME, t0.DIRECTION,
t0.DOCUMENTCLASS, t0.ISDUPLICATE,
t0.EDICONTROLID, t0.EXPIRATIONTIME, t0.INTEGRATIONID,
t0.ISCHILD, t0.ORIGINATIONTIME,
t0.PRODUCEDCOUNT, t0.PRODUCTIONFILENAME, t0.RECEIVERPARTYOID,
t0.RECEIVERPARTYID, t0.RECEIVERROUTINGID,
t0.RECEIVERROUTINGIDTYPE, t0.REFTOOID,
t0.RESUBMITCOUNT, t0.RESUBMITINFO,
t0.SENDERPARTYOID, t0.SENDERPARTYID, t0.SENDERROUTINGID,
t0.SENDERROUTINGIDTYPE, t0.TYPE
FROM DBA.MESSAGES t0
INNER JOIN DBA.CUSTOMDATA t1 ON t0.OID = t1.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t2 ON t0.OID = t2.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t3 ON t0.OID = t3.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t4 ON t0.OID = t4.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t5 ON t0.OID = t5.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t6 ON t0.OID = t6.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t7 ON t0.OID = t7.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t8 ON t0.OID = t8.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t9 ON t0.OID = t9.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t10 ON t0.OID = t10.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t11 ON t0.OID = t11.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t12 ON t0.OID = t12.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t13 ON t0.OID = t13.MESSAGEOID
INNER JOIN DBA.MESSAGESTATES t14 ON t0.OID = t14.MESSAGEOID
WHERE (t0.RECEIVERROUTINGID = ?
AND t0.SENDERROUTINGID = ?
AND t0.DIRECTION = ?
AND t0.ORIGINATIONTIME >= ?
AND t0.ORIGINATIONTIME <= ?
AND t0.DOCUMENTCLASS = ?
AND t0.CONTENTMIMETYPE = ?
AND t1.NAME = ?
AND t1.DATAVALUE = ?
AND t2.NAME = ?
AND t2.DATAVALUE = ?
AND t3.NAME = ?
[SNIP]
Ok, do you start to see the problem here?
Did you actually write this or are you using some brain dead auto generated
code?
Hint:
Try something like this.
SELECT t0.*
FROM dba.messages t0
INNER JOIN dba.customdata t1 ON t0.oid = t1.messageoid
WHERE t0.<Column Name> = ?
.
AND t1.name in (?,?,.)
AND t1.datavalue in (?,?,?,.)
Note the following:
1) I'm not going to write out your query long hand.
2) If you're writing out the in statement long hand the number of
values is going to be static
If you're going to do this dynamically, then at run time you can build the
query and then execute it.
In addition,
There is a known issue with the IN predicate.
HTH.
_____
From: Amit Patel [mailto:apatel@us.axway.com]
Sent: Wednesday, October 11, 2006 1:48 PM
To: derby-user@db.apache.org
Subject: Derby Optimizer Hangs
Hello,
I ran into a problem with Derby. When I issue a large SQL statement with
lots of WHERE conditions, the Derby optimizer looks like it's in an infinite
loop. A Thread dump of where it is stuck is shown below. Also, a sample of
the SQL that causes it to hang is also below.
Does anyone know if this is a bug or can I tune Derby to get around this
issue?
Thanks,
Amit Patel
Derby Version: 10.2.1.6
2006-10-11 18:30:03.666 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby -
10.2.1.6 - (452058): instance c065801d-010e-38a2-fae2-ffffb81c5a7c
on database directory C:\Source\CI5\Trunk\corelib\db\derby\Cyclone
Sample SQL that causes derby to hang:
SELECT DISTINCT t0.OID, t0.JDOVERSION, t0.BINARYCOLLABORATIONHASHCODE,
t0.CONSUMPTIONEXCHANGEPOINTOID, t0.CONSUMPTIONFILENAME, t0.CONTENTMIMETYPE,
t0.COREID, t0.CURRENTMESSAGEID, t0.CURRENTPACKAGINGSTATEOID,
t0.CURRENTSTATEOID, t0.DELIVEREDTIME, t0.DIRECTION, t0.DOCUMENTCLASS,
t0.ISDUPLICATE, t0.EDICONTROLID, t0.EXPIRATIONTIME, t0.INTEGRATIONID,
t0.ISCHILD, t0.ORIGINATIONTIME, t0.PRODUCEDCOUNT, t0.PRODUCTIONFILENAME,
t0.RECEIVERPARTYOID, t0.RECEIVERPARTYID, t0.RECEIVERROUTINGID,
t0.RECEIVERROUTINGIDTYPE, t0.REFTOOID, t0.RESUBMITCOUNT, t0.RESUBMITINFO,
t0.SENDERPARTYOID, t0.SENDERPARTYID, t0.SENDERROUTINGID,
t0.SENDERROUTINGIDTYPE, t0.TYPE FROM DBA.MESSAGES t0 INNER JOIN
DBA.CUSTOMDATA t1 ON t0.OID = t1.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t2 ON
t0.OID = t2.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t3 ON t0.OID =
t3.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t4 ON t0.OID = t4.MESSAGEOID INNER
JOIN DBA.CUSTOMDATA t5 ON t0.OID = t5.MESSAGEOID INNER JOIN DBA.CUSTOMDATA
t6 ON t0.OID = t6.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t7 ON t0.OID =
t7.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t8 ON t0.OID = t8.MESSAGEOID INNER
JOIN DBA.CUSTOMDATA t9 ON t0.OID = t9.MESSAGEOID INNER JOIN DBA.CUSTOMDATA
t10 ON t0.OID = t10.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t11 ON t0.OID =
t11.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t12 ON t0.OID = t12.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t13 ON t0.OID = t13.MESSAGEOID INNER JOIN
DBA.MESSAGESTATES t14 ON t0.OID = t14.MESSAGEOID WHERE (t0.RECEIVERROUTINGID
= ? AND t0.SENDERROUTINGID = ? AND t0.DIRECTION = ? AND t0.ORIGINATIONTIME
>= ? AND t0.ORIGINATIONTIME <= ? AND t0.DOCUMENTCLASS = ? AND
t0.CONTENTMIMETYPE = ? AND t1.NAME = ? AND t1.DATAVALUE = ? AND t2.NAME = ?
AND t2.DATAVALUE = ? AND t3.NAME = ? AND t3.DATAVALUE = ? AND t4.NAME = ?
AND t4.DATAVALUE = ? AND t5.NAME = ? AND t5.DATAVALUE = ? AND t6.NAME = ?
AND t6.DATAVALUE = ? AND t7.NAME = ? AND t7.DATAVALUE = ? AND t8.NAME = ?
AND t8.DATAVALUE = ? AND t9.NAME = ? AND t9.DATAVALUE = ? AND t10.NAME = ?
AND t10.DATAVALUE = ? AND t11.NAME = ? AND t11.DATAVALUE = ? AND t12.NAME =
? AND t12.DATAVALUE = ? AND t13.NAME = ? AND t13.DATAVALUE = ? AND t14.TYPE
= ? AND t14.URL = ?) ORDER BY t0.ORIGINATIONTIME DESC
Thread Dump of thread executing this query in Derby:
"JettySocketListener0-0" prio=6 tid=0x27939748 nid=0x838 runnable
[0x2a9ce000..0x2a9cfbe8]
at org.apache.derby.iapi.util.JBitSet.clearAll(Unknown Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.initBaseTable
Visitor(Unknown Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.valNodeRefere
ncesOptTable(Unknown Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.isQualifier(U
nknown Source)
at
org.apache.derby.impl.sql.compile.PredicateList.hasOptimizableEquijoin(Unkno
wn Source)
at
org.apache.derby.impl.sql.compile.HashJoinStrategy.findHashKeyColumns(Unknow
n Source)
at
org.apache.derby.impl.sql.compile.HashJoinStrategy.feasible(Unknown Source)
at
org.apache.derby.impl.sql.compile.FromTable.feasibleJoinStrategy(Unknown
Source)
at
org.apache.derby.impl.sql.compile.OptimizerImpl.costOptimizable(Unknown
Source)
at
org.apache.derby.impl.sql.compile.FromBaseTable.optimizeIt(Unknown Source)
at
org.apache.derby.impl.sql.compile.ProjectRestrictNode.optimizeIt(Unknown
Source)
at
org.apache.derby.impl.sql.compile.OptimizerImpl.costPermutation(Unknown
Source)
at org.apache.derby.impl.sql.compile.SelectNode.optimize(Unknown
Source)
at
org.apache.derby.impl.sql.compile.DMLStatementNode.optimize(Unknown Source)
at org.apache.derby.impl.sql.compile.CursorNode.optimize(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown
Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInter
nalStatement(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at
org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
- locked <0x0570e888> (a
org.apache.derby.impl.jdbc.EmbedConnection30)
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:382)
at
com.solarmetric.jdbc.PoolConnection.prepareStatement(PoolConnection.java:114
)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:380)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:380)
at
com.solarmetric.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareSta
tement(LoggingConnectionDecorator.java:374)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:380)
at
kodo.jdbc.runtime.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCS
toreManager.java:1645)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:366)
at kodo.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:449)
at kodo.jdbc.sql.Select.execute(Select.java:1578)
at kodo.jdbc.sql.Select.execute(Select.java:1533)
at
kodo.jdbc.runtime.SelectResultObjectProvider.open(SelectResultObjectProvider
.java:102)
at
com.solarmetric.rop.WindowResultList.<init>(WindowResultList.java:46)
at
kodo.jdbc.runtime.JDBCFetchConfigurationImpl.newResultList(JDBCFetchConfigur
ationImpl.java:164)
at
kodo.runtime.CopyOnWriteFetchConfiguration.newResultList(CopyOnWriteFetchCon
figuration.java:340)
at kodo.query.AbstractQuery.execute(AbstractQuery.java:1081)
at
kodo.query.AbstractQuery.executeWithMap(AbstractQuery.java:877)
at
com.cyclonecommerce.persistence.providers.jdo.JdoQuery.retrievePagedResults(
JdoQuery.java:201)
at
com.cyclonecommerce.persistence.query.AbstractQuery.executePagedQuery(Abstra
ctQuery.java:156)
at
com.cyclonecommerce.persistence.PagedView.getPage(PagedView.java:82)
at
com.cyclonecommerce.ui.messages.MessageQuery.findMessages(MessageQuery.java:
1206)
at
com.cyclonecommerce.ui.messages.MessageMetadataQueryAction.serializedAct(Mes
sageMetadataQueryAction.java:97)
at
com.cyclonecommerce.ui.core.actions.CycloneAbstractAction.act(CycloneAbstrac
tAction.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.ActTypeNode.invoke(ActTyp
eNode.java:124)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.invok
e(PreparableMatchNode.java:130)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.MatchNode.invoke(MatchNod
e.java:107)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(Pipel
ineNode.java:142)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(Pipe
linesNode.java:92)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process(Con
creteTreeProcessor.java:234)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process(Con
creteTreeProcessor.java:176)
at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcess
or.java:248)
at
org.apache.cocoon.components.treeprocessor.sitemap.MountNode.invoke(MountNod
e.java:117)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.invok
e(PreparableMatchNode.java:130)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.MatchNode.invoke(MatchNod
e.java:107)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(Pipel
ineNode.java:142)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(Pipe
linesNode.java:92)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process(Con
creteTreeProcessor.java:234)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process(Con
creteTreeProcessor.java:176)
at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcess
or.java:248)
at org.apache.cocoon.Cocoon.process(Cocoon.java:679)
at
org.apache.cocoon.servlet.CocoonServlet.service(CocoonServlet.java:1183)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at
org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428)
at
org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationHandl
er.java:473)
at
org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1530)
at
org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationContext
.java:633)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1482)
at org.mortbay.http.HttpServer.service(HttpServer.java:909)
at
org.mortbay.http.HttpConnection.service(HttpConnection.java:817)
at
org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:983)
at
org.mortbay.http.HttpConnection.handle(HttpConnection.java:834)
at
org.mortbay.http.SocketListener.handleConnection(SocketListener.java:244)
at
org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:359)
at
org.mortbay.util.ThreadPool$PoolThread.primRun(ThreadPool.java:537)
at
com.cyclonecommerce.lang.EventedThread.run(EventedThread.java:71)
RE: Derby Optimizer Hangs
Posted by Michael Segel <ms...@segel.com>.
That's good to know that it was auto gen'd code.
If someone sat down and wrote that by hand. It would be a case for
justifiable homicide. ;-)
I think a good rule of thumb is GIGO (Garbage In Garbage Out).
I mean just because you could write something that is syntactically correct,
doesn't mean that it's a good idea.
I think that Army did raise a good point.
More info is good.
Formatting the query so that its human readable really helps, as well as
adding an English description of what you are attempting to do.
I'm sure you're going to see something returned a tad bit faster. ;-)
-G
_____
From: Amit Patel [mailto:apatel@us.axway.com]
Sent: Wednesday, October 11, 2006 4:47 PM
To: Derby Discussion; msegel@segel.com
Subject: RE: Derby Optimizer Hangs
Yes, I'm in the process of ripping out the brain dead code that auto
generated that sql. The resulting sql will not need to do some many joins.
I guess my question/point was that even though the sql was terrible, why was
Derby Optimizer taking so long to chew on the sql.
Thanks for the sql advice.
Amit
_____
From: Michael Segel [mailto:msegel@segel.com]
Sent: Wednesday, October 11, 2006 2:43 PM
To: 'Derby Discussion'; msegel@segel.com
Subject: RE: Derby Optimizer Hangs
Just a follow up.
Looking at this, you could be trying to say
Join these two tables where (customerdata name = ? AND value = ?) OR
(customerdata name = ? AND value =?)
In that case you'll have a query that looks like
SELECT t0.*
FROM dba.messages t0
INNER JOIN dba.customdata t1 ON t0.oid = t1.messageoid
WHERE t0.<Column Name> = ?
.
AND( ( t1.name = ? AND t1.datavalue =?)
OR( t1.name = ? AND t1.datavalue =?)
OR(( t1.name = ? AND t1.datavalue =?)
.
)
ORDER BY .
Now I'm sure that there's a better way, but you are not doing so many joins.
HTH
-G
_____
From: Michael Segel [mailto:msegel@segel.com] On Behalf Of derby@segel.com
Sent: Wednesday, October 11, 2006 4:37 PM
To: 'Derby Discussion'
Subject: RE: Derby Optimizer Hangs
Well, with a lack of formatting its hard to read your query.
However. Lets clean it up.
Lets see if this works right.
SELECT DISTINCT t0.OID, t0.JDOVERSION, t0.BINARYCOLLABORATIONHASHCODE,
t0.CONSUMPTIONEXCHANGEPOINTOID,
t0.CONSUMPTIONFILENAME, t0.CONTENTMIMETYPE,
t0.COREID, t0.CURRENTMESSAGEID,
t0.CURRENTPACKAGINGSTATEOID,
t0.CURRENTSTATEOID, t0.DELIVEREDTIME, t0.DIRECTION,
t0.DOCUMENTCLASS, t0.ISDUPLICATE,
t0.EDICONTROLID, t0.EXPIRATIONTIME, t0.INTEGRATIONID,
t0.ISCHILD, t0.ORIGINATIONTIME,
t0.PRODUCEDCOUNT, t0.PRODUCTIONFILENAME, t0.RECEIVERPARTYOID,
t0.RECEIVERPARTYID, t0.RECEIVERROUTINGID,
t0.RECEIVERROUTINGIDTYPE, t0.REFTOOID,
t0.RESUBMITCOUNT, t0.RESUBMITINFO,
t0.SENDERPARTYOID, t0.SENDERPARTYID, t0.SENDERROUTINGID,
t0.SENDERROUTINGIDTYPE, t0.TYPE
FROM DBA.MESSAGES t0
INNER JOIN DBA.CUSTOMDATA t1 ON t0.OID = t1.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t2 ON t0.OID = t2.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t3 ON t0.OID = t3.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t4 ON t0.OID = t4.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t5 ON t0.OID = t5.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t6 ON t0.OID = t6.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t7 ON t0.OID = t7.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t8 ON t0.OID = t8.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t9 ON t0.OID = t9.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t10 ON t0.OID = t10.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t11 ON t0.OID = t11.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t12 ON t0.OID = t12.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t13 ON t0.OID = t13.MESSAGEOID
INNER JOIN DBA.MESSAGESTATES t14 ON t0.OID = t14.MESSAGEOID
WHERE (t0.RECEIVERROUTINGID = ?
AND t0.SENDERROUTINGID = ?
AND t0.DIRECTION = ?
AND t0.ORIGINATIONTIME >= ?
AND t0.ORIGINATIONTIME <= ?
AND t0.DOCUMENTCLASS = ?
AND t0.CONTENTMIMETYPE = ?
AND t1.NAME = ?
AND t1.DATAVALUE = ?
AND t2.NAME = ?
AND t2.DATAVALUE = ?
AND t3.NAME = ?
[SNIP]
Ok, do you start to see the problem here?
Did you actually write this or are you using some brain dead auto generated
code?
Hint:
Try something like this.
SELECT t0.*
FROM dba.messages t0
INNER JOIN dba.customdata t1 ON t0.oid = t1.messageoid
WHERE t0.<Column Name> = ?
.
AND t1.name in (?,?,.)
AND t1.datavalue in (?,?,?,.)
Note the following:
1) I'm not going to write out your query long hand.
2) If you're writing out the in statement long hand the number of
values is going to be static
If you're going to do this dynamically, then at run time you can build the
query and then execute it.
In addition,
There is a known issue with the IN predicate.
HTH.
_____
From: Amit Patel [mailto:apatel@us.axway.com]
Sent: Wednesday, October 11, 2006 1:48 PM
To: derby-user@db.apache.org
Subject: Derby Optimizer Hangs
Hello,
I ran into a problem with Derby. When I issue a large SQL statement with
lots of WHERE conditions, the Derby optimizer looks like it's in an infinite
loop. A Thread dump of where it is stuck is shown below. Also, a sample of
the SQL that causes it to hang is also below.
Does anyone know if this is a bug or can I tune Derby to get around this
issue?
Thanks,
Amit Patel
Derby Version: 10.2.1.6
2006-10-11 18:30:03.666 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby -
10.2.1.6 - (452058): instance c065801d-010e-38a2-fae2-ffffb81c5a7c
on database directory C:\Source\CI5\Trunk\corelib\db\derby\Cyclone
Sample SQL that causes derby to hang:
SELECT DISTINCT t0.OID, t0.JDOVERSION, t0.BINARYCOLLABORATIONHASHCODE,
t0.CONSUMPTIONEXCHANGEPOINTOID, t0.CONSUMPTIONFILENAME, t0.CONTENTMIMETYPE,
t0.COREID, t0.CURRENTMESSAGEID, t0.CURRENTPACKAGINGSTATEOID,
t0.CURRENTSTATEOID, t0.DELIVEREDTIME, t0.DIRECTION, t0.DOCUMENTCLASS,
t0.ISDUPLICATE, t0.EDICONTROLID, t0.EXPIRATIONTIME, t0.INTEGRATIONID,
t0.ISCHILD, t0.ORIGINATIONTIME, t0.PRODUCEDCOUNT, t0.PRODUCTIONFILENAME,
t0.RECEIVERPARTYOID, t0.RECEIVERPARTYID, t0.RECEIVERROUTINGID,
t0.RECEIVERROUTINGIDTYPE, t0.REFTOOID, t0.RESUBMITCOUNT, t0.RESUBMITINFO,
t0.SENDERPARTYOID, t0.SENDERPARTYID, t0.SENDERROUTINGID,
t0.SENDERROUTINGIDTYPE, t0.TYPE FROM DBA.MESSAGES t0 INNER JOIN
DBA.CUSTOMDATA t1 ON t0.OID = t1.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t2 ON
t0.OID = t2.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t3 ON t0.OID =
t3.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t4 ON t0.OID = t4.MESSAGEOID INNER
JOIN DBA.CUSTOMDATA t5 ON t0.OID = t5.MESSAGEOID INNER JOIN DBA.CUSTOMDATA
t6 ON t0.OID = t6.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t7 ON t0.OID =
t7.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t8 ON t0.OID = t8.MESSAGEOID INNER
JOIN DBA.CUSTOMDATA t9 ON t0.OID = t9.MESSAGEOID INNER JOIN DBA.CUSTOMDATA
t10 ON t0.OID = t10.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t11 ON t0.OID =
t11.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t12 ON t0.OID = t12.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t13 ON t0.OID = t13.MESSAGEOID INNER JOIN
DBA.MESSAGESTATES t14 ON t0.OID = t14.MESSAGEOID WHERE (t0.RECEIVERROUTINGID
= ? AND t0.SENDERROUTINGID = ? AND t0.DIRECTION = ? AND t0.ORIGINATIONTIME
>= ? AND t0.ORIGINATIONTIME <= ? AND t0.DOCUMENTCLASS = ? AND
t0.CONTENTMIMETYPE = ? AND t1.NAME = ? AND t1.DATAVALUE = ? AND t2.NAME = ?
AND t2.DATAVALUE = ? AND t3.NAME = ? AND t3.DATAVALUE = ? AND t4.NAME = ?
AND t4.DATAVALUE = ? AND t5.NAME = ? AND t5.DATAVALUE = ? AND t6.NAME = ?
AND t6.DATAVALUE = ? AND t7.NAME = ? AND t7.DATAVALUE = ? AND t8.NAME = ?
AND t8.DATAVALUE = ? AND t9.NAME = ? AND t9.DATAVALUE = ? AND t10.NAME = ?
AND t10.DATAVALUE = ? AND t11.NAME = ? AND t11.DATAVALUE = ? AND t12.NAME =
? AND t12.DATAVALUE = ? AND t13.NAME = ? AND t13.DATAVALUE = ? AND t14.TYPE
= ? AND t14.URL = ?) ORDER BY t0.ORIGINATIONTIME DESC
Thread Dump of thread executing this query in Derby:
"JettySocketListener0-0" prio=6 tid=0x27939748 nid=0x838 runnable
[0x2a9ce000..0x2a9cfbe8]
at org.apache.derby.iapi.util.JBitSet.clearAll(Unknown Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.initBaseTable
Visitor(Unknown Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.valNodeRefere
ncesOptTable(Unknown Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.isQualifier(U
nknown Source)
at
org.apache.derby.impl.sql.compile.PredicateList.hasOptimizableEquijoin(Unkno
wn Source)
at
org.apache.derby.impl.sql.compile.HashJoinStrategy.findHashKeyColumns(Unknow
n Source)
at
org.apache.derby.impl.sql.compile.HashJoinStrategy.feasible(Unknown Source)
at
org.apache.derby.impl.sql.compile.FromTable.feasibleJoinStrategy(Unknown
Source)
at
org.apache.derby.impl.sql.compile.OptimizerImpl.costOptimizable(Unknown
Source)
at
org.apache.derby.impl.sql.compile.FromBaseTable.optimizeIt(Unknown Source)
at
org.apache.derby.impl.sql.compile.ProjectRestrictNode.optimizeIt(Unknown
Source)
at
org.apache.derby.impl.sql.compile.OptimizerImpl.costPermutation(Unknown
Source)
at org.apache.derby.impl.sql.compile.SelectNode.optimize(Unknown
Source)
at
org.apache.derby.impl.sql.compile.DMLStatementNode.optimize(Unknown Source)
at org.apache.derby.impl.sql.compile.CursorNode.optimize(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown
Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInter
nalStatement(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at
org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
- locked <0x0570e888> (a
org.apache.derby.impl.jdbc.EmbedConnection30)
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:382)
at
com.solarmetric.jdbc.PoolConnection.prepareStatement(PoolConnection.java:114
)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:380)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:380)
at
com.solarmetric.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareSta
tement(LoggingConnectionDecorator.java:374)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:380)
at
kodo.jdbc.runtime.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCS
toreManager.java:1645)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:366)
at kodo.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:449)
at kodo.jdbc.sql.Select.execute(Select.java:1578)
at kodo.jdbc.sql.Select.execute(Select.java:1533)
at
kodo.jdbc.runtime.SelectResultObjectProvider.open(SelectResultObjectProvider
.java:102)
at
com.solarmetric.rop.WindowResultList.<init>(WindowResultList.java:46)
at
kodo.jdbc.runtime.JDBCFetchConfigurationImpl.newResultList(JDBCFetchConfigur
ationImpl.java:164)
at
kodo.runtime.CopyOnWriteFetchConfiguration.newResultList(CopyOnWriteFetchCon
figuration.java:340)
at kodo.query.AbstractQuery.execute(AbstractQuery.java:1081)
at
kodo.query.AbstractQuery.executeWithMap(AbstractQuery.java:877)
at
com.cyclonecommerce.persistence.providers.jdo.JdoQuery.retrievePagedResults(
JdoQuery.java:201)
at
com.cyclonecommerce.persistence.query.AbstractQuery.executePagedQuery(Abstra
ctQuery.java:156)
at
com.cyclonecommerce.persistence.PagedView.getPage(PagedView.java:82)
at
com.cyclonecommerce.ui.messages.MessageQuery.findMessages(MessageQuery.java:
1206)
at
com.cyclonecommerce.ui.messages.MessageMetadataQueryAction.serializedAct(Mes
sageMetadataQueryAction.java:97)
at
com.cyclonecommerce.ui.core.actions.CycloneAbstractAction.act(CycloneAbstrac
tAction.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.ActTypeNode.invoke(ActTyp
eNode.java:124)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.invok
e(PreparableMatchNode.java:130)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.MatchNode.invoke(MatchNod
e.java:107)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(Pipel
ineNode.java:142)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(Pipe
linesNode.java:92)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process(Con
creteTreeProcessor.java:234)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process(Con
creteTreeProcessor.java:176)
at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcess
or.java:248)
at
org.apache.cocoon.components.treeprocessor.sitemap.MountNode.invoke(MountNod
e.java:117)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.invok
e(PreparableMatchNode.java:130)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.MatchNode.invoke(MatchNod
e.java:107)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(Pipel
ineNode.java:142)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(Pipe
linesNode.java:92)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process(Con
creteTreeProcessor.java:234)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process(Con
creteTreeProcessor.java:176)
at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcess
or.java:248)
at org.apache.cocoon.Cocoon.process(Cocoon.java:679)
at
org.apache.cocoon.servlet.CocoonServlet.service(CocoonServlet.java:1183)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at
org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428)
at
org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationHandl
er.java:473)
at
org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1530)
at
org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationContext
.java:633)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1482)
at org.mortbay.http.HttpServer.service(HttpServer.java:909)
at
org.mortbay.http.HttpConnection.service(HttpConnection.java:817)
at
org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:983)
at
org.mortbay.http.HttpConnection.handle(HttpConnection.java:834)
at
org.mortbay.http.SocketListener.handleConnection(SocketListener.java:244)
at
org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:359)
at
org.mortbay.util.ThreadPool$PoolThread.primRun(ThreadPool.java:537)
at
com.cyclonecommerce.lang.EventedThread.run(EventedThread.java:71)
RE: Derby Optimizer Hangs
Posted by Amit Patel <ap...@us.axway.com>.
Yes, I'm in the process of ripping out the brain dead code that auto
generated that sql. The resulting sql will not need to do some many
joins. I guess my question/point was that even though the sql was
terrible, why was Derby Optimizer taking so long to chew on the sql.
Thanks for the sql advice.
Amit
________________________________
From: Michael Segel [mailto:msegel@segel.com]
Sent: Wednesday, October 11, 2006 2:43 PM
To: 'Derby Discussion'; msegel@segel.com
Subject: RE: Derby Optimizer Hangs
Just a follow up...
Looking at this, you could be trying to say
Join these two tables where (customerdata name = ? AND value = ?) OR
(customerdata name = ? AND value =?)
In that case you'll have a query that looks like
SELECT t0.*
FROM dba.messages t0
INNER JOIN dba.customdata t1 ON t0.oid = t1.messageoid
WHERE t0.<Column Name> = ?
...
AND( ( t1.name = ? AND t1.datavalue =?)
OR( t1.name = ? AND t1.datavalue =?)
OR(( t1.name = ? AND t1.datavalue =?)
...
)
ORDER BY ...
Now I'm sure that there's a better way, but you are not doing so many
joins.
HTH
-G
________________________________
From: Michael Segel [mailto:msegel@segel.com] On Behalf Of
derby@segel.com
Sent: Wednesday, October 11, 2006 4:37 PM
To: 'Derby Discussion'
Subject: RE: Derby Optimizer Hangs
Well, with a lack of formatting its hard to read your query.
However... Lets clean it up...
Lets see if this works right...
SELECT DISTINCT t0.OID, t0.JDOVERSION, t0.BINARYCOLLABORATIONHASHCODE,
t0.CONSUMPTIONEXCHANGEPOINTOID,
t0.CONSUMPTIONFILENAME, t0.CONTENTMIMETYPE,
t0.COREID, t0.CURRENTMESSAGEID,
t0.CURRENTPACKAGINGSTATEOID,
t0.CURRENTSTATEOID, t0.DELIVEREDTIME, t0.DIRECTION,
t0.DOCUMENTCLASS, t0.ISDUPLICATE,
t0.EDICONTROLID, t0.EXPIRATIONTIME, t0.INTEGRATIONID,
t0.ISCHILD, t0.ORIGINATIONTIME,
t0.PRODUCEDCOUNT, t0.PRODUCTIONFILENAME, t0.RECEIVERPARTYOID,
t0.RECEIVERPARTYID, t0.RECEIVERROUTINGID,
t0.RECEIVERROUTINGIDTYPE, t0.REFTOOID,
t0.RESUBMITCOUNT, t0.RESUBMITINFO,
t0.SENDERPARTYOID, t0.SENDERPARTYID, t0.SENDERROUTINGID,
t0.SENDERROUTINGIDTYPE, t0.TYPE
FROM DBA.MESSAGES t0
INNER JOIN DBA.CUSTOMDATA t1 ON t0.OID = t1.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t2 ON t0.OID = t2.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t3 ON t0.OID = t3.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t4 ON t0.OID = t4.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t5 ON t0.OID = t5.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t6 ON t0.OID = t6.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t7 ON t0.OID = t7.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t8 ON t0.OID = t8.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t9 ON t0.OID = t9.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t10 ON t0.OID = t10.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t11 ON t0.OID = t11.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t12 ON t0.OID = t12.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t13 ON t0.OID = t13.MESSAGEOID
INNER JOIN DBA.MESSAGESTATES t14 ON t0.OID = t14.MESSAGEOID
WHERE (t0.RECEIVERROUTINGID = ?
AND t0.SENDERROUTINGID = ?
AND t0.DIRECTION = ?
AND t0.ORIGINATIONTIME >= ?
AND t0.ORIGINATIONTIME <= ?
AND t0.DOCUMENTCLASS = ?
AND t0.CONTENTMIMETYPE = ?
AND t1.NAME = ?
AND t1.DATAVALUE = ?
AND t2.NAME = ?
AND t2.DATAVALUE = ?
AND t3.NAME = ?
[SNIP]
Ok, do you start to see the problem here?
Did you actually write this or are you using some brain dead auto
generated code?
Hint:
Try something like this...
SELECT t0.*
FROM dba.messages t0
INNER JOIN dba.customdata t1 ON t0.oid = t1.messageoid
WHERE t0.<Column Name> = ?
...
AND t1.name in (?,?,...)
AND t1.datavalue in (?,?,?,...)
Note the following:
1) I'm not going to write out your query long hand.
2) If you're writing out the in statement long hand the number of
values is going to be static
If you're going to do this dynamically, then at run time you can build
the query and then execute it.
In addition,
There is a known issue with the IN predicate...
HTH...
________________________________
From: Amit Patel [mailto:apatel@us.axway.com]
Sent: Wednesday, October 11, 2006 1:48 PM
To: derby-user@db.apache.org
Subject: Derby Optimizer Hangs
Hello,
I ran into a problem with Derby. When I issue a large SQL statement
with lots of WHERE conditions, the Derby optimizer looks like it's in an
infinite loop. A Thread dump of where it is stuck is shown below.
Also, a sample of the SQL that causes it to hang is also below.
Does anyone know if this is a bug or can I tune Derby to get around this
issue?
Thanks,
Amit Patel
Derby Version: 10.2.1.6
2006-10-11 18:30:03.666 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby -
10.2.1.6 - (452058): instance c065801d-010e-38a2-fae2-ffffb81c5a7c
on database directory C:\Source\CI5\Trunk\corelib\db\derby\Cyclone
Sample SQL that causes derby to hang:
SELECT DISTINCT t0.OID, t0.JDOVERSION, t0.BINARYCOLLABORATIONHASHCODE,
t0.CONSUMPTIONEXCHANGEPOINTOID, t0.CONSUMPTIONFILENAME,
t0.CONTENTMIMETYPE, t0.COREID, t0.CURRENTMESSAGEID,
t0.CURRENTPACKAGINGSTATEOID, t0.CURRENTSTATEOID, t0.DELIVEREDTIME,
t0.DIRECTION, t0.DOCUMENTCLASS, t0.ISDUPLICATE, t0.EDICONTROLID,
t0.EXPIRATIONTIME, t0.INTEGRATIONID, t0.ISCHILD, t0.ORIGINATIONTIME,
t0.PRODUCEDCOUNT, t0.PRODUCTIONFILENAME, t0.RECEIVERPARTYOID,
t0.RECEIVERPARTYID, t0.RECEIVERROUTINGID, t0.RECEIVERROUTINGIDTYPE,
t0.REFTOOID, t0.RESUBMITCOUNT, t0.RESUBMITINFO, t0.SENDERPARTYOID,
t0.SENDERPARTYID, t0.SENDERROUTINGID, t0.SENDERROUTINGIDTYPE, t0.TYPE
FROM DBA.MESSAGES t0 INNER JOIN DBA.CUSTOMDATA t1 ON t0.OID =
t1.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t2 ON t0.OID = t2.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t3 ON t0.OID = t3.MESSAGEOID INNER JOIN
DBA.CUSTOMDATA t4 ON t0.OID = t4.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t5
ON t0.OID = t5.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t6 ON t0.OID =
t6.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t7 ON t0.OID = t7.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t8 ON t0.OID = t8.MESSAGEOID INNER JOIN
DBA.CUSTOMDATA t9 ON t0.OID = t9.MESSAGEOID INNER JOIN DBA.CUSTOMDATA
t10 ON t0.OID = t10.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t11 ON t0.OID =
t11.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t12 ON t0.OID = t12.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t13 ON t0.OID = t13.MESSAGEOID INNER JOIN
DBA.MESSAGESTATES t14 ON t0.OID = t14.MESSAGEOID WHERE
(t0.RECEIVERROUTINGID = ? AND t0.SENDERROUTINGID = ? AND t0.DIRECTION =
? AND t0.ORIGINATIONTIME >= ? AND t0.ORIGINATIONTIME <= ? AND
t0.DOCUMENTCLASS = ? AND t0.CONTENTMIMETYPE = ? AND t1.NAME = ? AND
t1.DATAVALUE = ? AND t2.NAME = ? AND t2.DATAVALUE = ? AND t3.NAME = ?
AND t3.DATAVALUE = ? AND t4.NAME = ? AND t4.DATAVALUE = ? AND t5.NAME =
? AND t5.DATAVALUE = ? AND t6.NAME = ? AND t6.DATAVALUE = ? AND t7.NAME
= ? AND t7.DATAVALUE = ? AND t8.NAME = ? AND t8.DATAVALUE = ? AND
t9.NAME = ? AND t9.DATAVALUE = ? AND t10.NAME = ? AND t10.DATAVALUE = ?
AND t11.NAME = ? AND t11.DATAVALUE = ? AND t12.NAME = ? AND
t12.DATAVALUE = ? AND t13.NAME = ? AND t13.DATAVALUE = ? AND t14.TYPE =
? AND t14.URL = ?) ORDER BY t0.ORIGINATIONTIME DESC
Thread Dump of thread executing this query in Derby:
"JettySocketListener0-0" prio=6 tid=0x27939748 nid=0x838 runnable
[0x2a9ce000..0x2a9cfbe8]
at org.apache.derby.iapi.util.JBitSet.clearAll(Unknown
Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.initBaseT
ableVisitor(Unknown Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.valNodeRe
ferencesOptTable(Unknown Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.isQualifi
er(Unknown Source)
at
org.apache.derby.impl.sql.compile.PredicateList.hasOptimizableEquijoin(U
nknown Source)
at
org.apache.derby.impl.sql.compile.HashJoinStrategy.findHashKeyColumns(Un
known Source)
at
org.apache.derby.impl.sql.compile.HashJoinStrategy.feasible(Unknown
Source)
at
org.apache.derby.impl.sql.compile.FromTable.feasibleJoinStrategy(Unknown
Source)
at
org.apache.derby.impl.sql.compile.OptimizerImpl.costOptimizable(Unknown
Source)
at
org.apache.derby.impl.sql.compile.FromBaseTable.optimizeIt(Unknown
Source)
at
org.apache.derby.impl.sql.compile.ProjectRestrictNode.optimizeIt(Unknown
Source)
at
org.apache.derby.impl.sql.compile.OptimizerImpl.costPermutation(Unknown
Source)
at
org.apache.derby.impl.sql.compile.SelectNode.optimize(Unknown Source)
at
org.apache.derby.impl.sql.compile.DMLStatementNode.optimize(Unknown
Source)
at
org.apache.derby.impl.sql.compile.CursorNode.optimize(Unknown Source)
at
org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at
org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareI
nternalStatement(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
Source)
at
org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
- locked <0x0570e888> (a
org.apache.derby.impl.jdbc.EmbedConnection30)
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingCon
nection.java:382)
at
com.solarmetric.jdbc.PoolConnection.prepareStatement(PoolConnection.java
:114)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingCon
nection.java:380)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingCon
nection.java:380)
at
com.solarmetric.jdbc.LoggingConnectionDecorator$LoggingConnection.prepar
eStatement(LoggingConnectionDecorator.java:374)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingCon
nection.java:380)
at
kodo.jdbc.runtime.JDBCStoreManager$RefCountConnection.prepareStatement(J
DBCStoreManager.java:1645)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingCon
nection.java:366)
at
kodo.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:449)
at kodo.jdbc.sql.Select.execute(Select.java:1578)
at kodo.jdbc.sql.Select.execute(Select.java:1533)
at
kodo.jdbc.runtime.SelectResultObjectProvider.open(SelectResultObjectProv
ider.java:102)
at
com.solarmetric.rop.WindowResultList.<init>(WindowResultList.java:46)
at
kodo.jdbc.runtime.JDBCFetchConfigurationImpl.newResultList(JDBCFetchConf
igurationImpl.java:164)
at
kodo.runtime.CopyOnWriteFetchConfiguration.newResultList(CopyOnWriteFetc
hConfiguration.java:340)
at kodo.query.AbstractQuery.execute(AbstractQuery.java:1081)
at
kodo.query.AbstractQuery.executeWithMap(AbstractQuery.java:877)
at
com.cyclonecommerce.persistence.providers.jdo.JdoQuery.retrievePagedResu
lts(JdoQuery.java:201)
at
com.cyclonecommerce.persistence.query.AbstractQuery.executePagedQuery(Ab
stractQuery.java:156)
at
com.cyclonecommerce.persistence.PagedView.getPage(PagedView.java:82)
at
com.cyclonecommerce.ui.messages.MessageQuery.findMessages(MessageQuery.j
ava:1206)
at
com.cyclonecommerce.ui.messages.MessageMetadataQueryAction.serializedAct
(MessageMetadataQueryAction.java:97)
at
com.cyclonecommerce.ui.core.actions.CycloneAbstractAction.act(CycloneAbs
tractAction.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.ActTypeNode.invoke(Ac
tTypeNode.java:124)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.i
nvoke(PreparableMatchNode.java:130)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.MatchNode.invoke(Matc
hNode.java:107)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(P
ipelineNode.java:142)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(
PipelinesNode.java:92)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:234)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:176)
at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreePro
cessor.java:248)
at
org.apache.cocoon.components.treeprocessor.sitemap.MountNode.invoke(Moun
tNode.java:117)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.i
nvoke(PreparableMatchNode.java:130)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.MatchNode.invoke(Matc
hNode.java:107)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(P
ipelineNode.java:142)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(
PipelinesNode.java:92)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:234)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:176)
at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreePro
cessor.java:248)
at org.apache.cocoon.Cocoon.process(Cocoon.java:679)
at
org.apache.cocoon.servlet.CocoonServlet.service(CocoonServlet.java:1183)
at
javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at
org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428)
at
org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationH
andler.java:473)
at
org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568)
at
org.mortbay.http.HttpContext.handle(HttpContext.java:1530)
at
org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationCon
text.java:633)
at
org.mortbay.http.HttpContext.handle(HttpContext.java:1482)
at org.mortbay.http.HttpServer.service(HttpServer.java:909)
at
org.mortbay.http.HttpConnection.service(HttpConnection.java:817)
at
org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:983)
at
org.mortbay.http.HttpConnection.handle(HttpConnection.java:834)
at
org.mortbay.http.SocketListener.handleConnection(SocketListener.java:244
)
at
org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:359)
at
org.mortbay.util.ThreadPool$PoolThread.primRun(ThreadPool.java:537)
at
com.cyclonecommerce.lang.EventedThread.run(EventedThread.java:71)
RE: Derby Optimizer Hangs
Posted by Michael Segel <ms...@segel.com>.
Just a follow up.
Looking at this, you could be trying to say
Join these two tables where (customerdata name = ? AND value = ?) OR
(customerdata name = ? AND value =?)
In that case you'll have a query that looks like
SELECT t0.*
FROM dba.messages t0
INNER JOIN dba.customdata t1 ON t0.oid = t1.messageoid
WHERE t0.<Column Name> = ?
.
AND( ( t1.name = ? AND t1.datavalue =?)
OR( t1.name = ? AND t1.datavalue =?)
OR(( t1.name = ? AND t1.datavalue =?)
.
)
ORDER BY .
Now I'm sure that there's a better way, but you are not doing so many joins.
HTH
-G
_____
From: Michael Segel [mailto:msegel@segel.com] On Behalf Of derby@segel.com
Sent: Wednesday, October 11, 2006 4:37 PM
To: 'Derby Discussion'
Subject: RE: Derby Optimizer Hangs
Well, with a lack of formatting its hard to read your query.
However. Lets clean it up.
Lets see if this works right.
SELECT DISTINCT t0.OID, t0.JDOVERSION, t0.BINARYCOLLABORATIONHASHCODE,
t0.CONSUMPTIONEXCHANGEPOINTOID,
t0.CONSUMPTIONFILENAME, t0.CONTENTMIMETYPE,
t0.COREID, t0.CURRENTMESSAGEID,
t0.CURRENTPACKAGINGSTATEOID,
t0.CURRENTSTATEOID, t0.DELIVEREDTIME, t0.DIRECTION,
t0.DOCUMENTCLASS, t0.ISDUPLICATE,
t0.EDICONTROLID, t0.EXPIRATIONTIME, t0.INTEGRATIONID,
t0.ISCHILD, t0.ORIGINATIONTIME,
t0.PRODUCEDCOUNT, t0.PRODUCTIONFILENAME, t0.RECEIVERPARTYOID,
t0.RECEIVERPARTYID, t0.RECEIVERROUTINGID,
t0.RECEIVERROUTINGIDTYPE, t0.REFTOOID,
t0.RESUBMITCOUNT, t0.RESUBMITINFO,
t0.SENDERPARTYOID, t0.SENDERPARTYID, t0.SENDERROUTINGID,
t0.SENDERROUTINGIDTYPE, t0.TYPE
FROM DBA.MESSAGES t0
INNER JOIN DBA.CUSTOMDATA t1 ON t0.OID = t1.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t2 ON t0.OID = t2.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t3 ON t0.OID = t3.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t4 ON t0.OID = t4.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t5 ON t0.OID = t5.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t6 ON t0.OID = t6.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t7 ON t0.OID = t7.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t8 ON t0.OID = t8.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t9 ON t0.OID = t9.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t10 ON t0.OID = t10.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t11 ON t0.OID = t11.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t12 ON t0.OID = t12.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t13 ON t0.OID = t13.MESSAGEOID
INNER JOIN DBA.MESSAGESTATES t14 ON t0.OID = t14.MESSAGEOID
WHERE (t0.RECEIVERROUTINGID = ?
AND t0.SENDERROUTINGID = ?
AND t0.DIRECTION = ?
AND t0.ORIGINATIONTIME >= ?
AND t0.ORIGINATIONTIME <= ?
AND t0.DOCUMENTCLASS = ?
AND t0.CONTENTMIMETYPE = ?
AND t1.NAME = ?
AND t1.DATAVALUE = ?
AND t2.NAME = ?
AND t2.DATAVALUE = ?
AND t3.NAME = ?
[SNIP]
Ok, do you start to see the problem here?
Did you actually write this or are you using some brain dead auto generated
code?
Hint:
Try something like this.
SELECT t0.*
FROM dba.messages t0
INNER JOIN dba.customdata t1 ON t0.oid = t1.messageoid
WHERE t0.<Column Name> = ?
.
AND t1.name in (?,?,.)
AND t1.datavalue in (?,?,?,.)
Note the following:
1) I'm not going to write out your query long hand.
2) If you're writing out the in statement long hand the number of values
is going to be static
If you're going to do this dynamically, then at run time you can build the
query and then execute it.
In addition,
There is a known issue with the IN predicate.
HTH.
_____
From: Amit Patel [mailto:apatel@us.axway.com]
Sent: Wednesday, October 11, 2006 1:48 PM
To: derby-user@db.apache.org
Subject: Derby Optimizer Hangs
Hello,
I ran into a problem with Derby. When I issue a large SQL statement with
lots of WHERE conditions, the Derby optimizer looks like it's in an infinite
loop. A Thread dump of where it is stuck is shown below. Also, a sample of
the SQL that causes it to hang is also below.
Does anyone know if this is a bug or can I tune Derby to get around this
issue?
Thanks,
Amit Patel
Derby Version: 10.2.1.6
2006-10-11 18:30:03.666 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby -
10.2.1.6 - (452058): instance c065801d-010e-38a2-fae2-ffffb81c5a7c
on database directory C:\Source\CI5\Trunk\corelib\db\derby\Cyclone
Sample SQL that causes derby to hang:
SELECT DISTINCT t0.OID, t0.JDOVERSION, t0.BINARYCOLLABORATIONHASHCODE,
t0.CONSUMPTIONEXCHANGEPOINTOID, t0.CONSUMPTIONFILENAME, t0.CONTENTMIMETYPE,
t0.COREID, t0.CURRENTMESSAGEID, t0.CURRENTPACKAGINGSTATEOID,
t0.CURRENTSTATEOID, t0.DELIVEREDTIME, t0.DIRECTION, t0.DOCUMENTCLASS,
t0.ISDUPLICATE, t0.EDICONTROLID, t0.EXPIRATIONTIME, t0.INTEGRATIONID,
t0.ISCHILD, t0.ORIGINATIONTIME, t0.PRODUCEDCOUNT, t0.PRODUCTIONFILENAME,
t0.RECEIVERPARTYOID, t0.RECEIVERPARTYID, t0.RECEIVERROUTINGID,
t0.RECEIVERROUTINGIDTYPE, t0.REFTOOID, t0.RESUBMITCOUNT, t0.RESUBMITINFO,
t0.SENDERPARTYOID, t0.SENDERPARTYID, t0.SENDERROUTINGID,
t0.SENDERROUTINGIDTYPE, t0.TYPE FROM DBA.MESSAGES t0 INNER JOIN
DBA.CUSTOMDATA t1 ON t0.OID = t1.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t2 ON
t0.OID = t2.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t3 ON t0.OID =
t3.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t4 ON t0.OID = t4.MESSAGEOID INNER
JOIN DBA.CUSTOMDATA t5 ON t0.OID = t5.MESSAGEOID INNER JOIN DBA.CUSTOMDATA
t6 ON t0.OID = t6.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t7 ON t0.OID =
t7.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t8 ON t0.OID = t8.MESSAGEOID INNER
JOIN DBA.CUSTOMDATA t9 ON t0.OID = t9.MESSAGEOID INNER JOIN DBA.CUSTOMDATA
t10 ON t0.OID = t10.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t11 ON t0.OID =
t11.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t12 ON t0.OID = t12.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t13 ON t0.OID = t13.MESSAGEOID INNER JOIN
DBA.MESSAGESTATES t14 ON t0.OID = t14.MESSAGEOID WHERE (t0.RECEIVERROUTINGID
= ? AND t0.SENDERROUTINGID = ? AND t0.DIRECTION = ? AND t0.ORIGINATIONTIME
>= ? AND t0.ORIGINATIONTIME <= ? AND t0.DOCUMENTCLASS = ? AND
t0.CONTENTMIMETYPE = ? AND t1.NAME = ? AND t1.DATAVALUE = ? AND t2.NAME = ?
AND t2.DATAVALUE = ? AND t3.NAME = ? AND t3.DATAVALUE = ? AND t4.NAME = ?
AND t4.DATAVALUE = ? AND t5.NAME = ? AND t5.DATAVALUE = ? AND t6.NAME = ?
AND t6.DATAVALUE = ? AND t7.NAME = ? AND t7.DATAVALUE = ? AND t8.NAME = ?
AND t8.DATAVALUE = ? AND t9.NAME = ? AND t9.DATAVALUE = ? AND t10.NAME = ?
AND t10.DATAVALUE = ? AND t11.NAME = ? AND t11.DATAVALUE = ? AND t12.NAME =
? AND t12.DATAVALUE = ? AND t13.NAME = ? AND t13.DATAVALUE = ? AND t14.TYPE
= ? AND t14.URL = ?) ORDER BY t0.ORIGINATIONTIME DESC
Thread Dump of thread executing this query in Derby:
"JettySocketListener0-0" prio=6 tid=0x27939748 nid=0x838 runnable
[0x2a9ce000..0x2a9cfbe8]
at org.apache.derby.iapi.util.JBitSet.clearAll(Unknown Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.initBaseTable
Visitor(Unknown Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.valNodeRefere
ncesOptTable(Unknown Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.isQualifier(U
nknown Source)
at
org.apache.derby.impl.sql.compile.PredicateList.hasOptimizableEquijoin(Unkno
wn Source)
at
org.apache.derby.impl.sql.compile.HashJoinStrategy.findHashKeyColumns(Unknow
n Source)
at
org.apache.derby.impl.sql.compile.HashJoinStrategy.feasible(Unknown Source)
at
org.apache.derby.impl.sql.compile.FromTable.feasibleJoinStrategy(Unknown
Source)
at
org.apache.derby.impl.sql.compile.OptimizerImpl.costOptimizable(Unknown
Source)
at
org.apache.derby.impl.sql.compile.FromBaseTable.optimizeIt(Unknown Source)
at
org.apache.derby.impl.sql.compile.ProjectRestrictNode.optimizeIt(Unknown
Source)
at
org.apache.derby.impl.sql.compile.OptimizerImpl.costPermutation(Unknown
Source)
at org.apache.derby.impl.sql.compile.SelectNode.optimize(Unknown
Source)
at
org.apache.derby.impl.sql.compile.DMLStatementNode.optimize(Unknown Source)
at org.apache.derby.impl.sql.compile.CursorNode.optimize(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown
Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInter
nalStatement(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at
org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
- locked <0x0570e888> (a
org.apache.derby.impl.jdbc.EmbedConnection30)
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:382)
at
com.solarmetric.jdbc.PoolConnection.prepareStatement(PoolConnection.java:114
)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:380)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:380)
at
com.solarmetric.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareSta
tement(LoggingConnectionDecorator.java:374)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:380)
at
kodo.jdbc.runtime.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCS
toreManager.java:1645)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:366)
at kodo.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:449)
at kodo.jdbc.sql.Select.execute(Select.java:1578)
at kodo.jdbc.sql.Select.execute(Select.java:1533)
at
kodo.jdbc.runtime.SelectResultObjectProvider.open(SelectResultObjectProvider
.java:102)
at
com.solarmetric.rop.WindowResultList.<init>(WindowResultList.java:46)
at
kodo.jdbc.runtime.JDBCFetchConfigurationImpl.newResultList(JDBCFetchConfigur
ationImpl.java:164)
at
kodo.runtime.CopyOnWriteFetchConfiguration.newResultList(CopyOnWriteFetchCon
figuration.java:340)
at kodo.query.AbstractQuery.execute(AbstractQuery.java:1081)
at
kodo.query.AbstractQuery.executeWithMap(AbstractQuery.java:877)
at
com.cyclonecommerce.persistence.providers.jdo.JdoQuery.retrievePagedResults(
JdoQuery.java:201)
at
com.cyclonecommerce.persistence.query.AbstractQuery.executePagedQuery(Abstra
ctQuery.java:156)
at
com.cyclonecommerce.persistence.PagedView.getPage(PagedView.java:82)
at
com.cyclonecommerce.ui.messages.MessageQuery.findMessages(MessageQuery.java:
1206)
at
com.cyclonecommerce.ui.messages.MessageMetadataQueryAction.serializedAct(Mes
sageMetadataQueryAction.java:97)
at
com.cyclonecommerce.ui.core.actions.CycloneAbstractAction.act(CycloneAbstrac
tAction.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.ActTypeNode.invoke(ActTyp
eNode.java:124)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.invok
e(PreparableMatchNode.java:130)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.MatchNode.invoke(MatchNod
e.java:107)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(Pipel
ineNode.java:142)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(Pipe
linesNode.java:92)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process(Con
creteTreeProcessor.java:234)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process(Con
creteTreeProcessor.java:176)
at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcess
or.java:248)
at
org.apache.cocoon.components.treeprocessor.sitemap.MountNode.invoke(MountNod
e.java:117)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.invok
e(PreparableMatchNode.java:130)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.MatchNode.invoke(MatchNod
e.java:107)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(Pipel
ineNode.java:142)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(Pipe
linesNode.java:92)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process(Con
creteTreeProcessor.java:234)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process(Con
creteTreeProcessor.java:176)
at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcess
or.java:248)
at org.apache.cocoon.Cocoon.process(Cocoon.java:679)
at
org.apache.cocoon.servlet.CocoonServlet.service(CocoonServlet.java:1183)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at
org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428)
at
org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationHandl
er.java:473)
at
org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1530)
at
org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationContext
.java:633)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1482)
at org.mortbay.http.HttpServer.service(HttpServer.java:909)
at
org.mortbay.http.HttpConnection.service(HttpConnection.java:817)
at
org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:983)
at
org.mortbay.http.HttpConnection.handle(HttpConnection.java:834)
at
org.mortbay.http.SocketListener.handleConnection(SocketListener.java:244)
at
org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:359)
at
org.mortbay.util.ThreadPool$PoolThread.primRun(ThreadPool.java:537)
at
com.cyclonecommerce.lang.EventedThread.run(EventedThread.java:71)
RE: Derby Optimizer Hangs
Posted by de...@segel.com.
Well, with a lack of formatting its hard to read your query.
However. Lets clean it up.
Lets see if this works right.
SELECT DISTINCT t0.OID, t0.JDOVERSION, t0.BINARYCOLLABORATIONHASHCODE,
t0.CONSUMPTIONEXCHANGEPOINTOID,
t0.CONSUMPTIONFILENAME, t0.CONTENTMIMETYPE,
t0.COREID, t0.CURRENTMESSAGEID,
t0.CURRENTPACKAGINGSTATEOID,
t0.CURRENTSTATEOID, t0.DELIVEREDTIME, t0.DIRECTION,
t0.DOCUMENTCLASS, t0.ISDUPLICATE,
t0.EDICONTROLID, t0.EXPIRATIONTIME, t0.INTEGRATIONID,
t0.ISCHILD, t0.ORIGINATIONTIME,
t0.PRODUCEDCOUNT, t0.PRODUCTIONFILENAME, t0.RECEIVERPARTYOID,
t0.RECEIVERPARTYID, t0.RECEIVERROUTINGID,
t0.RECEIVERROUTINGIDTYPE, t0.REFTOOID,
t0.RESUBMITCOUNT, t0.RESUBMITINFO,
t0.SENDERPARTYOID, t0.SENDERPARTYID, t0.SENDERROUTINGID,
t0.SENDERROUTINGIDTYPE, t0.TYPE
FROM DBA.MESSAGES t0
INNER JOIN DBA.CUSTOMDATA t1 ON t0.OID = t1.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t2 ON t0.OID = t2.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t3 ON t0.OID = t3.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t4 ON t0.OID = t4.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t5 ON t0.OID = t5.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t6 ON t0.OID = t6.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t7 ON t0.OID = t7.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t8 ON t0.OID = t8.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t9 ON t0.OID = t9.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t10 ON t0.OID = t10.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t11 ON t0.OID = t11.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t12 ON t0.OID = t12.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t13 ON t0.OID = t13.MESSAGEOID
INNER JOIN DBA.MESSAGESTATES t14 ON t0.OID = t14.MESSAGEOID
WHERE (t0.RECEIVERROUTINGID = ?
AND t0.SENDERROUTINGID = ?
AND t0.DIRECTION = ?
AND t0.ORIGINATIONTIME >= ?
AND t0.ORIGINATIONTIME <= ?
AND t0.DOCUMENTCLASS = ?
AND t0.CONTENTMIMETYPE = ?
AND t1.NAME = ?
AND t1.DATAVALUE = ?
AND t2.NAME = ?
AND t2.DATAVALUE = ?
AND t3.NAME = ?
[SNIP]
Ok, do you start to see the problem here?
Did you actually write this or are you using some brain dead auto generated
code?
Hint:
Try something like this.
SELECT t0.*
FROM dba.messages t0
INNER JOIN dba.customdata t1 ON t0.oid = t1.messageoid
WHERE t0.<Column Name> = ?
.
AND t1.name in (?,?,.)
AND t1.datavalue in (?,?,?,.)
Note the following:
1) I'm not going to write out your query long hand.
2) If you're writing out the in statement long hand the number of
values is going to be static
If you're going to do this dynamically, then at run time you can build the
query and then execute it.
In addition,
There is a known issue with the IN predicate.
HTH.
_____
From: Amit Patel [mailto:apatel@us.axway.com]
Sent: Wednesday, October 11, 2006 1:48 PM
To: derby-user@db.apache.org
Subject: Derby Optimizer Hangs
Hello,
I ran into a problem with Derby. When I issue a large SQL statement with
lots of WHERE conditions, the Derby optimizer looks like it's in an infinite
loop. A Thread dump of where it is stuck is shown below. Also, a sample of
the SQL that causes it to hang is also below.
Does anyone know if this is a bug or can I tune Derby to get around this
issue?
Thanks,
Amit Patel
Derby Version: 10.2.1.6
2006-10-11 18:30:03.666 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby -
10.2.1.6 - (452058): instance c065801d-010e-38a2-fae2-ffffb81c5a7c
on database directory C:\Source\CI5\Trunk\corelib\db\derby\Cyclone
Sample SQL that causes derby to hang:
SELECT DISTINCT t0.OID, t0.JDOVERSION, t0.BINARYCOLLABORATIONHASHCODE,
t0.CONSUMPTIONEXCHANGEPOINTOID, t0.CONSUMPTIONFILENAME, t0.CONTENTMIMETYPE,
t0.COREID, t0.CURRENTMESSAGEID, t0.CURRENTPACKAGINGSTATEOID,
t0.CURRENTSTATEOID, t0.DELIVEREDTIME, t0.DIRECTION, t0.DOCUMENTCLASS,
t0.ISDUPLICATE, t0.EDICONTROLID, t0.EXPIRATIONTIME, t0.INTEGRATIONID,
t0.ISCHILD, t0.ORIGINATIONTIME, t0.PRODUCEDCOUNT, t0.PRODUCTIONFILENAME,
t0.RECEIVERPARTYOID, t0.RECEIVERPARTYID, t0.RECEIVERROUTINGID,
t0.RECEIVERROUTINGIDTYPE, t0.REFTOOID, t0.RESUBMITCOUNT, t0.RESUBMITINFO,
t0.SENDERPARTYOID, t0.SENDERPARTYID, t0.SENDERROUTINGID,
t0.SENDERROUTINGIDTYPE, t0.TYPE FROM DBA.MESSAGES t0 INNER JOIN
DBA.CUSTOMDATA t1 ON t0.OID = t1.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t2 ON
t0.OID = t2.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t3 ON t0.OID =
t3.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t4 ON t0.OID = t4.MESSAGEOID INNER
JOIN DBA.CUSTOMDATA t5 ON t0.OID = t5.MESSAGEOID INNER JOIN DBA.CUSTOMDATA
t6 ON t0.OID = t6.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t7 ON t0.OID =
t7.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t8 ON t0.OID = t8.MESSAGEOID INNER
JOIN DBA.CUSTOMDATA t9 ON t0.OID = t9.MESSAGEOID INNER JOIN DBA.CUSTOMDATA
t10 ON t0.OID = t10.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t11 ON t0.OID =
t11.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t12 ON t0.OID = t12.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t13 ON t0.OID = t13.MESSAGEOID INNER JOIN
DBA.MESSAGESTATES t14 ON t0.OID = t14.MESSAGEOID WHERE (t0.RECEIVERROUTINGID
= ? AND t0.SENDERROUTINGID = ? AND t0.DIRECTION = ? AND t0.ORIGINATIONTIME
>= ? AND t0.ORIGINATIONTIME <= ? AND t0.DOCUMENTCLASS = ? AND
t0.CONTENTMIMETYPE = ? AND t1.NAME = ? AND t1.DATAVALUE = ? AND t2.NAME = ?
AND t2.DATAVALUE = ? AND t3.NAME = ? AND t3.DATAVALUE = ? AND t4.NAME = ?
AND t4.DATAVALUE = ? AND t5.NAME = ? AND t5.DATAVALUE = ? AND t6.NAME = ?
AND t6.DATAVALUE = ? AND t7.NAME = ? AND t7.DATAVALUE = ? AND t8.NAME = ?
AND t8.DATAVALUE = ? AND t9.NAME = ? AND t9.DATAVALUE = ? AND t10.NAME = ?
AND t10.DATAVALUE = ? AND t11.NAME = ? AND t11.DATAVALUE = ? AND t12.NAME =
? AND t12.DATAVALUE = ? AND t13.NAME = ? AND t13.DATAVALUE = ? AND t14.TYPE
= ? AND t14.URL = ?) ORDER BY t0.ORIGINATIONTIME DESC
Thread Dump of thread executing this query in Derby:
"JettySocketListener0-0" prio=6 tid=0x27939748 nid=0x838 runnable
[0x2a9ce000..0x2a9cfbe8]
at org.apache.derby.iapi.util.JBitSet.clearAll(Unknown Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.initBaseTable
Visitor(Unknown Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.valNodeRefere
ncesOptTable(Unknown Source)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.isQualifier(U
nknown Source)
at
org.apache.derby.impl.sql.compile.PredicateList.hasOptimizableEquijoin(Unkno
wn Source)
at
org.apache.derby.impl.sql.compile.HashJoinStrategy.findHashKeyColumns(Unknow
n Source)
at
org.apache.derby.impl.sql.compile.HashJoinStrategy.feasible(Unknown Source)
at
org.apache.derby.impl.sql.compile.FromTable.feasibleJoinStrategy(Unknown
Source)
at
org.apache.derby.impl.sql.compile.OptimizerImpl.costOptimizable(Unknown
Source)
at
org.apache.derby.impl.sql.compile.FromBaseTable.optimizeIt(Unknown Source)
at
org.apache.derby.impl.sql.compile.ProjectRestrictNode.optimizeIt(Unknown
Source)
at
org.apache.derby.impl.sql.compile.OptimizerImpl.costPermutation(Unknown
Source)
at org.apache.derby.impl.sql.compile.SelectNode.optimize(Unknown
Source)
at
org.apache.derby.impl.sql.compile.DMLStatementNode.optimize(Unknown Source)
at org.apache.derby.impl.sql.compile.CursorNode.optimize(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown
Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInter
nalStatement(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at
org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
- locked <0x0570e888> (a
org.apache.derby.impl.jdbc.EmbedConnection30)
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:382)
at
com.solarmetric.jdbc.PoolConnection.prepareStatement(PoolConnection.java:114
)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:380)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:380)
at
com.solarmetric.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareSta
tement(LoggingConnectionDecorator.java:374)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:380)
at
kodo.jdbc.runtime.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCS
toreManager.java:1645)
at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConnect
ion.java:366)
at kodo.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:449)
at kodo.jdbc.sql.Select.execute(Select.java:1578)
at kodo.jdbc.sql.Select.execute(Select.java:1533)
at
kodo.jdbc.runtime.SelectResultObjectProvider.open(SelectResultObjectProvider
.java:102)
at
com.solarmetric.rop.WindowResultList.<init>(WindowResultList.java:46)
at
kodo.jdbc.runtime.JDBCFetchConfigurationImpl.newResultList(JDBCFetchConfigur
ationImpl.java:164)
at
kodo.runtime.CopyOnWriteFetchConfiguration.newResultList(CopyOnWriteFetchCon
figuration.java:340)
at kodo.query.AbstractQuery.execute(AbstractQuery.java:1081)
at
kodo.query.AbstractQuery.executeWithMap(AbstractQuery.java:877)
at
com.cyclonecommerce.persistence.providers.jdo.JdoQuery.retrievePagedResults(
JdoQuery.java:201)
at
com.cyclonecommerce.persistence.query.AbstractQuery.executePagedQuery(Abstra
ctQuery.java:156)
at
com.cyclonecommerce.persistence.PagedView.getPage(PagedView.java:82)
at
com.cyclonecommerce.ui.messages.MessageQuery.findMessages(MessageQuery.java:
1206)
at
com.cyclonecommerce.ui.messages.MessageMetadataQueryAction.serializedAct(Mes
sageMetadataQueryAction.java:97)
at
com.cyclonecommerce.ui.core.actions.CycloneAbstractAction.act(CycloneAbstrac
tAction.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.ActTypeNode.invoke(ActTyp
eNode.java:124)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.invok
e(PreparableMatchNode.java:130)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.MatchNode.invoke(MatchNod
e.java:107)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(Pipel
ineNode.java:142)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(Pipe
linesNode.java:92)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process(Con
creteTreeProcessor.java:234)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process(Con
creteTreeProcessor.java:176)
at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcess
or.java:248)
at
org.apache.cocoon.components.treeprocessor.sitemap.MountNode.invoke(MountNod
e.java:117)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.invok
e(PreparableMatchNode.java:130)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.MatchNode.invoke(MatchNod
e.java:107)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(Pipel
ineNode.java:142)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(Pipe
linesNode.java:92)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process(Con
creteTreeProcessor.java:234)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process(Con
creteTreeProcessor.java:176)
at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcess
or.java:248)
at org.apache.cocoon.Cocoon.process(Cocoon.java:679)
at
org.apache.cocoon.servlet.CocoonServlet.service(CocoonServlet.java:1183)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at
org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428)
at
org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationHandl
er.java:473)
at
org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1530)
at
org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationContext
.java:633)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1482)
at org.mortbay.http.HttpServer.service(HttpServer.java:909)
at
org.mortbay.http.HttpConnection.service(HttpConnection.java:817)
at
org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:983)
at
org.mortbay.http.HttpConnection.handle(HttpConnection.java:834)
at
org.mortbay.http.SocketListener.handleConnection(SocketListener.java:244)
at
org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:359)
at
org.mortbay.util.ThreadPool$PoolThread.primRun(ThreadPool.java:537)
at
com.cyclonecommerce.lang.EventedThread.run(EventedThread.java:71)