RE: Performance of IN operator

[Michael Segel]

I believe that this is a known Derby defect. Does anyone know if this is fixed in the upcoming release?     From: Christine Johnson [mailto:[EMAIL PROTECTED] 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:   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 [EMAIL PROTECTED]