Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 88970 invoked from network); 11 Oct 2006 22:04:38 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 11 Oct 2006 22:04:38 -0000 Received: (qmail 80543 invoked by uid 500); 11 Oct 2006 22:04:37 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 80510 invoked by uid 500); 11 Oct 2006 22:04:37 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 80499 invoked by uid 99); 11 Oct 2006 22:04:37 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 11 Oct 2006 15:04:37 -0700 X-ASF-Spam-Status: No, hits=2.0 required=10.0 tests=HTML_MESSAGE,UPPERCASE_25_50 X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [65.195.181.55] (HELO dbrack01.segel.com) (65.195.181.55) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 11 Oct 2006 15:04:34 -0700 Received: from Desktop02 (desktop02.segel.com [65.195.181.45]) by dbrack01.segel.com (Postfix - We shoot spammers on site.) with ESMTP id 40BDD4BA9C for ; Wed, 11 Oct 2006 17:11:30 -0500 (CDT) Reply-To: From: "Michael Segel" To: "'Derby Discussion'" Subject: RE: Derby Optimizer Hangs Date: Wed, 11 Oct 2006 17:04:09 -0500 Organization: MSCC MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002C_01C6ED57.448BAD20" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 In-Reply-To: <97085FEE4C8BDB4AB6FA3E770EBC79BB5913AE@mail1.cyclonecommerce.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 thread-index: AcbtZdEVLTAqFj1/SLesQkAuAMCPCAAFONcQAAC5RUAAADivgAAAZ75g Message-Id: <20061011221130.40BDD4BA9C@dbrack01.segel.com> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N This is a multi-part message in MIME format. ------=_NextPart_000_002C_01C6ED57.448BAD20 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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. = ? . 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. = ? . 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.(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.(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.(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) ------=_NextPart_000_002C_01C6ED57.448BAD20 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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 =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)

 

 

 

 

 

------=_NextPart_000_002C_01C6ED57.448BAD20--