Hi there,

We use Apache Derby in our commercial application, PaperCut NG.  It's proven to be very reliable, however we occasionally get reports of very bad performance in some areas.  We haven't had the time to investigate them fully previously (usually upgrading to an external DB like Postgres or SQL Server fixes the issue).  This time we had a look in more detail with a recent report, and we've found some very strange performance characteristics ... and would love some advice and assistance.

We have a query that is doing inner joins to 5 tables.  It's quite a simple query, but the core table has about 300,000 rows, and where limiting the results based on a date in that table that is indexed.  Here's a summary of my situation/findings:

>From the query plan it seems that seems that it stops using the date index on the "tbl_printer_usage_log" log table, and changes from Hash joins to Nested Loop joins.  On a large table like this when providing a where clause that on a field that is indexed .... we have to ensure that derby uses the index.

If I increase the pageCacheSize to 100,000 pages, it reduces the time of the query to about 2-3 minutes, but it's still very slow compared to when the correct index is used.

Can anyone please help me understand the following:
If we can understand what's causing this, we'll be able to make a much more effective use of Derby.  At the moment, on customers with large datasets, we currently just recommend they "upsize" to Postgres or SQL Server and the problem goes away.  However, we'd much prefer to fix this and have our Derby database behave better.

I'd be happy to provide the derby database that exhibits these problems if someone would like to see what's going on.  The database is from a customer, so I don't want to post it publicly, but if you send me an email off-list I'd be happy to provide it.

Matt Doran
PaperCut Software International Pty. Ltd.
Phone:   +61 (3) 9807 5767
E-mail:  matt.doran@papercut.com
Profile: http://www.papercut.com/about/#matt
Blog:    http://www.papercut.com/blog/