--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:

>
> Hi Arno!
>
> How come this works? Admittedly, I haven't seen any plan, but it just
> seems strange to repeat the Candidateid for every Attributeid. Is is
> due to using an index on t5.Attributeid and putting this first in the
> plan? Would using
>
> JOIN CandidateAttribute t5 ON (t5.Candidateid=t0.Candidateid
> AND t5.Attributeid+0 IN(3916,1402,784,3461,2184))
>
> or (probably better)
>
> JOIN CandidateAttribute t5 ON (t5.Candidateid=t0.Candidateid+0
> AND t5.Attributeid IN(3916,1402,784,3461,2184))
>
> have a similar effect?
>
> I'm curious,
> Set

Hi Set,

1. Your suggestion also resulted in very good performance.
2. Although slightly slower, (with this level of data) your suggestion
has an added benefit of being easier to generate the SQL.
3. Using FB 1.5.3.4870 on Win XP Pro, 2GB ram, P4 3.80GHz, DB page
size=16384, DefaultDbCachePages=8192. The FB service was stopped and
started before each run.
5. The existence or non-existence of 'standard' foreign key
declarations appears to have a massive impact on performance (> 5mins!)
6. The statements below add an additional join back to candidate as
this is often required with further candidate-specific criteria.

SELECT
distinct c.candidateid
FROM
Candidate c
JOIN CandidateAttribute t0 ON (t0.Candidateid=c.Candidateid AND
t0.Attributeid=256)
JOIN CandidateAttribute t1 ON (t1.Candidateid=c.Candidateid AND
t1.Attributeid=34 )
JOIN CandidateAttribute t2 ON (t2.Candidateid=c.Candidateid AND
t2.Attributeid=321 )
JOIN CandidateAttribute t3 ON (t3.Candidateid=c.Candidateid AND
t3.Attributeid=3563 )
JOIN CandidateAttribute t4 ON (t4.Candidateid=c.Candidateid AND
t4.Attributeid=29 )
JOIN CandidateAttribute t5 ON
(
(t5.Candidateid=c.Candidateid AND t5.Attributeid = 3916) or
(t5.Candidateid=c.Candidateid AND t5.Attributeid = 1402) or
(t5.Candidateid=c.Candidateid AND t5.Attributeid =784) or
(t5.Candidateid=c.Candidateid AND t5.Attributeid = 3461) or
(t5.Candidateid=c.Candidateid AND t5.Attributeid = 2184)
)
WHERE
c.candidateid>0

Adapted Plan
PLAN SORT (JOIN (T0 INDEX
(IDXCANDIDATEATTRIBUTE,PK_CANDIDATEATTRIBUTE),T4 INDEX
(PK_CANDIDATEATTRIBUTE),T3 INDEX (PK_CANDIDATEATTRIBUTE),T2 INDEX
(PK_CANDIDATEATTRIBUTE),T1 INDEX (PK_CANDIDATEATTRIBUTE),C INDEX
(PK_CANDIDATE),T5 INDEX
(PK_CANDIDATEATTRIBUTE,PK_CANDIDATEATTRIBUTE,PK_CANDIDATEATTRIBUTE,PK_CANDIDATEATTRIBUTE,PK_CANDIDATEATTRIBUTE)))

------ Performance info ------
Prepare time = 0ms
Execute time = 2s 15ms
Avg fetch time = 2.51 ms
Current memory = 5,104,800
Max memory = 5,154,556
Memory buffers = 8,192
Reads from disk to cache = 11,532
Writes from cache to disk = 0
Fetches from cache = 341,444

-------------------
SELECT
distinct c.candidateid
FROM
Candidate c
JOIN CandidateAttribute t0 ON (t0.Candidateid=c.Candidateid AND
t0.Attributeid=256)
JOIN CandidateAttribute t1 ON (t1.Candidateid=c.Candidateid AND
t1.Attributeid=34 )
JOIN CandidateAttribute t2 ON (t2.Candidateid=c.Candidateid AND
t2.Attributeid=321 )
JOIN CandidateAttribute t3 ON (t3.Candidateid=c.Candidateid AND
t3.Attributeid=3563 )
JOIN CandidateAttribute t4 ON (t4.Candidateid=c.Candidateid AND
t4.Attributeid=29 )
JOIN CandidateAttribute t5 ON (t5.Candidateid=t0.Candidateid
AND t5.Attributeid+0 IN(3916,1402,784,3461,2184))
WHERE
c.candidateid>0



PLAN SORT (JOIN (T0 INDEX
(IDXCANDIDATEATTRIBUTE,PK_CANDIDATEATTRIBUTE),T4 INDEX
(PK_CANDIDATEATTRIBUTE),T3 INDEX (PK_CANDIDATEATTRIBUTE),T2 INDEX
(PK_CANDIDATEATTRIBUTE),T5 INDEX (PK_CANDIDATEATTRIBUTE),T1 INDEX
(PK_CANDIDATEATTRIBUTE),C INDEX (PK_CANDIDATE)))

------ Performance info ------
Prepare time = 15ms
Execute time = 2s 235ms
Avg fetch time = 2.79 ms
Current memory = 5,094,404
Max memory = 5,144,164
Memory buffers = 8,192
Reads from disk to cache = 11,483
Writes from cache to disk = 0
Fetches from cache = 530,197



Tom