We have implemented a star schema and we are noticing that queries involving 5 or more joins perform very slowly. We have, also, noticed that batch updates and deletes perform slowly. We support multiple databases and, as we improve on performance in SQL Server and Oracle, we notice a trend in the opposite direction with Derby.
I know that the Derby optimizer use to only consider left-deep trees. Is this still true or is there another optimizer that we can use? Does anybody have any advise on working in a manner that can provide good performance on Derby, SQL Server and Oracle? Any advice would be appreciated.