db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Prasenjit Sarkar <psar...@almaden.ibm.com>
Subject Derby Performance Problem
Date Mon, 10 Apr 2006 22:39:33 GMT

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.


Prasenjit Sarkar
Research Staff Member
Master Inventor
Storage Systems
IBM Almaden Research

View raw message