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