db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chris Wilson <ch...@aptivate.org>
Subject SQL performance with complex query
Date Fri, 16 Apr 2010 13:51:17 GMT
Dear sirs,

I have a query that generates about 2,000 rows in a summary table. It runs 
fast enough (~30 seconds) on MySQL. The same query on Derby runs for about 
10 minutes and then fails with an OutOfMemoryError.

I have created a test case to reproduce the problem. It's not minimal 
because it relies on a rather large dataset to reproduce it, and it's not 
trivial, but I don't mind doing a bit of work trimming it if someone can 
point me in the necessary direction.

You can download the test case here:

  http://rita.wfplogistics.org/svn/trackrita/rita/doc/derby-oom-slow-query

which includes a pre-built Derby database in "testdb.derby". If this 
database is deleted, "test.sh" will recreate it, but that takes about 
10-15 minutes.

Just modify the script "test.sh" to point to your Derby libraries, and 
run it (or just execute the commands in "movement_complete.sql") to 
demonstrate the problem. You can view the source of that file online here:

http://rita.wfplogistics.org/trac/browser/rita/conf/movement_complete.sql

The first "insert into movement_complete" (starting around line 4) takes 
about 15 seconds to complete and inserts 5890 rows. The second, starting 
around line 54, does not complete in reasonable time on Derby. On MySQL, 
it runs in 28 seconds and inserts 2038 rows. On Derby, after 10 minutes I 
get:

JAVA ERROR: java.lang.OutOfMemoryError: Java heap space
ij> ERROR X0Y67: Cannot issue rollback in a nested connection when there 
is a pending operation in the parent connection.
(process exits)

I think this could be a case where the query optimizer could use some 
work? Is it useful for me to file a bug report somewhere, e.g. in JIRA? 
Can anyone suggest how I could work around the OOM error?

Cheers, Chris.
-- 
Aptivate | http://www.aptivate.org | Phone: +44 1223 760887
The Humanitarian Centre, Fenner's, Gresham Road, Cambridge CB1 2ES

Aptivate is a not-for-profit company registered in England and Wales
with company number 04980791.

Mime
View raw message