Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; CHARSET=US-ASCII; format=flowed
Date: Wed, 29 Jul 2009 06:08:08 -0700
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: Slow performance on 5 or more joins in a star schema
In-reply-to: <64A84ACFB244464D8C7086F909034D5401C92086C7@athena.isdwhq.com>
Sender: Richard.Hillegas@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <4A7049B8.8050101@sun.com>
References: <64A84ACFB244464D8C7086F909034D5401C92086C7@athena.isdwhq.com>
User-Agent: Thunderbird 2.0.0.22 (Macintosh/20090605)

Hi Dustin,

As you note, the Derby query optimizer only considers left-deep trees. I 
have logged an enhancement request for support for bushy query plans: 
DERBY-4327. If many people vote for this issue, we can consider it for 
the next Derby feature release.

Regards,
-Rick

Dustin T. Clifford wrote:
>
> All,
>
>  
>
> We have implemented a star schema and we are noticing that queries 
> involving 5 or more joins perform very slowly. We have, also, noticed 
> that batch updates and deletes perform slowly. We support multiple 
> databases and, as we improve on performance in SQL Server and Oracle, 
> we notice a trend in the opposite direction with Derby.
>
>  
>
> I know that the Derby optimizer use to only consider  left-deep 
>  trees. Is this still true or is there another optimizer that we can 
> use? Does anybody have any advise on working in a manner that can 
> provide good performance on Derby, SQL Server and Oracle? Any advice 
> would be appreciated.
>
>  
>
> *Dustin Clifford*
>
>  
>