Hi,

Performance on combinations of mandatory or mandatory + optional is
fantastic, thanks again to Arno.

Implementing the <> test is proving not so
straightforward and it seems my logic is wrong.

Common SQL:
-----------
SELECT [DISTINCT if OR/NOT] candidate.candidateid
FROM candidate
JOIN candidateAttribute t1 ON (t1.candidateid=candidate.candidateid
AND t1.Attributeid = 29 )
...
JOIN candidateAttribute t6 ON (t6.candidateid=candidate.candidateid
AND t6.Attributeid = 3916 )
<..... VARIATIONS GO HERE .....>
WHERE [candidate.candidateid>0] AND [other candidate criteria]


VARIATION 1 (OR) - Execution Time: 1s 609ms :)
----------------------------------------------
JOIN candidateAttribute t7 ON
(
(t7.candidateid=candidate.candidateid AND t7.Attributeid=256) OR
(t7.candidateid=candidate.candidateid AND t7.Attributeid=34) OR
(t7.candidateid=candidate.candidateid AND t7.Attributeid=321) OR
(t7.candidateid=candidate.candidateid AND t7.Attributeid=3563)
)

VARIATION 2 (<>) - Execution Time: 27s 860ms :(
-----------------------------------------------
JOIN candidateAttribute t7 ON
(
(t7.candidateid=candidate.candidateid AND t7.Attributeid<>256) AND
(t7.candidateid=candidate.candidateid AND t7.Attributeid<>34) AND
(t7.candidateid=candidate.candidateid AND t7.Attributeid<>321) AND
(t7.candidateid=candidate.candidateid AND t7.Attributeid<>3563 )
)


VARIATION 3 (OR + <>) - Execution Time: 39s 468ms :(
----------------------------------------------------
JOIN candidateAttribute t7 ON
(
(t7.candidateid=candidate.candidateid AND t7.Attributeid=321) OR
(t7.candidateid=candidate.candidateid AND t7.Attributeid=3563)
)
JOIN candidateAttribute t8 ON
(
(t8.candidateid=candidate.candidateid AND t8.Attributeid<>256) AND
(t8.candidateid=candidate.candidateid AND t8.Attributeid<>34)
)

The plans show all available indexes being used. Maybe a different
strategy is required for the <> test?

Tom