It would be good to see the query.... There are couple of existing bugs
that may match the description I have seen so far. There are two major
known issues with subquery optimization (views get expanded as select
subqueries) and some work is being done to address these.
Satheesh
Prasenjit Sarkar wrote:
Looks like the problem is worse in the snapshot version - 6.5 minutes in
Derby vs 2 s in DB2. I'll post a defect in Jira -- I'll probably attach the
database and the query in question...
Prasenjit Sarkar
Research Staff Member
Master Inventor
Storage Systems
IBM Almaden Research
Rajesh Kartha
<[EMAIL PROTECTED]
om> To
Derby Discussion
04/10/2006 06:41 <derby-user@db.apache.org>
PM cc
Subject
Please respond to Re: Derby Performance Problem
"Derby
Discussion"
<[EMAIL PROTECTED]
ache.org>
Hi,
I am wondering if it is related to the issue -
http://issues.apache.org/jira/browse/DERBY-649
If you have an older version (than 10.1.2.3), is it possible to re-run
you scenario
using the newer snapshot jars posted at:
http://db.apache.org/derby/derby_downloads.html#Snapshot+Jars
Please do post your findings.
Regards,
Rajesh
Prasenjit Sarkar wrote:
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
|
- Re: Derby Performance Problem Satheesh Bandaram
-
