tomconlon7777777 wrote:

> 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);
>

What's the selectivity of Site.RegionID, i.e. how much distincts
RegionID are in the Site table ?

see you !


--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br