April 7, 2011 at 3:04 pm
is there an alternate method to perform better for this code..
ALTER VIEW [dbo].[empcheck]
AS
(
SELECT Col1,Col1...Col3
FROM empdet_OCC WHERE empscope IN ('US','UK')
UNION ALL
SELECT Col1,Col1...Col3
from empdet_OTC where empscope IN ('US', 'UK')
)as P left join emplookup.dbo.OBVR_emprangeno AG
ON P.emprangeno=AG.emprangeno where AG.OBVR_never in ('345','765') and P.emprangeno not in (select emprangeno from dbo.OBVR_emprangeno )
April 7, 2011 at 3:40 pm
Tara, it looks like you are missing a few things in the code. It is hard to tell if there is any way to optimize this without seeing all of the code.
For example, it looks like you have a derived query being used for the UNION ALL - but I do not see the alias, nor do I see where you have defined the alias 'P' - and the where clause is truncated.
Just guessing, but you could probably push the where clause up to each part of the union - but not sure if that is going to help, since this is a view. It would depend on how the view is actually used.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 7, 2011 at 7:38 pm
you are correct the code didnt work for me but i just wanted to through out the logic that union of 2 tables should have left join with 3rd table and the where clause part should also be included.
April 8, 2011 at 4:13 am
can some one help me with the code, i want make it work 1st then i can look ofr better performance.
April 8, 2011 at 4:29 am
Actually i have a question here.
1. can i do union all of 2 tables and left join to 3rd table in the same query?
2. When i am doing a left join on column "emprangeno", can i also do a "not in" caluse for the same column to the joined table?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply