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