Hi All,

FB is very fast (~1 sec) when all values are mandatory:

SELECT t0.candidateid
FROM candidateAttribute t0
JOIN CandidateAttribute t1 ON (t1.Candidateid=t0.Candidateid AND
t1.Attributeid=34 AND T1.RATING<200)
JOIN CandidateAttribute t2 ON (t2.Candidateid=t0.Candidateid AND
t2.Attributeid=321 )
JOIN CandidateAttribute t3 ON (t3.Candidateid=t0.Candidateid AND
t3.Attributeid=3563 )
JOIN CandidateAttribute t4 ON (t4.Candidateid=t0.Candidateid AND
t4.Attributeid=29 )
WHERE t0.Attributeid =256


However, add this line (or a NOT variation) for optional attributes
and FB takes over 3minutes with the server working very hard:
JOIN CandidateAttribute t5 ON (t5.Candidateid=t0.Candidateid AND
t5.Attributeid IN(3916,1402,784,3461,2184))

I've tried everything I can (EXISTS variations, stored procedures,
JOIN variations, GROUP BYs, etc) but cannot get around a serious
performance problem.

If anyone has got around this problem with larg-ish tables I'd be
delighted to know how.

CANDIDATEATTRIBUTE TABLE
------------------------
COUNT: 2.6 million
HIGHEST OCCURING ATTRIBUTES:

COUNT ATTRIBUTEID ATTRIBUTENAME
----- ----------- -------------
45396 256 NT
39282 34 SUPPORT
31800 321 UNIX
31755 3563 PERM
29273 29 PROGRAMMER
27782 2184 SQL
25369 3461 MANAGEMENT

DDL
---

CREATE TABLE CANDIDATEATTRIBUTE
(
CANDIDATEID INTEGER DEFAULT 0 NOT NULL,
ATTRIBUTEID NTEGER DEFAULT 0 NOT NULL,
RATING INTEGER DEFAULT 10000 NOT NULL,
DATEFROM DATE,
DATETO DATE,
NOTES BLOB SUB_TYPE 1 SEGMENT SIZE 512,
ISCORE INTEGER DEFAULT 0
);

ALTER TABLE CANDIDATEATTRIBUTE ADD CONSTRAINT PK_CANDIDATEATTRIBUTE
PRIMARY KEY (CANDIDATEID, ATTRIBUTEID);
CREATE UNIQUE INDEX IDXCANDIDATEATTRIBUTE ON CANDIDATEATTRIBUTE
(ATTRIBUTEID, RATING, CANDIDATEID);
NOTE: The 2 foreign keys (on candidateid and attributeid) have
been dropped for now and may be maintained by triggers instead.


TIA,
Tom