For example, this morning I started my app and did few 'app stuff', the queries took much longer. I closed my app/JVM, started again and did the exact same 'app stuff', and the queries were much faster.
Is it possible that this has nothing to do with Derby, but is instead some other behavior on your app or system? Since Derby is just running as ordinary Java code in your JVM, it can be affected by starvation of resources if for whatever reason your application or system is busy. If the overall query plan is basically identical between the "bad" run and the "good" run (same overall query plan, same number of records/pages touched, etc.), then perhaps when you get the slow runs, the reason is that some other Java code in your application is stealing all the CPU time, or some other process running on your system is stealing all the resources. Try hooking up a tool like VisualVM to your application while it's running, and see if you can see any differences in overall JVM behavior between the slow runs and the fast runs. thanks, bryan
