EXISTS Vs. NOT EXISTS

Is there a performace difference in the following queries, assuming tblAgent
contains 100,000 records?
IF EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
BEGIN
...do operation A
END
ELSE
BEGIN
...do operation B
END
________________________________________
____________________________
IF NOT EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
BEGIN
...do operation B
END
ELSE
BEGIN
...do operation A
ENDIt depends (among 400 other things) on how likely you are to find a row
where AgentID = @.SomeAgentID.
"Justin" <jyang@.ioutsource.info> wrote in message
news:OKCem3LZGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Is there a performace difference in the following queries, assuming
> tblAgent contains 100,000 records?
> IF EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
> BEGIN
> ....do operation A
> END
> ELSE
> BEGIN
> ....do operation B
> END
> ________________________________________
____________________________
>
> IF NOT EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
> BEGIN
> ....do operation B
> END
> ELSE
> BEGIN
> ....do operation A
> END
>|||EXISTS() and NOT EXISTS() require exactly the same processing to come
up with an answer, assuming the same expression in the parentheses.
The answer to either gives the answer to both - they are the same.
This means that it makes no difference if you test for one or the
other, as long as the THEN/ELSE logic is also adjusted accordingly.
Which appears to be what your example does.
Roy Harvey
Beacon Falls, CT
On Thu, 20 Apr 2006 17:03:37 -0400, "Justin" <jyang@.ioutsource.info>
wrote:

>Is there a performace difference in the following queries, assuming tblAgen
t
>contains 100,000 records?
>IF EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
>BEGIN
> ....do operation A
>END
>ELSE
>BEGIN
> ....do operation B
>END
> ________________________________________
____________________________
>
>IF NOT EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
>BEGIN
> ....do operation B
>END
>ELSE
>BEGIN
> ....do operation A
>END
>|||I think "exists" ususally is better, since it will stop searching once the
1st row is found, but the "not exists" perhaps (depending on the index) will
search all rows.
"Justin" wrote:

> Is there a performace difference in the following queries, assuming tblAge
nt
> contains 100,000 records?
> IF EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
> BEGIN
> ....do operation A
> END
> ELSE
> BEGIN
> ....do operation B
> END
> ________________________________________
____________________________
>
> IF NOT EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
> BEGIN
> ....do operation B
> END
> ELSE
> BEGIN
> ....do operation A
> END
>
>|||You're right. I was a bit distracted to another topic just now.
"Roy Harvey" wrote:

> EXISTS() and NOT EXISTS() require exactly the same processing to come
> up with an answer, assuming the same expression in the parentheses.
> The answer to either gives the answer to both - they are the same.
> This means that it makes no difference if you test for one or the
> other, as long as the THEN/ELSE logic is also adjusted accordingly.
> Which appears to be what your example does.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 20 Apr 2006 17:03:37 -0400, "Justin" <jyang@.ioutsource.info>
> wrote:
>
>|||Justin (jyang@.ioutsource.info) writes:
> Is there a performace difference in the following queries, assuming
> tblAgent contains 100,000 records?
> IF EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
>...
> IF NOT EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
They should perform the same. At least that is my expectation.
What matters a lot more is whether AgentID is indexed or not.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||James Ma wrote:
> I think "exists" ususally is better, since it will stop searching once the
> 1st row is found, but the "not exists" perhaps (depending on the index) wi
ll
> search all rows.
I would expect "not exists" stop once first row is found either.
What is the point to continue if the result is already known?|||I was wrong in this case.
I was thinking some real-life scenarios like: if you want to prove a cattle
flock has BSE, you can stop testing once you find 1st cattle with BSE; but i
f
you want to prove the flock doesn’t has BSE, you need to test each cattle
of
the flock. Recently I have been studying statistics and somehow was
distracted to there. Sorry for the misleading post.
Thanks,
James
"Sericinus hunter" wrote:

> James Ma wrote:
> I would expect "not exists" stop once first row is found either.
> What is the point to continue if the result is already known?
>