db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-3716) Impractically slow query
Date Wed, 30 Jul 2008 18:53:32 GMT

     [ https://issues.apache.org/jira/browse/DERBY-3716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Kathey Marsden updated DERBY-3716:
----------------------------------

    Component/s: SQL

Thanks Evan for submitting the issue.  Can you give us the schema or a database so we can
try out the query?

Can you try compressing the tables to see if that updates statistics and improves things at
all?


> Impractically slow query
> ------------------------
>
>                 Key: DERBY-3716
>                 URL: https://issues.apache.org/jira/browse/DERBY-3716
>             Project: Derby
>          Issue Type: Bug
>          Components: Performance, SQL
>    Affects Versions: 10.4.1.3
>         Environment: tried on both win2k and  os x
> java 1.6, hibernate
>            Reporter: Evan Leonard
>
> Here's the query I'm trying to run in hibernate query language(hql). I'll give a version
in sql afterward. It contains a number joins and a sum. When the tables are empty the query
completes. When there are more than ~20 rows in these tables the query (practially speaking)
never completes.  I've looked at the thread dumps while its running and they're in data.row
something, and don't appear to be dead-locked according to the JVM. Other databases execute
this query without a problem
> ---------hql---------------
> select sum(bd.length)
> from User u, WorkspaceBean w, ExchangeEntry ee, ExchangeBean e, MessageBase m, BinaryData
bd
> where (u = :user) and
>          ((u = w.creator and w = ee.workspace and e = ee.exchange and m = e.input and
m.binData = bd) or
>           (u = w.creator and w = ee.workspace and e = ee.exchange and m = e.output and
m.binData = bd))
> ---------sql--------------
> select sum(bd.length)
> from User u, WorkspaceBean w, ExchangeEntry ee, ExchangeBean e, MessageBase m, BinaryData
bd
> where (u.id = @userId) and
>          ((u.id = w.creatorId and w.id = ee.workspaceId and e.id = ee.exchangeId and
m.id = e.inputId and m.binDataId = bd.id) or
>           (u.id = w.creatorId and w.id = ee.workspaceId and e.id = ee.exchangeId and
m.id = e.outputId and m.binDataId = bd.id))
> -------------------------
> Thank you!
> Evan

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message