Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <213949800.1217526992015.JavaMail.jira@brutus>
Date: Thu, 31 Jul 2008 10:56:32 -0700 (PDT)
From: "Kathey Marsden (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-3716) Impractically slow query
In-Reply-To: <547467375.1213044525116.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

Kathey Marsden commented on DERBY-3716:
---------------------------------------

To compress the tables, run:
   call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', '<tableName>', 1); 
on all the tables in your database.  See:
http://db.apache.org/derby/docs/10.4/ref/rrefaltertablecompress.html


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