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: domain of gcaddu-derby-user@m.gmane.org
 designates 80.91.229.2 as permitted sender)
To: derby-user@db.apache.org
From: Tim Dudgeon <tdudgeon@informaticsmatters.com>
Subject: Re: Perfroamnce of IN ( ... list ... ) statements
Date: Mon, 06 Oct 2008 09:58:26 +0100
Lines: 55
Message-ID: <gcck2j$861$2@ger.gmane.org>
References: <4d2670740810051311x4a4762che62f7c456cd326c2@mail.gmail.com>
 <20081005223644.7D2D75FB78@dbrack01.segel.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 8bit
User-Agent: Thunderbird 2.0.0.17 (Windows/20080914)
In-Reply-To: <20081005223644.7D2D75FB78@dbrack01.segel.com>
Sender: news <news@ger.gmane.org>

derby@segel.com wrote:
> There was an issue with earlier versions of Derby, that was supposed to 
> have been fixed.
> 
>  
> 
> Even so, the performance of the IN clause where you have a large list is 
> going to be an issue in most databases.

I'm running against 3 different databases. With Oracle and MySQL IN 
clause performance is satisfactory. With Derby it is not and its much 
faster to retrieve each row individually, which seems to indicate that 
something is amiss.

Tim

> 
>  
> 
>  
> 
> ------------------------------------------------------------------------
> 
> *From:* Peter Ondru�ka 
> [mailto:peter.ondruska@gmail.com]
> *Sent:* Sunday, October 05, 2008 3:12 PM
> *To:* Derby Discussion
> *Subject:* Re: Perfroamnce of IN ( ... list ... ) statements
> 
>  
> 
> What Derby version is it?
> 
> How does it perform if you re-create index on foo_id column?
> 
>  
> 
> p. 
> 
> On Sun, Oct 5, 2008 at 10:07 PM, Tim Dudgeon 
> <tdudgeon@informaticsmatters.com 
> <mailto:tdudgeon@informaticsmatters.com>> wrote:
> 
> If I run a statement like this is Derby:
> SELECT COL1, COL2 FROM FOO WHERE FOO_ID IN ( 1, 31, ..... 4567)
> it seems unexpectedly slow. Its actually faster to retrieve each row 
> individually using a for loop that it is to use the IN ( ..  list ... ) 
> clause. This seems strange.
> 
> Thanks
> 
> Tim
> 
>  
>