I have create a view which I use to get duplicates and near matches between
two tables. Getting the near matches means a where clause with lots of OR
clauses to get the various permutatiuons which are classed as a near match.
So my view is like this - there are lots or variations I have only included
a brief version. This is painfully slow with only 1000 records once you get
more than a certain amount of OR clause. The performance speeds up
dramatically when I replace my OR's with UNIONS but the minute I add a where
clause to the view it grinds to a halt. I feel there is a big gap in my
knowledge in terms of how to achieve this. Can anyone point me in the right
direction.

SELECT     *
FROM         dbo.LT_CUSTOMERS LT
WHERE
(NOT EXISTS
(
SELECT CID.FIRSTNAME, CID.SURNAME, CD.POSTCODE, CID.DOB
FROM  [NEXTSTEP WOE].DBO.CUSTOMERID CID
 INNER JOIN [DB2].DBO.CUSTOMERDETAILS CD ON CID.CUSTOMERID = CD.CUSTOMERID
WHERE (CID.SURNAME = LT.SURNAME AND CID.FIRSTNAME = LT.FORENAMES AND
CID.DOB = LT.DOB AND CD.POSTCODE = LT.POSTCODE)
OR
(CID.SURNAME = LT.SURNAME AND CID.FIRSTNAME = LT.FORENAMES AND CID.DOB =
LT.DOB)

I

SELECT     *
FROM         dbo.LT_CUSTOMERS LT
WHERE
(NOT EXISTS
(
SELECT CID.FIRSTNAME, CID.SURNAME, CD.POSTCODE, CID.DOB
FROM  [DB2].DBO.CUSTOMERID CID
 INNER JOIN [DB2].DBO.CUSTOMERDETAILS CD ON CID.CUSTOMERID = CD.CUSTOMERID
WHERE (CID.SURNAME = LT.SURNAME AND CID.FIRSTNAME = LT.FORENAMES AND
CID.DOB = LT.DOB AND CD.POSTCODE = LT.POSTCODE)

UNION
(SELECT CID.FIRSTNAME, CID.SURNAME, CD.POSTCODE, CID.DOB
FROM  [DB2].DBO.CUSTOMERID CID
 INNER JOIN [DB2].DBO.CUSTOMERDETAILS CD ON CID.CUSTOMERID = CD.CUSTOMERID
WHERE(CID.SURNAME = LT.SURNAME AND CID.FIRSTNAME = LT.FORENAMES AND CID.DOB
= LT.DOB)