>>>>Hi
>>>>I have a view (MyView) which is
>>>>
>>>>
SELECT * FROM DB1.dbo.tableA
>>>>UNION
>>>>SELECT * FROM DB2.dbo.TableB
>>>>
>>>>
>>>>I have SQL statement
>>>>
>>>>
>>>>SELECT myView.* FROM MyView
>>>>INNER JOIN DB2.dbo.TableZ ON myView.Key = TableZ.Key
>>>>
>>>>
>>>>It is working fine, returned result in 2 secs
>>>>
>>>>However, if I amend it as below, it will take more than a minute to execute.
>>>>
>>>>
>>>>SELECT myView.*, TableZ.FieldA FROM MyView
>>>>INNER JOIN DB2.dbo.TableZ ON myView.Key = TableZ.Key
>>>>
>>>>
>>>>After some trial and error, found that, if I remark the edit my view without UNION (select only from TableB), the performance is 2 secs as well. Why?
>>>>
>>>>Beside, I tried to add an index which contains Key + FieldA, but not helping at all.
>>>>
>>>>Please advice, why remove the UNION can be helpful? How to solve this matter with "right" way?
>>>>
>>>>Thank you
>>>
>>>If it's performance you're after you should definitely look at EXISTS.
>>
>>
>>They are not the same, Mike. JOIN and EXISTS are two different animals
>
>Two different means to the same end. My point stands.
Nah, it doesn't. JOIN may return more results than EXISTS
Gregory