[jira] Commented: (DERBY-3716) Impractically slow query

["Evan Leonard (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-3716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12618481#action_12618481 ] 

Evan Leonard commented on DERBY-3716:
-------------------------------------

I'm sorry, its unlikely I'll get back to this issue in the near term.

How do I compress the tables? 

The schema is pretty straightforward some basic bean info ints, varchars, etc on each of the classes listed with join columns on the "id" fields of each. I'm sorry I can't provide more information at this time.

> 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.