left outer joins

[seekwall]

I need to update TABLEA with a value where the uin is in TABLEB OR TABLEC OR TABLED. I have used left outer joins to do this.

This query is taking a huge amount of time to run, even though the correct indexes are present.

Is there a simple way around this problem without having to use legacy joins ?

update A
set A.column1 = '1'
from TABLEA A
left outer join TABLEB B on A.uin = B.uin
left outer join TABLEC C on A.uin = C.uin
left outer join TABLED D on A.uin = D.uin
where ( B.uin is not null
or C.uin is not null
or D.uin is not null ) )

ta

Paul

 

[tlbroadbent]

You might want to try WHERE EXISTS and correlated sub-queries. I don't know if it will be any faster but should be because SQL Server will stop searching when a true condition is found.

Update TABLEA
Set column1 = '1'
Where Exists
(Select * From TABLEB Where uin = TableA.uin)
Or Exists
(Select * From TABLEC Where uin = TableA.uin)
Or Exists
(Select * From TABLED Where uin = TableA.uin)
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[seekwall]

Cheers Terry,

I wanted to try to keep away from sub queries. But I don't think I have much of an option really

Paul

 

[tlbroadbent]

May I ask why you want to avoid sub-queries? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[seekwall]

Force of habit I suppose.

I just like using ANSI joins

 

[tlbroadbent]

I prefer ANSI JOINs to old style JOINs. However, sub-queries are powerful tools in SQL programming. Correlated sub-queries are particularly valuable beause of their efficiency. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[seekwall]

Correlated Sub-queries are something I don't use very often to be honest. Thanks for the advice, I will read up more and do some execution plan comparisons or something.

By the way, that query works very well indeed

Thanks again

Paul