db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: Perfroamnce of IN ( ... list ... ) statements
Date Mon, 06 Oct 2008 15:39:51 GMT
Tim,

The issue is that even though in using other databases, you get "adequate"
performance, the key is that in all the databases, the IN clause can be the
one clause that doesn't perform as well as others. An IN clause using a sub
select could perform much better than an IN clause using a list of values.
Especially if the list gets too long.

If you are using a large list of elements in the IN clause, you may want to
consider trying to put the values in to a temp table and then do an inner
select statement. Or you could do a join. 
Performance here too is going to vary by database. Oracle's temp tables are
a royal pain. Informix's temp tables (Now IBM IDS) are much easier to create
on the fly and you can even index them.

The point I'm trying to make is that even if the IN clause works, you may
want to consider a possible redesign to allow for scalability.

-Mike


> -----Original Message-----
> From: news [mailto:news@ger.gmane.org] On Behalf Of Tim Dudgeon
> Sent: Monday, October 06, 2008 7:02 AM
> To: derby-user@db.apache.org
> Subject: Re: Perfroamnce of IN ( ... list ... ) statements
> 
> derby@segel.com wrote:
> > There was an issue with earlier versions of Derby, that was supposed to
> > have been fixed.
> 
> 
> You are right. I tested with Derby 10.4 and it now looks much better.
> Sorry for the confusion.
> 
> 
> 
> Tim
> 
> >
> >
> >
> > Even so, the performance of the IN clause where you have a large list is
> > going to be an issue in most databases.
> >
> >
> >
> >
> >
> > ------------------------------------------------------------------------
> >
> > *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
> >
> >
> >




Mime
View raw message