January 21, 2009 at 7:41 am
I have two tables tempa and tempb both containing same type of informations with common columns say ab, ac, ad, ae and af. tempa contains around 40k rows and tempb contains 250k rows. There are few rows in tempa which are not in tempb. I need to insert those rows into other table. There are no indexes or primary key in any of the table. I've wrote the query like thi:
insert into #temp
select * from tempa nolock
where ab+ac+ad+ae+ai not in(select ab+ac+ad+ae+ai from tempb nolock)
Its taking too much time(more than 30 mins). DBCC SHOWCONTIG shows healthy scan density for both the tables (approx 95%).
Please let me know if i can write the query in better way.
January 21, 2009 at 7:52 am
use not exist instead of in
January 21, 2009 at 8:00 am
I agree. Not exists is usually a better option.
However depending on your version (even service pack or hotfix revision) of SQL, an exists still might scan all of the rows instead of just returning a single row for existence. Later versions of SQL have fixed this functionality.
To ensure, however, that you never have to deal with that and that the test for existence will cease to explore more data once an existing row is found, you can simply add a (top 1) in the correlated sub query.
Here is an example.
insert into #temp
select *
from tempa as A with (nolock)
where not exists(
select top 1 1
from tempb as B with (nolock)
where A.ab = B.ab
and A.ac = B.ac
and A.ad = B.ad
and A.ae = B.ae
and A.ai = B.ai
)
Hope that helps.
January 22, 2009 at 2:36 am
Thanks a lot. It worked within seconds. And now I also got the logic.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply