Hi,

The query below seems to use all available indexes but the
performance is bizarrely slow (v1.53). It stood out as it took 3mins
at a client site so then it was ran on a dev machine. If anyone has
any ideas/suggestions it would be appreciated.

Thanks,
Tom

-----------------------------------------------------------
SELECT c.clntid, c.surname, c.forename, c.title, c.siteid, v.vacid,
v.jobtitle, v.vactype, v.rate, v.isactive, co.compname, c.email
FROM Vacancy v
JOIN Client c ON c.clntid=v.clntid
JOIN Site s ON c.siteid=s.siteid
JOIN Company co ON s.compid=co.compid
WHERE ( ( s.REGIONID IN(3,1,2,4) ) )

PLAN JOIN (S INDEX
(IDXSITE_REGIONID,IDXSITE_REGIONID,IDXSITE_REGIONID,IDXSITE_REGIONID),CO
INDEX (PK_COMPANY),C INDEX (FK1_CLIENT),V INDEX (FK1_VACANCY))

------ Performance info ------
Prepare time = 0ms
Execute time = 8m 28s 594ms (!)

Company count: 32484
Site count: 38837
Client count: 117194
Vacancy count: 21755

/*** Primary Keys
ALTER TABLE CLIENT ADD CONSTRAINT PK_CLIENT PRIMARY KEY (CLNTID);
ALTER TABLE COMPANY ADD CONSTRAINT PK_COMPANY PRIMARY KEY (COMPID);
ALTER TABLE SITE ADD CONSTRAINT PK_SITE PRIMARY KEY (SITEID);
ALTER TABLE VACANCY ADD CONSTRAINT PK_VACANCY PRIMARY KEY (VACID);


/*** Foreign Keys
ALTER TABLE CLIENT ADD CONSTRAINT FK1_CLIENT FOREIGN KEY (SITEID)
REFERENCES SITE (SITEID);
ALTER TABLE CLIENT ADD FOREIGN KEY (STAFFID) REFERENCES STAFF (STAFFID);
ALTER TABLE COMPANY ADD FOREIGN KEY (SICID) REFERENCES SIC (SICID);
ALTER TABLE SITE ADD CONSTRAINT FK1_SITE FOREIGN KEY (COMPID)
REFERENCES COMPANY (COMPID);
ALTER TABLE VACANCY ADD CONSTRAINT FK1_VACANCY FOREIGN KEY (CLNTID)
REFERENCES CLIENT (CLNTID);
ALTER TABLE VACANCY ADD CONSTRAINT FK2_VACANCY FOREIGN KEY (STAFFID)
REFERENCES STAFF (STAFFID);


/*** Indices
CREATE INDEX IDXSITE_REGIONID ON SITE (REGIONID);