coalesce in where clause results in timeout [message #524550] |
Thu, 01 April 2010 11:42 |
Vasco Figueira Messages: 6 Registered: April 2010 |
Junior Member |
|
|
Dear all,
We have a query along the lines of:
@NamedQuery(name = "Organizacao.findByConcelhoId", query =
"SELECT co.contactoOrganizacaoPK.fkOrganizacao " +
"FROM ContactoOrganizacao co, Contacto c, " +
"Morada m, CodigoPostal cp " +
"WHERE co.contactoOrganizacaoPK.fkContacto = c.id AND c.fkMorada = m.id " +
"AND (m.fkCodigoPostal is null OR m.fkCodigoPostal = cp.id) " +
"AND coalesce(m.fkConcelho, cp.fkConcelho) = :fkConcelho")
(Have to match given Id with either fkConcelho on Morada or fkConcelho on CodigoPostal "outer joined" with Morada).
The NamedQuery gets translated to the SQL:
SELECT t0.FK_ORGANIZACAO
FROM CODIGO_POSTAL t3, MORADA t2, CONTACTO t1, CONTACTO_ORGANIZACAO t0
WHERE ((
((t0.FK_CONTACTO = t1.ID) AND (t1.FK_MORADA = t2.ID))
AND ((t2.FK_CODIGO_POSTAL IS NULL) OR (t2.FK_CODIGO_POSTAL = t3.ID))
) AND (COALESCE(t2.FK_CONCELHO,t3.FK_CONCELHO ) = '227'))
...that runs fine (as SQL, using an SQL client). However, querying via eclipselink takes too long and breaks with a timeout.
[EL Fine]: 2010-04-01 12:26:59.986--ServerSession(11738152)--Connection(64055960)--Thread(Thread[[ACTIVE] ExecuteThread: '19' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads])--SELECT t0.FK_ORGANIZACAO FROM CODIGO_POSTAL t3, MORADA t2, CONTACTO t1, CONTACTO_ORGANIZACAO t0 WHERE ((((t0.FK_CONTACTO = t1.ID) AND (t1.FK_MORADA = t2.ID)) AND ((t2.FK_CODIGO_POSTAL IS NULL) OR (t2.FK_CODIGO_POSTAL = t3.ID))) AND (COALESCE(t2.FK_CONCELHO,t3.FK_CONCELHO ) = ?))
bind => [227]
[EL Finer]: 2010-04-01 12:27:48.825--UnitOfWork(64048396)--Thread(Thread[[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads])--TX afterCompletion callback, status=ROLLEDBACK
[EL Finer]: 2010-04-01 12:27:48.825--UnitOfWork(64048396)--Thread(Thread[[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads])--release unit of work
[EL Finer]: 2010-04-01 12:27:48.825--ClientSession(63235017)--Thread(Thread[[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads])--client released
javax.ejb.EJBException: Transaction Rolledback.: weblogic.transaction.internal.TimedOutException: Transaction timed out after 33 seconds
BEA1-000687F032CA884F477B
at weblogic.transaction.internal.ServerTransactionImpl.wakeUp(ServerTransactionImpl.java:1734)
at weblogic.transaction.internal.ServerTransactionManagerImpl.processTimedOutTransactions(ServerTransactionManagerImpl.java:1607)
at weblogic.transaction.internal.TransactionManagerImpl.wakeUp(TransactionManagerImpl.java:1879)
at weblogic.transaction.internal.ServerTransactionManagerImpl.wakeUp(ServerTransactionManagerImpl.java:1517)
at weblogic.transaction.internal.WLSTimer.timerExpired(WLSTimer.java:35)
at weblogic.timers.internal.TimerImpl.run(TimerImpl.java:273)
at weblogic.work.SelfTuningWorkManagerImpl$WorkAdapterImpl.run(SelfTuningWorkManagerImpl.java:516)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:201)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:173)
; nested exception is: weblogic.transaction.internal.TimedOutException: Transaction timed out after 33 seconds
The reason we don't simply use LEFT OUTER JOIN on the "optional" Freguesia table is mainly historical as we decided not to use relations annotations by fear of previously encountered problems with older versions of JPA providers.
But it's supposed to work like this, right? Should I file a bug? Any ideas?
Thanks in advance.
[Updated on: Thu, 01 April 2010 11:56] Report message to a moderator
|
|
|
|
Powered by
FUDForum. Page generated in 0.03449 seconds