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 Re: Derby Performance Problem
Date Wed, 12 Apr 2006 04:32:29 GMT
The issue is Derby-1205 and I have attached the db as well as the queries
in question...

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


                                                                           
             Satheesh Bandaram                                             
             <satheesh@Sourcer                                             
             y.Org>                                                     To 
                                       Derby Discussion                    
             04/11/2006 12:09          <derby-user@db.apache.org>          
             PM                                                         cc 
                                                                           
                                                                   Subject 
             Please respond to         Re: Derby Performance Problem       
                  "Derby                                                   
                Discussion"                                                
             <derby-user@db.ap                                             
                 ache.org>                                                 
                                                                           
                                                                           




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

                   <kartha02@gmail.c

                   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"

                   <derby-user@db.ap

                       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













Mime
View raw message