db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Segel" <mse...@segel.com>
Subject RE: Performance of IN operator
Date Wed, 20 Sep 2006 22:19:41 GMT
I believe that this is a known Derby defect.

Does anyone know if this is fixed in the upcoming release?




From: Christine Johnson [mailto:cjohnson@willowtech.com] 
Sent: Wednesday, September 20, 2006 3:48 PM
To: Derby Discussion
Subject: Performance of IN operator


Hello, fellow Derby users


I have a table like this:



          thingIdx             BIGINT  NOT NULL GENERATED ALWAYS AS

          thingName         VARCHAR(80) NOT NULL,

          ...         // bunch of other columns not relevant to my problem

          CONSTRAINT Things_PK PRIMARY KEY (thingIdx)



I find that when I want to select a bunch of non-contiguous values, for


SELECT thingIdx, thingName FROM Things WHERE thingIdx IN (?, ?, ?, ?, ...
[100 values scattered throughout index])


performance is pretty slow -- a bit over a minute for 100 records.  The same
SQL for the same data runs in less than one second on SQL Server, Oracle,
and DB2.  If the values are contiguous (e.g., thingIdx IN (1, 2, 3, 4, 5, 6,
...) Derby's performance is comparable to that of the other databases.  But
if I were looking for contiguous records, I'd use BETWEEN, not IN.


Has anyone encountered this before, and can you suggest how to improve
performance when using IN?



Chris Johnson


View raw message