Help needed with optimization of JOIN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help needed with optimization of JOIN

Author

Topic

xdk1x
Starting Member

9 Posts

Posted - 2004-06-14 : 04:35:06

Hi!

I have got to tables with both about 500000 rows. I now want to insert into table a all rows of table b which are not yet in table a. I check this within the WHERE-clause:

INSERT INTO Tabelle1 ( PK_1, wert_1)
SELECT Tabelle2.PK_2, Tabelle2.wert__2
FROM Tabelle2
WHERE (((Tabelle2.PK_2) Not In (SELECT Tabelle2.PK_2 FROM Tabelle1 INNER JOIN Tabelle2 ON Tabelle1.PK_1 = Tabelle2.PK_2)));

This works but needs a lot of time since 25 billion (500000*500000) datasets must be compared. Is there any possibility to accerlate this?

Thanks for your help!

Daniel

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-06-14 : 04:50:30

A NOT EXISTS using a correlated subquery should be faster than using NOT IN...

INSERT INTO Tabelle1 ( PK_1, wert_1)
SELECT Tabelle2.PK_2, Tabelle2.wert__2
FROM Tabelle2
WHERE NOT EXISTS (SELECT PK_1 FROM Tabelle1 WHERE PK_1=Tabelle2.PK_2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources