whihc query should be fast [with in or left 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 2005 Forums

Transact-SQL (2005)

whihc query should be fast [with in or left join]

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-11-25 : 08:59:55

my table structure is

tbluser
id | name
1 | n1
2 | n2
3 | n3
4 | n4

import_user
1 | n1
4 | n4
5 | n5

and my query

insert into tbluser
select id,name
from import_users u
where not exists (
select *
from tbluser t
where t.id = u.id
)

insert into tbluser
select i.id, i.name
from import_user i left outer join tbluser u
on i.id = u.id
where u.id is null

My analysis is the query without left join is better.
Please give your commnets
Thans

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-25 : 09:28:45

Did you compare the execution plan for both?

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-11-25 : 09:37:16

yep but very minor difference i am getting

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 11:25:09

what are indexes present in your tables?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-25 : 11:28:14

WHERE NOT EXISTS
can be faster because the select is always stopped when an existing id is matched.
LEFT OUTER JOIN
selects always all entries from the right table...

In case of not so many rows in your tables the difference may be minor i.e SQL Server can read table data in one block (block=64 kb) and handels it in memory.

In case of large tables you will see a greater difference...

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-11-25 : 12:51:03

Yes, for a single record insert such as yours, WHERE NOT EXISTS on an indexed column is probably faster.
When dealing with multi-record transactions, I prefer LEFT OUTER JOIN.

If it is not practically useful, then it is practically useless.

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-11-25 : 15:04:54

Thanks Webfred

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-25 : 15:25:33

Welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources