[
https://issues.apache.org/jira/browse/DERBY-5555?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen updated DERBY-5555:
--------------------------------------
Attachment: derby.log
I could reproduce this in my environment. Actually, the second execution of the
PreparedStatement failed with an OutOfMemoryError, but the first execution took
a very long time, as reported.
The query executed with a Statement is:
SELECT ID, MSG FROM ACTIVEMQ_MSGS WHERE CONTAINER='queue://queue' AND ID > 100
ORDER BY ID
The PreparedStatement executes this query:
SELECT ID, MSG FROM ACTIVEMQ_MSGS WHERE CONTAINER=? AND ID > ? ORDER BY ID
I ran the test case with logging of query plans
(derby.language.logQueryPlan=true, see the attached derby.log for the detailed
plans). The first query uses the primary key index on the ID column. The second
query uses a non-unique index on the CONTAINER column to retrieve the matching
rows, and then sorts those results.
The optimizer probably makes a guess that the predicate CONTAINER=? won't match
that many rows, in which case using an index on CONTAINER and sorting would be
a good strategy. This turns out to be a bad decision when the parameter is set
to a value that matches every row in the table at execution time.
In the first query, the predicate CONTAINER='queue://queue' is known at prepare
time to match a high number of rows, so the optimizer avoids making the same
mistake there.
> Execution Time with a Statement 1 ms and with a PreparedStatement about 4
> minutes
> ---------------------------------------------------------------------------------
>
> Key: DERBY-5555
> URL: https://issues.apache.org/jira/browse/DERBY-5555
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.8.1.2, 10.8.2.2
> Reporter: Steffen Kuche
> Attachments: derby.log, test.zip
>
>
> Sounds incrediable? Test it by yourself with the attached program.
> The issued reased up because of AMQ-3644, but it is actually a derby problem
> so I post the issue here as well.
> Derby writes multiple gigabyte of temporary files in its tmp directory during
> one select statement. Afterwards these temporary files are deleted and
> rebuild by the next Statement.
> You can find a Test-Program (import it as a Maven-Eclipse project) which
> reproduces this issue.
> This program creates a derby database with the activemq Message table and
> fills it with data (2000 Messages, this can take several hours). I assumed
> 3MB for one message as our messages contain same binary data and has an
> average size of 3MB.
> After the database is built one time a Statement and another time a
> PreparedStatment is used to retrieve the next messages.
> The Statement takes about 1 ms and the PreparedStatement about 258805 ms.
> Also the second PreparedStatement takes as much time.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
