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.
