db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Segel" <mse...@segel.com>
Subject RE: Derby Optimizer Hangs
Date Wed, 11 Oct 2006 21:43:14 GMT
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)

 


 

 

 

 


Mime
View raw message