Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Dummy statement for avoiding "if not exists"

11 posts in Product Futures Discussion Last posting was on 2003-06-27 15:14:36.0Z
Raymond_Mardle Posted on 2003-05-26 06:54:36.0Z
From: Raymond_Mardle
Date: Mon, 26 May 2003 02:54:36 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Dummy statement for avoiding "if not exists"
Message-ID: <526289C498EBA9900025F54D85256D32.0025F58C85256D32@webforums>
Lines: 54
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1237
Article PK: 95476

Folks,
If my memory serves me correctly, one of the performance improvements the
P&T course recommended was that "if exists" be used along with a dummy
statement to perform if a condition exists followed by the failure
sequence, instead of "if not exists".

For example, instead of

if not exists (select 1 from syslogins where name = "A_Name")
begin
exec sp_addlogin A_Name, "123456"
end

do something like this

declare @i int
if exists (select 1 from syslogins where name = "A_Name")
begin
select @i = @i
end
else
begin
exec sp_addlogin A_Name, "123456"
end

The problem I have is that to perform some dummy statement there either
has to be a variable declared and then an assignment to itself; or if I
know there this is going to be a small batch, just the declaration of a
variable after the first "begin" (e.g. "declare @dummyvar int"). Just
having a "begin" immediately followed by an "end" with nothing between them
throws error 156.

If any of you out there have as many grey hairs as me, you may be
familiar with the FORTRAN 77 "continue" statement (not the same as the
T-SQL "continue" statement). This was a dummy statement that did nothing
except act as a placeholder in the code (for things like the above).

I would like something similar in T-SQL to allow an "end" to immediately
follow a "begin" or to have a dummy statement, something like this

if exists (select 1 from syslogins where name = "A_Name")
begin
DONOTHING
end
else
begin
exec sp_addlogin A_Name, "123456"
end

Cheers

Raymond

PS If engineering are looking for some syntax, they could call it MARDLE.
That way I can finally prove that I do nothing.


Rob Verschoor Posted on 2003-05-26 06:48:35.0Z
Reply-To: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
References: <526289C498EBA9900025F54D85256D32.0025F58C85256D32@webforums>
Subject: Re: Dummy statement for avoiding "if not exists"
Date: Mon, 26 May 2003 08:48:35 +0200
Lines: 76
Organization: Sypron B.V.
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <eamGkO1IDHA.220@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: a66085.upc-a.chello.nl 62.163.66.85
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1238
Article PK: 95477

I've hit this 'inconvenience' various times as well. An improvement
would be welcome.
The simplest way would probably be syntactical, allowing a begin...end
block to contain no statements at all.

Rob V.

<Raymond_Mardle> wrote in message
news:526289C498EBA9900025F54D85256D32.0025F58C85256D32@webforums...
> Folks,
> If my memory serves me correctly, one of the performance
improvements the
> P&T course recommended was that "if exists" be used along with a
dummy
> statement to perform if a condition exists followed by the failure
> sequence, instead of "if not exists".
>
> For example, instead of
>
> if not exists (select 1 from syslogins where name = "A_Name")
> begin
> exec sp_addlogin A_Name, "123456"
> end
>
> do something like this
>
> declare @i int
> if exists (select 1 from syslogins where name = "A_Name")
> begin
> select @i = @i
> end
> else
> begin
> exec sp_addlogin A_Name, "123456"
> end
>
> The problem I have is that to perform some dummy statement there
either
> has to be a variable declared and then an assignment to itself; or
if I
> know there this is going to be a small batch, just the declaration
of a
> variable after the first "begin" (e.g. "declare @dummyvar int").
Just
> having a "begin" immediately followed by an "end" with nothing
between them
> throws error 156.
>
> If any of you out there have as many grey hairs as me, you may be
> familiar with the FORTRAN 77 "continue" statement (not the same as
the
> T-SQL "continue" statement). This was a dummy statement that did
nothing
> except act as a placeholder in the code (for things like the above).
>
> I would like something similar in T-SQL to allow an "end" to
immediately
> follow a "begin" or to have a dummy statement, something like this
>
> if exists (select 1 from syslogins where name = "A_Name")
> begin
> DONOTHING
> end
> else
> begin
> exec sp_addlogin A_Name, "123456"
> end
>
> Cheers
>
> Raymond
>
> PS If engineering are looking for some syntax, they could call it
MARDLE.
> That way I can finally prove that I do nothing.


<Olivier Citeau> Posted on 2003-05-26 21:22:44.0Z
From: <Olivier Citeau>
References: <526289C498EBA9900025F54D85256D32.0025F58C85256D32@webforums> <eamGkO1IDHA.220@forums-1-dub>
Subject: Re: Dummy statement for avoiding "if not exists"
Date: Mon, 26 May 2003 23:22:44 +0200
Lines: 104
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <usxfVL9IDHA.218@forums-2-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: nas-p19-1-62-147-235-247.dial.proxad.net 62.147.235.247
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1239
Article PK: 95480

Hi experts, i am sure that this used to be true.
But are you sure that modern version of ASE cannot optimize "not exists"
statements ?

i have a statement like this :
Select sum(a) mt
into #tmp
from t
where not exists (select 1 from...)

Would you advise me to write :
Select sum(a) mt
into #tmp
from t

Select sum(a) mt
into #tmp2
from t
where exists (select 1 from...)

update #tmp
set mt=#tmp2.mt
from #tmp2

"Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> a écrit dans
le message news: eamGkO1IDHA.220@forums-1-dub...

> I've hit this 'inconvenience' various times as well. An improvement
> would be welcome.
> The simplest way would probably be syntactical, allowing a begin...end
> block to contain no statements at all.
>
> Rob V.
>
> <Raymond_Mardle> wrote in message
> news:526289C498EBA9900025F54D85256D32.0025F58C85256D32@webforums...
> > Folks,
> > If my memory serves me correctly, one of the performance
> improvements the
> > P&T course recommended was that "if exists" be used along with a
> dummy
> > statement to perform if a condition exists followed by the failure
> > sequence, instead of "if not exists".
> >
> > For example, instead of
> >
> > if not exists (select 1 from syslogins where name = "A_Name")
> > begin
> > exec sp_addlogin A_Name, "123456"
> > end
> >
> > do something like this
> >
> > declare @i int
> > if exists (select 1 from syslogins where name = "A_Name")
> > begin
> > select @i = @i
> > end
> > else
> > begin
> > exec sp_addlogin A_Name, "123456"
> > end
> >
> > The problem I have is that to perform some dummy statement there
> either
> > has to be a variable declared and then an assignment to itself; or
> if I
> > know there this is going to be a small batch, just the declaration
> of a
> > variable after the first "begin" (e.g. "declare @dummyvar int").
> Just
> > having a "begin" immediately followed by an "end" with nothing
> between them
> > throws error 156.
> >
> > If any of you out there have as many grey hairs as me, you may be
> > familiar with the FORTRAN 77 "continue" statement (not the same as
> the
> > T-SQL "continue" statement). This was a dummy statement that did
> nothing
> > except act as a placeholder in the code (for things like the above).
> >
> > I would like something similar in T-SQL to allow an "end" to
> immediately
> > follow a "begin" or to have a dummy statement, something like this
> >
> > if exists (select 1 from syslogins where name = "A_Name")
> > begin
> > DONOTHING
> > end
> > else
> > begin
> > exec sp_addlogin A_Name, "123456"
> > end
> >
> > Cheers
> >
> > Raymond
> >
> > PS If engineering are looking for some syntax, they could call it
> MARDLE.
> > That way I can finally prove that I do nothing.
>


Stefan Goebel Posted on 2003-05-27 12:54:17.0Z
From: "Stefan Goebel" <s.goebel@dbap.de>
References: <526289C498EBA9900025F54D85256D32.0025F58C85256D32@webforums> <eamGkO1IDHA.220@forums-1-dub> <usxfVL9IDHA.218@forums-2-dub>
Subject: Re: Dummy statement for avoiding "if not exists"
Date: Tue, 27 May 2003 14:54:17 +0200
Lines: 40
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <#5LKb8EJDHA.275@forums-2-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 62.159.143.62
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1241
Article PK: 95481

Hi Olivier !

I thought the same way, but when reading the P&T guide, I got the point. The
advice to use "exists" instead of "not exists" only matters for table scans
! So this has nothing to do with optimization problems. If you have an
"exists" and must do a table scan, the server can immediately stop when a
record matches - whereas a "not exists" would ALWAYS need a full scan if
actually there is no matching record !

Regards,

Stefan.

<Olivier Citeau> schrieb im Newsbeitrag news:usxfVL9IDHA.218@forums-2-dub...

> Hi experts, i am sure that this used to be true.
> But are you sure that modern version of ASE cannot optimize "not exists"
> statements ?
>
> i have a statement like this :
> Select sum(a) mt
> into #tmp
> from t
> where not exists (select 1 from...)
>
> Would you advise me to write :
> Select sum(a) mt
> into #tmp
> from t
>
> Select sum(a) mt
> into #tmp2
> from t
> where exists (select 1 from...)
>
> update #tmp
> set mt=#tmp2.mt
> from #tmp2

...


Olivier Posted on 2003-05-27 14:32:00.0Z
From: "Olivier" <ociteau-NOCANSPAM@yahoo.fr>
Organization: 212.234.59.105
References: <526289C498EBA9900025F54D85256D32.0025F58C85256D32@webforums> <eamGkO1IDHA.220@forums-1-dub> <usxfVL9IDHA.218@forums-2-dub> <#5LKb8EJDHA.275@forums-2-dub>
X-Newsreader: AspNNTP 1.50 (Tri Hoang)
Subject: Re: Dummy statement for avoiding "if not exists"
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-ID: <uKEJ9yFJDHA.311@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Tue, 27 May 2003 07:32:00 -0700
NNTP-Posting-Host: transinc-win02.netoptex.com 206.169.167.22
Lines: 23
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1242
Article PK: 95483

Hi Stefan,
Danke schöne.

So, there is no use in dummy statements.
if you write :
if exists(..)
dummy statement
else
-- Here, what i wanna do if not exists

It still need a full table scan if there are no matching records.

On Tue, 27 May 2003 14:54:17 +0200,
in sybase.public.ase.product_futures_discussion

Stefan Goebel <s.goebel@dbap.de> wrote:
>Hi Olivier !
>
>I thought the same way, but when reading the P&T guide, I got the point. The
>advice to use "exists" instead of "not exists" only matters for table scans
>! So this has nothing to do with optimization problems. If you have an
>"exists" and must do a table scan, the server can immediately stop when a
>record matches - whereas a "not exists" would ALWAYS need a full scan if
>actually there is no matching record !
>
>Regards,
>
>Stefan.

--
Olivier Citeau from Paris, France
posted via pfcguide.com


Stefan Goebel Posted on 2003-05-27 15:25:15.0Z
From: "Stefan Goebel" <s.goebel@dbap.de>
References: <526289C498EBA9900025F54D85256D32.0025F58C85256D32@webforums> <eamGkO1IDHA.220@forums-1-dub> <usxfVL9IDHA.218@forums-2-dub> <#5LKb8EJDHA.275@forums-2-dub> <uKEJ9yFJDHA.311@forums-1-dub>
Subject: Re: Dummy statement for avoiding "if not exists"
Date: Tue, 27 May 2003 17:25:15 +0200
Lines: 28
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <e1UHGPGJDHA.220@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 62.159.143.62
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1244
Article PK: 95484


> Hi Stefan,
> Danke schöne.

Pas de problème ! *g* (Oh, well, it's a long time ago - the french lessons
at school, you know...)


>
> So, there is no use in dummy statements.
> if you write :
> if exists(..)
> dummy statement
> else
> -- Here, what i wanna do if not exists
>
> It still need a full table scan if there are no matching records.
>
...

Yes. I personally don't see any use for a special dummy statement, because
"declare @JustADummyStatement bit" is a perfect dummy statement and kinda
explains itself...


Stefan.


Sherlock, Kevin Posted on 2003-05-27 14:52:45.0Z
Message-ID: <3ED37BBA.69963A2E@qwest.com.nospam>
Date: Tue, 27 May 2003 09:52:45 -0500
From: "Sherlock, Kevin" <ksherlo@qwest.com.nospam>
Reply-To: ksherlo@qwest.com.nospam
Organization: QWEST DEX
X-Mailer: Mozilla 4.79 (Macintosh; U; PPC)
X-Accept-Language: en,pdf,ko
MIME-Version: 1.0
Subject: Re: Dummy statement for avoiding "if not exists"
References: <526289C498EBA9900025F54D85256D32.0025F58C85256D32@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: np45.qwest.com 155.70.39.45
Lines: 61
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1243
Article PK: 95482

Until such time, you could work around this with "GOTO".

if exists (select 1 from ...)
goto continu
else
begin
exec sp_addlogin ...
end
continu:


BTW - I can't think of a situation where there would be a performance
difference between NOT EXISTS and EXISTS. In the case of an indexed
search, both would "stop" after "finding" a row. Same would be true of
a full table/index scan. I think what you may be referring to in the
documentation is probably out-dated, and not applicable to more recent
versions of ASE (11.0 and up).

Raymond_Mardle wrote:
>
> Folks,
> If my memory serves me correctly, one of the performance improvements the
> P&T course recommended was that "if exists" be used along with a dummy
> statement to perform if a condition exists followed by the failure
> sequence, instead of "if not exists".
>
> For example, instead of
>
> if not exists (select 1 from syslogins where name = "A_Name")
> begin
> exec sp_addlogin A_Name, "123456"
> end
>
> do something like this
>
> declare @i int
> if exists (select 1 from syslogins where name = "A_Name")
> begin
> select @i = @i
> end
> else
> begin
> exec sp_addlogin A_Name, "123456"
> end
>
> The problem I have is that to perform some dummy statement there either
> has to be a variable declared and then an assignment to itself; or if I
> know there this is going to be a small batch, just the declaration of a
> variable after the first "begin" (e.g. "declare @dummyvar int"). Just
> having a "begin" immediately followed by an "end" with nothing between them
> throws error 156.
>
> If any of you out there have as many grey hairs as me, you may be
> familiar with the FORTRAN 77 "continue" statement (not the same as the
> T-SQL "continue" statement). This was a dummy statement that did nothing
> except act as a placeholder in the code (for things like the above).
>
> I would like something similar in T-SQL to allow an "end" to immediately
> follow a "begin" or to have a dummy statement, something like this
>
> if exists (select 1 from syslogins where name = "A_Name")
> begin
> DONOTHING
> end
> else
> begin
> exec sp_addlogin A_Name, "123456"
> end
>
> Cheers
>
> Raymond
>
> PS If engineering are looking for some syntax, they could call it MARDLE.
> That way I can finally prove that I do nothing.


Stefan Goebel Posted on 2003-05-27 15:41:13.0Z
From: "Stefan Goebel" <s.goebel@dbap.de>
References: <526289C498EBA9900025F54D85256D32.0025F58C85256D32@webforums> <3ED37BBA.69963A2E@qwest.com.nospam>
Subject: Re: Dummy statement for avoiding "if not exists"
Date: Tue, 27 May 2003 17:41:13 +0200
Lines: 110
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <eTO8AYGJDHA.221@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 62.159.143.62
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1245
Article PK: 95485

Hi Sherlock,

Though the P&T guide (11.0) says...

"In subqueries and if statements, exists and in perform faster than not
exists and not in when the values in the where clause are not indexed. For
exists and in, SQL Server can return TRUE as soon as a single row matches.
For the negated expressions, it must examine all values to determine that
there are not matches."

... and though I just "convinced" myself and Olivier that this makes sense,
you've made me doubt that again...

Well, if the server evaluates "not exists" in this way (and why shouldn't
that be so ?)...
1. evaluate exists
2. negate the result of 1.
... then obviously it shouldn't make a difference at all !

Hmmm.... ???

Regards,

Stefan.


"Sherlock, Kevin" <ksherlo@qwest.com.nospam> schrieb im Newsbeitrag
news:3ED37BBA.69963A2E@qwest.com.nospam...

> Until such time, you could work around this with "GOTO".
>
> if exists (select 1 from ...)
> goto continu
> else
> begin
> exec sp_addlogin ...
> end
> continu:
>
>
> BTW - I can't think of a situation where there would be a performance
> difference between NOT EXISTS and EXISTS. In the case of an indexed
> search, both would "stop" after "finding" a row. Same would be true of
> a full table/index scan. I think what you may be referring to in the
> documentation is probably out-dated, and not applicable to more recent
> versions of ASE (11.0 and up).
>
> Raymond_Mardle wrote:
> >
> > Folks,
> > If my memory serves me correctly, one of the performance improvements
the
> > P&T course recommended was that "if exists" be used along with a dummy
> > statement to perform if a condition exists followed by the failure
> > sequence, instead of "if not exists".
> >
> > For example, instead of
> >
> > if not exists (select 1 from syslogins where name = "A_Name")
> > begin
> > exec sp_addlogin A_Name, "123456"
> > end
> >
> > do something like this
> >
> > declare @i int
> > if exists (select 1 from syslogins where name = "A_Name")
> > begin
> > select @i = @i
> > end
> > else
> > begin
> > exec sp_addlogin A_Name, "123456"
> > end
> >
> > The problem I have is that to perform some dummy statement there
either
> > has to be a variable declared and then an assignment to itself; or if I
> > know there this is going to be a small batch, just the declaration of a
> > variable after the first "begin" (e.g. "declare @dummyvar int"). Just
> > having a "begin" immediately followed by an "end" with nothing between
them
> > throws error 156.
> >
> > If any of you out there have as many grey hairs as me, you may be
> > familiar with the FORTRAN 77 "continue" statement (not the same as the
> > T-SQL "continue" statement). This was a dummy statement that did nothing
> > except act as a placeholder in the code (for things like the above).
> >
> > I would like something similar in T-SQL to allow an "end" to
immediately
> > follow a "begin" or to have a dummy statement, something like this
> >
> > if exists (select 1 from syslogins where name = "A_Name")
> > begin
> > DONOTHING
> > end
> > else
> > begin
> > exec sp_addlogin A_Name, "123456"
> > end
> >
> > Cheers
> >
> > Raymond
> >
> > PS If engineering are looking for some syntax, they could call it
MARDLE.
> > That way I can finally prove that I do nothing.


Sherlock, Kevin Posted on 2003-05-27 17:57:59.0Z
Message-ID: <3ED3A714.B407E6BD@qwest.com.nospam>
Date: Tue, 27 May 2003 12:57:59 -0500
From: "Sherlock, Kevin" <ksherlo@qwest.com.nospam>
Reply-To: ksherlo@qwest.com.nospam
Organization: QWEST DEX
X-Mailer: Mozilla 4.79 (Macintosh; U; PPC)
X-Accept-Language: en,pdf,ko
MIME-Version: 1.0
Subject: Re: Dummy statement for avoiding "if not exists"
References: <526289C498EBA9900025F54D85256D32.0025F58C85256D32@webforums> <3ED37BBA.69963A2E@qwest.com.nospam> <eTO8AYGJDHA.221@forums-1-dub>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: np45.qwest.com 155.70.39.45
Lines: 137
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1246
Article PK: 95487

A simple test proves it. On my ASE ( 12.0.5.3 ) I have a one Million
row table with an unindexed column (unique1). Another indexed column
(unique2) is a sequential number. I'm choosing to go the 100,000th row
to do my test.

select unique1,unique2
from oneMrows
where unique2 = 100000

unique1 unique2
----------- -----------
78947 100000

set showplan on
print " "
print "EXISTS TEST"
print " "

if exists (select 1 from oneMrows where unique1 = 78947)
print "Exists"
else
print "Not Exists"

print " "
print "NOT EXISTS TEST"
print " "
if not exists (select 1 from oneMrows where unique1 = 78947)
print "Not Exists"
else
print "Exists"

STEP 1

The type of query is SELECT.

Evaluate Ungrouped ANY AGGREGATE.

FROM TABLE
oneMrows
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.

STEP 1
The type of query is SELECT.

Evaluate Ungrouped ANY AGGREGATE.

FROM TABLE
oneMrows
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.


EXISTS TEST

Table: oneMrows scan count 1, logical reads: (regular=4152 apf=0
total=4152), physical reads: (regular=7 apf=4204 total=4211), apf IOs used=4133

Exists

NOT EXISTS TEST

Table: oneMrows scan count 1, logical reads: (regular=4152 apf=0
total=4152), physical reads: (regular=200 apf=725 total=925), apf IOs used=725

Exists

Stefan Goebel wrote:
>
> Hi Sherlock,
>
> Though the P&T guide (11.0) says...
>
> "In subqueries and if statements, exists and in perform faster than not
> exists and not in when the values in the where clause are not indexed. For
> exists and in, SQL Server can return TRUE as soon as a single row matches.
> For the negated expressions, it must examine all values to determine that
> there are not matches."
>
> ... and though I just "convinced" myself and Olivier that this makes sense,
> you've made me doubt that again...
>
> Well, if the server evaluates "not exists" in this way (and why shouldn't
> that be so ?)...
> 1. evaluate exists
> 2. negate the result of 1.
> ... then obviously it shouldn't make a difference at all !
>
> Hmmm.... ???
>
> Regards,
>
> Stefan.
>
> "Sherlock, Kevin" <ksherlo@qwest.com.nospam> schrieb im Newsbeitrag
> news:3ED37BBA.69963A2E@qwest.com.nospam...
> > Until such time, you could work around this with "GOTO".
> >
> > if exists (select 1 from ...)
> > goto continu
> > else
> > begin
> > exec sp_addlogin ...
> > end
> > continu:
> >
> >
> > BTW - I can't think of a situation where there would be a performance
> > difference between NOT EXISTS and EXISTS. In the case of an indexed
> > search, both would "stop" after "finding" a row. Same would be true of
> > a full table/index scan. I think what you may be referring to in the
> > documentation is probably out-dated, and not applicable to more recent
> > versions of ASE (11.0 and up).
> >
> > Raymond_Mardle wrote:
> > >
> > > Folks,
> > > If my memory serves me correctly, one of the performance improvements
> the
> > > P&T course recommended was that "if exists" be used along with a dummy
> > > statement to perform if a condition exists followed by the failure
> > > sequence, instead of "if not exists".
> > >
> > > For example, instead of
> > >
> > > if not exists (select 1 from syslogins where name = "A_Name")
> > > begin
> > > exec sp_addlogin A_Name, "123456"
> > > end
> > >
> > > do something like this
> > >
> > > declare @i int
> > > if exists (select 1 from syslogins where name = "A_Name")
> > > begin
> > > select @i = @i
> > > end
> > > else
> > > begin
> > > exec sp_addlogin A_Name, "123456"
> > > end
> > >
> > > The problem I have is that to perform some dummy statement there
> either
> > > has to be a variable declared and then an assignment to itself; or if I
> > > know there this is going to be a small batch, just the declaration of a
> > > variable after the first "begin" (e.g. "declare @dummyvar int"). Just
> > > having a "begin" immediately followed by an "end" with nothing between
> them
> > > throws error 156.
> > >
> > > If any of you out there have as many grey hairs as me, you may be
> > > familiar with the FORTRAN 77 "continue" statement (not the same as the
> > > T-SQL "continue" statement). This was a dummy statement that did nothing
> > > except act as a placeholder in the code (for things like the above).
> > >
> > > I would like something similar in T-SQL to allow an "end" to
> immediately
> > > follow a "begin" or to have a dummy statement, something like this
> > >
> > > if exists (select 1 from syslogins where name = "A_Name")
> > > begin
> > > DONOTHING
> > > end
> > > else
> > > begin
> > > exec sp_addlogin A_Name, "123456"
> > > end
> > >
> > > Cheers
> > >
> > > Raymond
> > >
> > > PS If engineering are looking for some syntax, they could call it
> MARDLE.
> > > That way I can finally prove that I do nothing.


Stefan Goebel Posted on 2003-05-28 08:13:03.0Z
From: "Stefan Goebel" <s.goebel@dbap.de>
References: <526289C498EBA9900025F54D85256D32.0025F58C85256D32@webforums> <3ED37BBA.69963A2E@qwest.com.nospam> <eTO8AYGJDHA.221@forums-1-dub> <3ED3A714.B407E6BD@qwest.com.nospam>
Subject: Re: Dummy statement for avoiding "if not exists"
Date: Wed, 28 May 2003 10:13:03 +0200
Lines: 214
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <ee1VRCPJDHA.220@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 62.159.143.62
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1247
Article PK: 95486

Convincing...

BTW, why did I say "Hi Sherlock" ? Maybe I should get more sleep and stop
working to long...

Thanks, Kevin !

Stefan.


"Sherlock, Kevin" <ksherlo@qwest.com.nospam> schrieb im Newsbeitrag
news:3ED3A714.B407E6BD@qwest.com.nospam...

> A simple test proves it. On my ASE ( 12.0.5.3 ) I have a one Million
> row table with an unindexed column (unique1). Another indexed column
> (unique2) is a sequential number. I'm choosing to go the 100,000th row
> to do my test.
>
> select unique1,unique2
> from oneMrows
> where unique2 = 100000
>
> unique1 unique2
> ----------- -----------
> 78947 100000
>
> set showplan on
> print " "
> print "EXISTS TEST"
> print " "
>
> if exists (select 1 from oneMrows where unique1 = 78947)
> print "Exists"
> else
> print "Not Exists"
>
> print " "
> print "NOT EXISTS TEST"
> print " "
> if not exists (select 1 from oneMrows where unique1 = 78947)
> print "Not Exists"
> else
> print "Exists"
>
> STEP 1
>
> The type of query is SELECT.
>
> Evaluate Ungrouped ANY AGGREGATE.
>
> FROM TABLE
> oneMrows
> EXISTS TABLE : nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 2 Kbytes for data pages.
> With MRU Buffer Replacement Strategy for data pages.
>
> STEP 1
> The type of query is SELECT.
>
> Evaluate Ungrouped ANY AGGREGATE.
>
> FROM TABLE
> oneMrows
> EXISTS TABLE : nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 2 Kbytes for data pages.
> With MRU Buffer Replacement Strategy for data pages.
>
>
> EXISTS TEST
>
> Table: oneMrows scan count 1, logical reads: (regular=4152 apf=0
> total=4152), physical reads: (regular=7 apf=4204 total=4211), apf IOs
used=4133
>
> Exists
>
> NOT EXISTS TEST
>
> Table: oneMrows scan count 1, logical reads: (regular=4152 apf=0
> total=4152), physical reads: (regular=200 apf=725 total=925), apf IOs
used=725
>
> Exists
>
> Stefan Goebel wrote:
> >
> > Hi Sherlock,
> >
> > Though the P&T guide (11.0) says...
> >
> > "In subqueries and if statements, exists and in perform faster than not
> > exists and not in when the values in the where clause are not indexed.
For
> > exists and in, SQL Server can return TRUE as soon as a single row
matches.
> > For the negated expressions, it must examine all values to determine
that
> > there are not matches."
> >
> > ... and though I just "convinced" myself and Olivier that this makes
sense,
> > you've made me doubt that again...
> >
> > Well, if the server evaluates "not exists" in this way (and why
shouldn't
> > that be so ?)...
> > 1. evaluate exists
> > 2. negate the result of 1.
> > ... then obviously it shouldn't make a difference at all !
> >
> > Hmmm.... ???
> >
> > Regards,
> >
> > Stefan.
> >
> > "Sherlock, Kevin" <ksherlo@qwest.com.nospam> schrieb im Newsbeitrag
> > news:3ED37BBA.69963A2E@qwest.com.nospam...
> > > Until such time, you could work around this with "GOTO".
> > >
> > > if exists (select 1 from ...)
> > > goto continu
> > > else
> > > begin
> > > exec sp_addlogin ...
> > > end
> > > continu:
> > >
> > >
> > > BTW - I can't think of a situation where there would be a performance
> > > difference between NOT EXISTS and EXISTS. In the case of an indexed
> > > search, both would "stop" after "finding" a row. Same would be true
of
> > > a full table/index scan. I think what you may be referring to in the
> > > documentation is probably out-dated, and not applicable to more recent
> > > versions of ASE (11.0 and up).
> > >
> > > Raymond_Mardle wrote:
> > > >
> > > > Folks,
> > > > If my memory serves me correctly, one of the performance
improvements
> > the
> > > > P&T course recommended was that "if exists" be used along with a
dummy
> > > > statement to perform if a condition exists followed by the failure
> > > > sequence, instead of "if not exists".
> > > >
> > > > For example, instead of
> > > >
> > > > if not exists (select 1 from syslogins where name = "A_Name")
> > > > begin
> > > > exec sp_addlogin A_Name, "123456"
> > > > end
> > > >
> > > > do something like this
> > > >
> > > > declare @i int
> > > > if exists (select 1 from syslogins where name = "A_Name")
> > > > begin
> > > > select @i = @i
> > > > end
> > > > else
> > > > begin
> > > > exec sp_addlogin A_Name, "123456"
> > > > end
> > > >
> > > > The problem I have is that to perform some dummy statement there
> > either
> > > > has to be a variable declared and then an assignment to itself; or
if I
> > > > know there this is going to be a small batch, just the declaration
of a
> > > > variable after the first "begin" (e.g. "declare @dummyvar int").
Just
> > > > having a "begin" immediately followed by an "end" with nothing
between
> > them
> > > > throws error 156.
> > > >
> > > > If any of you out there have as many grey hairs as me, you may be
> > > > familiar with the FORTRAN 77 "continue" statement (not the same as
the
> > > > T-SQL "continue" statement). This was a dummy statement that did
nothing
> > > > except act as a placeholder in the code (for things like the above).
> > > >
> > > > I would like something similar in T-SQL to allow an "end" to
> > immediately
> > > > follow a "begin" or to have a dummy statement, something like this
> > > >
> > > > if exists (select 1 from syslogins where name = "A_Name")
> > > > begin
> > > > DONOTHING
> > > > end
> > > > else
> > > > begin
> > > > exec sp_addlogin A_Name, "123456"
> > > > end
> > > >
> > > > Cheers
> > > >
> > > > Raymond
> > > >
> > > > PS If engineering are looking for some syntax, they could call it
> > MARDLE.
> > > > That way I can finally prove that I do nothing.


"Ilya Zvyagin" <ziv{ Posted on 2003-06-27 15:14:36.0Z
Reply-To: "Ilya Zvyagin" <ziv{@}fct{.}ru>
From: "Ilya Zvyagin" <ziv{@}fct{.}ru>
References: <526289C498EBA9900025F54D85256D32.0025F58C85256D32@webforums>
Subject: Re: Dummy statement for avoiding "if not exists"
Date: Fri, 27 Jun 2003 19:14:36 +0400
Lines: 19
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
FL-Build: Fidolook Express 2001 UIExt. BuildID: 3BC00FAD (7/10/2001 12:17:49).
X-Comment-To: Raymond_Mardle
Organization: FCT Ltd
Message-ID: <1056726856.905244@gatekeeper.fct.ru>
Cache-Post-Path: gatekeeper.fct.ru!unknown@dream.int.fct.ru
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: gatekeeper.fct.ru 212.113.103.2
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1267
Article PK: 95507

Hello, Raymond_Mardle!
You wrote on Mon, 26 May 2003 02:54:36 -0400:

RM> Folks,
RM> If my memory serves me correctly, one of the performance
RM> improvements the
RM> P&T course recommended was that "if exists" be used along with a
RM> dummy statement to perform if a condition exists followed by the
RM> failure sequence, instead of "if not exists".

No, exists and not exists should be optimized the same way.
So, you don't have to worry about it.
This doesn't diminish the need for an empty statement indeed.
------------------------------------
Ilya Zvyagin
E-mail: ziv[AT]fct[dot]ru
ICQ# 29427861