Query tuning - 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

General SQL Server Forums

New to SQL Server Programming

Query tuning

Author

Topic

gkavithamca
Starting Member

1 Post

Posted - 2010-09-28 : 02:02:49

how to reduce the time taken while we use is null in left outer join for millions of record.

eg.

select a.txnno from a
left outer join b
on a.txnno=b.txnno
where b.txnno is null

for the above query it return 191

if i use below query it fast

select a.txnno from a
left outer join b
on a.txnno=b.txnno
and b.txnno is null

but it return 192

but 191 is the correct one

any one solve this problem

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-28 : 04:49:53

Change to NOT EXISTS? Add indexes?
http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/

Those two queries aren't equivalent, so you can't replace one with the other. One's checking for rows that don't match, one's checking for rows that do match where txnno is null.

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-30 : 12:29:33

see

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

to understand difference

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources