It *may* be that the reason for the slow execution is outdated index cardinality statistics. Check out "CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE" either on nabble or in the derby docs.
Did you get around to try out the tip from Thomas?
If you first create the index and then load the data, the statistics can be badly outdated. If you first created the table, loaded the data and finally created the index you should be fine though.
Just curious, as we have seen reports of severe performance degradations because of this issue, which causes the optimizer to choose a bad plan.
I created example of application. All app merged into one file. All in this topic can run app and get the same results.
final private WORK_TYPE work_type should be set to DATA_FROM_SCRATCH at first run.
Than use DATA_DROP_AND_CREATE for recreating data or DATA_USE_OLD to use old data.
Anyone can use app and get the same results as i. I am interested in high speed of calculateTotalStored or calculateTotalClient methods ONLY!!!
PS this app for testing purposes only, so code re-usability and other things like it are not important.