db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Christine Johnson" <cjohn...@willowtech.com>
Subject Performance of IN operator
Date Wed, 20 Sep 2006 20:48:07 GMT
Hello, fellow Derby users

I have a table like this:

 

CREATE TABLE Things (

            thingIdx             BIGINT  NOT NULL GENERATED ALWAYS AS IDENTITY,

            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 example:

 

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?

 

Regards,

Chris Johnson

cjohnson@willowtech.com
Mime
View raw message