Standards questions - 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 2000 Forums

SQL Server Development (2000)

Standards questions

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2004-05-20 : 23:11:26

Wondering what option in the below example should be used for performace reasons. It looks like Option A performs slightly better but I have seen and used both. Anybody want to comment on why or why not.

set nocount on
create table tblwf ( wfid int primary key)
create table tblwf2 ( wfid int primary key)
go

insert into tblwf
select 1
union
select 2
union
select 3
union
select 5
union
select 6
go

insert into tblwf2
select 1
union
select 2
union
select 3
go

--option a
select a.wfid
from tblwf a
where not exists(select 1 from tblwf2 b where a.wfid = b.wfid)

--option b
select a.wfid
from tblwf a
left join tblwf2 b ON a.wfid = b.wfid
where b.wfid IS NULL

drop table tblwf
drop table tblwf2
go

slow down to move faster...

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-05-21 : 06:19:37

I think option A is going to outrun option B for smaller resultsets in the subquery. When this gets larger, the JOIN is probably going to be faster since the correlated subquery doesn't have to be evaluated for every row. Not sure if this is really the case though, the only way to find out is to test it. Also guess an index on the join columns should benefit both queries equally.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources