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.
| Author |
Topic |
|
chandru@zsl
Starting Member
8 Posts |
 Posted - 2008-07-28 : 06:44:39
|
| Hi All,I have just come across the except tool in SQL 2005 and have been running some performance checks to see which is better in the following situation:SELECT id FROM tableAEXCEPT SELECT ClientId FROM tableBSELECT id FROM tableA a LEFT JOIN tableB b ON b.ClientID = a.idWHERE b.id is nullWhen looking at the execution plan the EXCEPT cost only 6% where the join costs 94%However the time in ms of the EXCEPT is much slower and the LOGICAL reads is much higher.This leaves me confused :-(Could someone let me know or point me in the correct direction of which is better to use and why? Thanks. |
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-07-28 : 08:38:12
|
| This may help a little: http://www.mcse.ms/message1787813.htmlI suspect the except is like a subquery.Another apporach to throw into the mix is the "not exists". I'd be interested to hear metrics on it. |
 |
|
|
pootle_flump
1064 Posts |
Posted - 2008-07-28 : 09:19:38
|
| Yeah - I prefer to use EXISTS. I know that resolves to an outer-semi-join which is darned efficient. I think, however, the left outer resolves to that too - I can't remember though....Have a look at the estimated returns of the two queries - the optimiser might be under\ over estimating for one of the queries.Have a look here (start from the bottom not the top!) too:http://blogs.msdn.com/craigfr/archive/tags/Joins/default.aspx |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
|
|
|
Left join Vs Except