Hi Gail

Unfortunately the query plan is very big to post, but here is what looks like the query:

Also please note that the query plan when I ran the query in Management Studio is different from the one captured in SQL Profiler

SELECT

o.CMID,

p0.FORMAT,

p1.REFCMID

FROM dbo.CRMOBJECTS o

LEFT OUTER JOIN dbo.CRMOBJPROPS p0 ON o.CMID=p0.CMID

LEFT OUTER JOIN dbo.CRMREFNOORD p1 ON o.CMID=p1.CMID

LEFT OUTER JOIN dbo.CRMOBJPROPS p2 ON o.CMID=p2.CMID

LEFT OUTER JOIN dbo.CRMLOCALES p5 ON p5.LOCALEID=p0.OLOCALEID,

#CRMTMP t

WHERE (((

NOT (p2.RECIPSEMAIL IS NOT NULL)

AND NOT (exists

(SELECT *

FROM dbo.CRMREF1 p3

LEFT OUTER JOIN dbo.CRMOBJECTS d0 ON p3.REFCMID=d0.CMID

WHERE o.CMID=p3.CMID

AND (p3.PROPID=26)

AND (d0.CLASSID!=140)

))

)

OR exists

(SELECT *

FROM dbo.CRMREF1 p4

LEFT OUTER JOIN dbo.CRMOBJECTS d0 ON p4.REFCMID=d0.CMID

WHERE o.CMID=p4.CMID

AND (p4.PROPID=26

OR p4.PROPID=25)

AND (d0.CLASSID!=140)

AND p4.REFCMID IN (343831,89,92,1782,1786,1813,1817,5653,6322,7327,7869,88,478))))

AND o.CMID=t.CMID

AND t.QUERYID=69

Temp table will contain about 500 rows and the query will return 1 rows