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 =3D ? AND value =3D ?) OR (customerdata name =3D ? = AND value =3D?)

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 =3D = t1.messageoid

WHERE t0.<Column Name> =3D ? =

…

AND( ( t1.name =3D ? AND = t1.datavalue =3D?)

OR( t1.name =3D = ? AND t1.datavalue =3D?)

OR(( t1.name =3D = ? AND t1.datavalue =3D?)

…

= )

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,

= &= nbsp; t0.CONSUMPTIONFILENAME, t0.CONTENTMIMETYPE, t0.COREID, t0.CURRENTMESSAGEID,

= &= nbsp; t0.CURRENTPACKAGINGSTATEOID, t0.CURRENTSTATEOID, t0.DELIVEREDTIME, t0.DIRECTION,

= &= nbsp; t0.DOCUMENTCLASS, t0.ISDUPLICATE, t0.EDICONTROLID, t0.EXPIRATIONTIME, t0.INTEGRATIONID,

= &= nbsp; t0.ISCHILD, t0.ORIGINATIONTIME, t0.PRODUCEDCOUNT, t0.PRODUCTIONFILENAME, t0.RECEIVERPARTYOID,

= &= nbsp; t0.RECEIVERPARTYID, t0.RECEIVERROUTINGID, t0.RECEIVERROUTINGIDTYPE, t0.REFTOOID,

= &= nbsp; t0.RESUBMITCOUNT, t0.RESUBMITINFO, t0.SENDERPARTYOID, = t0.SENDERPARTYID, t0.SENDERROUTINGID,

= &= nbsp; t0.SENDERROUTINGIDTYPE, t0.TYPE

FROM DBA.MESSAGES t0

INNER JOIN DBA.CUSTOMDATA t1 ON t0.OID =3D = t1.MESSAGEOID

INNER JOIN DBA.CUSTOMDATA t2 ON t0.OID =3D = t2.MESSAGEOID

INNER JOIN DBA.CUSTOMDATA t3 ON t0.OID =3D = t3.MESSAGEOID

INNER JOIN DBA.CUSTOMDATA t4 ON t0.OID =3D = t4.MESSAGEOID

INNER JOIN DBA.CUSTOMDATA t5 ON t0.OID =3D = t5.MESSAGEOID

INNER JOIN DBA.CUSTOMDATA t6 ON t0.OID =3D = t6.MESSAGEOID

INNER JOIN DBA.CUSTOMDATA t7 ON t0.OID =3D = t7.MESSAGEOID

INNER JOIN DBA.CUSTOMDATA t8 ON t0.OID =3D = t8.MESSAGEOID

INNER JOIN DBA.CUSTOMDATA t9 ON t0.OID =3D = t9.MESSAGEOID

INNER JOIN DBA.CUSTOMDATA t10 ON t0.OID =3D = t10.MESSAGEOID

INNER JOIN DBA.CUSTOMDATA t11 ON t0.OID =3D = t11.MESSAGEOID

INNER JOIN DBA.CUSTOMDATA t12 ON t0.OID =3D = t12.MESSAGEOID

INNER JOIN DBA.CUSTOMDATA t13 ON t0.OID =3D = t13.MESSAGEOID

INNER JOIN DBA.MESSAGESTATES t14 ON t0.OID =3D = t14.MESSAGEOID

WHERE (t0.RECEIVERROUTINGID =3D ? =

= AND t0.SENDERROUTINGID =3D ?

= AND t0.DIRECTION =3D ?

= AND t0.ORIGINATIONTIME >=3D ?

= AND t0.ORIGINATIONTIME <=3D ?

= AND t0.DOCUMENTCLASS =3D ?

= AND t0.CONTENTMIMETYPE =3D ?

= AND t1.NAME =3D ?

= AND t1.DATAVALUE =3D ?

= AND t2.NAME =3D ?

= AND t2.DATAVALUE =3D ?

= AND t3.NAME =3D ?

[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 =3D = t1.messageoid

WHERE t0.<Column Name> =3D = ?

…

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 =3D t1.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t2 ON t0.OID = =3D t2.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t3 ON t0.OID =3D t3.MESSAGEOID = INNER JOIN DBA.CUSTOMDATA t4 ON t0.OID =3D t4.MESSAGEOID INNER JOIN DBA.CUSTOMDATA = t5 ON t0.OID =3D t5.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t6 ON t0.OID =3D = t6.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t7 ON t0.OID =3D t7.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t8 ON t0.OID =3D t8.MESSAGEOID INNER JOIN DBA.CUSTOMDATA = t9 ON t0.OID =3D t9.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t10 ON t0.OID =3D = t10.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t11 ON t0.OID =3D t11.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t12 ON t0.OID =3D t12.MESSAGEOID INNER JOIN = DBA.CUSTOMDATA t13 ON t0.OID =3D t13.MESSAGEOID INNER JOIN DBA.MESSAGESTATES t14 ON t0.OID =3D t14.MESSAGEOID WHERE (t0.RECEIVERROUTINGID =3D ? AND t0.SENDERROUTINGID = =3D ? AND t0.DIRECTION =3D ? AND t0.ORIGINATIONTIME >=3D ? AND = t0.ORIGINATIONTIME <=3D ? AND t0.DOCUMENTCLASS =3D ? AND t0.CONTENTMIMETYPE =3D ? AND t1.NAME =3D = ? AND t1.DATAVALUE =3D ? AND t2.NAME =3D ? AND t2.DATAVALUE =3D ? AND t3.NAME = =3D ? AND t3.DATAVALUE =3D ? AND t4.NAME =3D ? AND t4.DATAVALUE =3D ? AND t5.NAME = =3D ? AND t5.DATAVALUE =3D ? AND t6.NAME =3D ? AND t6.DATAVALUE =3D ? AND t7.NAME = =3D ? AND t7.DATAVALUE =3D ? AND t8.NAME =3D ? AND t8.DATAVALUE =3D ? AND t9.NAME = =3D ? AND t9.DATAVALUE =3D ? AND t10.NAME =3D ? AND t10.DATAVALUE =3D ? AND = t11.NAME =3D ? AND t11.DATAVALUE =3D ? AND t12.NAME =3D ? AND t12.DATAVALUE =3D ? AND = t13.NAME =3D ? AND t13.DATAVALUE =3D ? AND t14.TYPE =3D ? AND t14.URL =3D ?) ORDER BY t0.ORIGINATIONTIME = DESC

Thread Dump of thread executing this query in = Derby:

"JettySocketListener0-0" prio=3D6 = tid=3D0x27939748 nid=3D0x838 runnable = [0x2a9ce000..0x2a9cfbe8]

= at org.apache.derby.iapi.util.JBitSet.clearAll(Unknown = Source)

= at org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.initBaseTa= bleVisitor(Unknown Source)

= at org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.valNodeRef= erencesOptTable(Unknown Source)

= at org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.isQualifie= r(Unknown Source)

= at org.apache.derby.impl.sql.compile.PredicateList.hasOptimizableEquijoin(Un= known Source)

= at = org.apache.derby.impl.sql.compile.HashJoinStrategy.findHashKeyColumns(Unk= nown 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.prepareIn= ternalStatement(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(DelegatingConn= ection.java:382)

= at com.solarmetric.jdbc.PoolConnection.prepareStatement(PoolConnection.java:= 114)

= at = com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConn= ection.java:380)

= at com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConn= ection.java:380)

= at = com.solarmetric.jdbc.LoggingConnectionDecorator$LoggingConnection.prepare= Statement(LoggingConnectionDecorator.java:374)

= at com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConn= ection.java:380)

= at kodo.jdbc.runtime.JDBCStoreManager$RefCountConnection.prepareStatement(JD= BCStoreManager.java:1645)

= at com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingConn= ection.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(SelectResultObjectProvi= der.java:102)

= at = com.solarmetric.rop.WindowResultList.<init>(WindowResultList.java:4= 6)

= at kodo.jdbc.runtime.JDBCFetchConfigurationImpl.newResultList(JDBCFetchConfi= gurationImpl.java:164)

= at kodo.runtime.CopyOnWriteFetchConfiguration.newResultList(CopyOnWriteFetch= Configuration.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.retrievePagedResul= ts(JdoQuery.java:201)

= at com.cyclonecommerce.persistence.query.AbstractQuery.executePagedQuery(Abs= tractQuery.java:156)

= at = com.cyclonecommerce.persistence.PagedView.getPage(PagedView.java:82)=

= at = com.cyclonecommerce.ui.messages.MessageQuery.findMessages(MessageQuery.ja= va:1206)

= at com.cyclonecommerce.ui.messages.MessageMetadataQueryAction.serializedAct(= MessageMetadataQueryAction.java:97)

= at = com.cyclonecommerce.ui.core.actions.CycloneAbstractAction.act(CycloneAbst= ractAction.java:46)

= at org.apache.cocoon.components.treeprocessor.sitemap.ActTypeNode.invoke(Act= TypeNode.java:124)

= at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.i= nvokeNodes(AbstractParentProcessingNode.java:46)=

= at org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.in= voke(PreparableMatchNode.java:130)

= at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.i= nvokeNodes(AbstractParentProcessingNode.java:46)=

= at org.apache.cocoon.components.treeprocessor.sitemap.MatchNode.invoke(Match= Node.java:107)

= at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.i= nvokeNodes(AbstractParentProcessingNode.java:68)=

= at org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(Pi= pelineNode.java:142)

= at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.i= nvokeNodes(AbstractParentProcessingNode.java:68)=

= at org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(P= ipelinesNode.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(TreeProc= essor.java:248)

= at = org.apache.cocoon.components.treeprocessor.sitemap.MountNode.invoke(Mount= Node.java:117)

= at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.i= nvokeNodes(AbstractParentProcessingNode.java:46)=

= at org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.in= voke(PreparableMatchNode.java:130)

= at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.i= nvokeNodes(AbstractParentProcessingNode.java:46)=

= at org.apache.cocoon.components.treeprocessor.sitemap.MatchNode.invoke(Match= Node.java:107)

= at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.i= nvokeNodes(AbstractParentProcessingNode.java:68)=

= at = org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(Pi= pelineNode.java:142)

= at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.i= nvokeNodes(AbstractParentProcessingNode.java:68)=

= at = org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(P= ipelinesNode.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(TreeProc= essor.java:248)

= at = org.apache.cocoon.Cocoon.process(Cocoon.java:679)

= at = org.apache.cocoon.servlet.CocoonServlet.service(CocoonServlet.java:1183)<= o:p>

= 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(WebApplicationHa= ndler.java:473)

= at = org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568)<= o:p>

= at = org.mortbay.http.HttpContext.handle(HttpContext.java:1530)

= at = org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationCont= ext.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)<= /o:p>

= 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)<= /o:p>

= at = com.cyclonecommerce.lang.EventedThread.run(EventedThread.java:71)