db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: Derby Performance Problem
Date Tue, 11 Apr 2006 19:09:32 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
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.<br>
<br>
Satheesh<br>
<br>
Prasenjit Sarkar wrote:
<blockquote
 cite="midOF1C72ADA4.40CF050A-ON8825714D.005DA9B8-8825714D.005DEACE@us.ibm.com"
 type="cite">
  <pre wrap="">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                                                 
             <a class="moz-txt-link-rfc2396E" href="mailto:kartha02@gmail.com">&lt;kartha02@gmail.c
                                            
             om&gt;</a>                                                        To

                                       Derby Discussion                    
             04/10/2006 06:41          <a class="moz-txt-link-rfc2396E" href="mailto:derby-user@db.apache.org">&lt;derby-user@db.apache.org&gt;</a>
         
             PM                                                         cc 
                                                                           
                                                                   Subject 
             Please respond to         Re: Derby Performance Problem       
                  "Derby                                                   
                Discussion"                                                
             <a class="moz-txt-link-rfc2396E" href="mailto:derby-user@db.apache.org">&lt;derby-user@db.ap
                                            
                 ache.org&gt;</a>                                              
  
                                                                           
                                                                           




Hi,

I am wondering if it is related to the issue -
<a class="moz-txt-link-freetext" href="http://issues.apache.org/jira/browse/DERBY-649">http://issues.apache.org/jira/browse/DERBY-649</a>

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:
<a class="moz-txt-link-freetext" href="http://db.apache.org/derby/derby_downloads.html#Snapshot+Jars">http://db.apache.org/derby/derby_downloads.html#Snapshot+Jars</a>

Please do post your findings.

Regards,
Rajesh



Prasenjit Sarkar wrote:

  </pre>
  <blockquote type="cite">
    <pre wrap="">Hi,

We are porting a commercial application from DB2 to Derby and have run
    </pre>
  </blockquote>
  <pre wrap=""><!---->into
  </pre>
  <blockquote type="cite">
    <pre wrap="">a performance issue. Our application has a very complex data model
and
    </pre>
  </blockquote>
  <pre wrap=""><!---->uses
  </pre>
  <blockquote type="cite">
    <pre wrap="">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 (&lt;1s). For
    </pre>
  </blockquote>
  <pre wrap=""><!---->the
  </pre>
  <blockquote type="cite">
    <pre wrap="">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
    </pre>
  </blockquote>
  <pre wrap=""><!---->than
  </pre>
  <blockquote type="cite">
    <pre wrap="">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 (&lt;1s). It looks like
that the Derby query engine is CPU-bound for the most part during the
    </pre>
  </blockquote>
  <pre wrap=""><!---->time.
  </pre>
  <blockquote type="cite">
    <pre wrap="">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




    </pre>
  </blockquote>
  <pre wrap=""><!---->




  </pre>
</blockquote>
</body>
</html>


Mime
View raw message