better alternative for NOT IN - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

better alternative for NOT IN

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-02 : 13:05:58

Hi there

I am trying to insert millions of records from staging table to destination table but only those records that do not exist in destination. NOT IN is a crawl

Thank you

If you don't have the passion to help people, you have no passion

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-02 : 13:19:31

Try Not Exists. If the columns are nullable not in is going to crawl.

http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

--
Gail Shaw
SQL Server MVP

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-02 : 13:25:00

wow wow wow awesome, thanks very much!

If you don't have the passion to help people, you have no passion

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-02 : 14:29:13

Maybe a left join?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-02 : 15:44:59

I looked into that and it seems like with a left join you will still haev to do a IS NULL which is a filter that will slow it down some

If you don't have the passion to help people, you have no passion

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-02 : 16:09:31

http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/

--
Gail Shaw
SQL Server MVP

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-02 : 16:11:16

You should do a perf test. NOT EXISTS will sometimes be faster. Since your dataset is realtivly small, it'll probably work just fine. I find that most of the time LEFT OUTER JOINs start to perform faster on larger tables. But, YMMV.

Besides performace, it's more of a coding style thing. I much prefer join syntax over that of the (not) exits syntax.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-02 : 16:15:53

quote:
Originally posted by GilaMonster

http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/

--
Gail Shaw
SQL Server MVP

Yeah, that's why I suggested a perf test. The article is interesting, but the real world may very well show different results. In my dealings with larger tables (100M+) the left join has almost always outperformed a not exists. There are a lot of variables at play.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-02 : 17:20:09

thanks you all. I am testing as we speak. NOT EXISTS is working very well for me. I guess it depends for each scenario (index, no index, NULL not NULL) etc. appreciate the input

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources