>>>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.