Hi, We are porting a commercial application from DB2 to Derby and have run into a performance issue. Our application has a very complex data model and uses four levels of views for some reports. We are facing a performance problem in joining views at the second level.
To illustrate an example, VIEW_L2_1 and VIEW_L2_2 are two views at the second level. Both VIEW_L2_1 and VIEW_L2_2 compute very fast (<1s). For the experiment in question, the cardinality of VIEW_L2_1 and VIEW_L2_2 is only 300 and 10 respectively - each row in VIEW_L2_1 and VIEW_L2_2 has less than 128 bytes of data. So, we are not talking large datasets here. Both views are dependent on some common views at the first level. The issue is this: a join of VIEW_L2_1 and VIEW_L2_2 on a simple equality condition (on a column each from one view) takes 2-3 minutes on Derby, while the equivalent query in DB2 computes very fast (<1s). It looks like that the Derby query engine is CPU-bound for the most part during the time. The statistics obtained do not shed much light on this issue. I'm fairly new to Derby and would like some direction on how to proceed. Thanks, Prasenjit Sarkar Research Staff Member Master Inventor Storage Systems IBM Almaden Research
