Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 50920 invoked from network); 5 Oct 2008 22:37:15 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 5 Oct 2008 22:37:15 -0000 Received: (qmail 48739 invoked by uid 500); 5 Oct 2008 22:37:13 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 48711 invoked by uid 500); 5 Oct 2008 22:37:13 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 48700 invoked by uid 99); 5 Oct 2008 22:37:13 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 05 Oct 2008 15:37:13 -0700 X-ASF-Spam-Status: No, hits=2.1 required=10.0 tests=DNS_FROM_SECURITYSAGE,HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [67.103.199.55] (HELO dbrack01.segel.com) (67.103.199.55) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 05 Oct 2008 22:36:10 +0000 Received: from Desktop02 (desktop02.segel.com [67.103.199.45]) by dbrack01.segel.com (Postfix - We shoot spammers on site.) with ESMTP id 7D2D75FB78 for ; Sun, 5 Oct 2008 17:36:44 -0500 (CDT) Reply-To: From: Sender: "Michael Segel" To: "'Derby Discussion'" Subject: RE: Perfroamnce of IN ( ... list ... ) statements Date: Sun, 5 Oct 2008 17:35:28 -0500 Organization: MSCC MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0071_01C92710.C20901E0" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Thread-Index: AcknJrLftAKQBb66Q5+ueg7n8tIQqgAE7PTQ In-Reply-To: <4d2670740810051311x4a4762che62f7c456cd326c2@mail.gmail.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350 Message-Id: <20081005223644.7D2D75FB78@dbrack01.segel.com> X-Virus-Checked: Checked by ClamAV on apache.org This is a multi-part message in MIME format. ------=_NextPart_000_0071_01C92710.C20901E0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable There was an issue with earlier versions of Derby, that was supposed to = have been fixed. =20 Even so, the performance of the IN clause where you have a large list is going to be an issue in most databases. =20 =20 _____ =20 From: Peter Ondru=B9ka [mailto:peter.ondruska@gmail.com]=20 Sent: Sunday, October 05, 2008 3:12 PM To: Derby Discussion Subject: Re: Perfroamnce of IN ( ... list ... ) statements =20 What Derby version is it? How does it perform if you re-create index on foo_id column? =20 p.=20 On Sun, Oct 5, 2008 at 10:07 PM, Tim Dudgeon 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 =20 ------=_NextPart_000_0071_01C92710.C20901E0 Content-Type: text/html; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable

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.

From: Peter Ondru=B9ka [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?

On Sun, Oct 5, 2008 at 10:07 PM, Tim Dudgeon <tdudgeon@informaticsmatte= rs.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

------=_NextPart_000_0071_01C92710.C20901E0--