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 |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2002-08-10 : 23:22:42
|
| Hiya,This is a performance question for curiousity's sake: How do Exists, ANY, and IN compare in performance, and is there any reason to prefer one over the other? We are working on the assumption that the data is the same for all three; and we are trying a comparison against e.g. 30 values.Sarah Berger MCSD |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-08-11 : 01:36:27
|
| HiOver 30 values it probably won't make the slightest bit of difference. But, as far as the theory goes, Exists will be faster than IN. This is because, with an IN, the whole subquery will be evaluated and tested. With EXISTS, as soon as a "TRUE" result is obtained, the processor jumps out of that step.I am not sure about "ANY", but I suspect it works like "IN".Once again, I say theory, because in reality the query optimizer has a few tricks up it's sleeve and is pretty smart.My 2c, I am sure someone else has some better info.Damian |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-08-12 : 07:26:30
|
| select tbl.*from tblwhere fld in (select fld from tbl2)select tbl.*from tblwhere exists (select * from tbl2 where tbl1.fld = tbl2.fld)select distinct tbl.*from tbl, tbl2where tbl.fld = tbl2.fldFrom testing in the past with v7The first can possibly be slighlty faster than the othersbut ends up a lot slower if it is used as an update statement and hence is to be avoided.For your situation don't worry about performance and go for whatever makes the code easier to understand.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
EXISTS vs. ANY vs. IN