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.

not in vs. not ( ... in )

6 posts in Performance and Tuning Last posting was on 2008-07-01 18:42:56.0Z
sybfan Posted on 2008-06-24 19:59:57.0Z
Sender: 35c1.48614a76.1804289383@sybase.com
From: sybfan
Newsgroups: sybase.public.ase.performance+tuning
Subject: not in vs. not ( ... in )
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4861523d.3754.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Jun 2008 12:59:57 -0700
X-Trace: forums-1-dub 1214337597 10.22.241.41 (24 Jun 2008 12:59:57 -0700)
X-Original-Trace: 24 Jun 2008 12:59:57 -0700, 10.22.241.41
Lines: 13
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10972
Article PK: 89583

For the following query:

select ...
from tba, tbb, ...
where ...
and tba.code not in ( "AA", "BB", ... )
...

Would this query perform better(in general), if the the not
in
cond. is re-written as:

and not ( tba.code in ( "AA", "BB", ... ) )


Mark A. Parsons Posted on 2008-06-26 16:47:51.0Z
Sender: 2ec4.4861260e.1804289383@sybase.com
From: Mark A. Parsons
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: not in vs. not ( ... in )
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4863c837.d83.1681692777@sybase.com>
References: <4861523d.3754.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 26 Jun 2008 09:47:51 -0700
X-Trace: forums-1-dub 1214498871 10.22.241.41 (26 Jun 2008 09:47:51 -0700)
X-Original-Trace: 26 Jun 2008 09:47:51 -0700, 10.22.241.41
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10981
Article PK: 89592

When in doubt, try it out.

Try running the following for both queries:

set showplan on
set statistics io on
go
<run_query>
go

Then compare the results. Does one version of the query
provide better performance?

> For the following query:
>
> select ...
> from tba, tbb, ...
> where ...
> and tba.code not in ( "AA", "BB", ... )
> ...
>
> Would this query perform better(in general), if the the
> not in
> cond. is re-written as:
>
> and not ( tba.code in ( "AA", "BB", ... ) )


sybfan Posted on 2008-06-26 18:34:47.0Z
Sender: 660e.4862a2fd.1804289383@sybase.com
From: sybfan
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: not in vs. not ( ... in )
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4863e147.13a0.1681692777@sybase.com>
References: <4863c837.d83.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 26 Jun 2008 11:34:47 -0700
X-Trace: forums-1-dub 1214505287 10.22.241.41 (26 Jun 2008 11:34:47 -0700)
X-Original-Trace: 26 Jun 2008 11:34:47 -0700, 10.22.241.41
Lines: 47
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10982
Article PK: 89593

Mark,

Hi. I don't have a sufficient set of good test data to
experiment with this yet. I'm just afraid if I make up some
test data that's simple and small, I won't be able to really
tell which is better.

I raised this question because I was going through some old
notes I had taken in a previous sybase class. One of the
things I wrote down was that the optimizer will do better
with in ( ... ) as opposed to not in ( ... ). That's why I
asked if I were to re-write my not ins as not ( in ( ) ),
would the optimizer do better, in general ?

Thanks for your help in advance on this question.


sybfan

> When in doubt, try it out.
>
> Try running the following for both queries:
>
> set showplan on
> set statistics io on
> go
> <run_query>
> go
>
> Then compare the results. Does one version of the query
> provide better performance?
>
>
>
> > For the following query:
> >
> > select ...
> > from tba, tbb, ...
> > where ...
> > and tba.code not in ( "AA", "BB", ... )
> > ...
> >
> > Would this query perform better(in general), if the the
> > not in
> > cond. is re-written as:
> >
> > and not ( tba.code in ( "AA", "BB", ... ) )


ThanksButNo Posted on 2008-06-30 19:12:53.0Z
From: ThanksButNo <no.no.thanks@gmail.com>
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: not in vs. not ( ... in )
Date: Mon, 30 Jun 2008 12:12:53 -0700 (PDT)
Organization: http://groups.google.com
Lines: 10
Message-ID: <4bcab879-9c49-490e-930f-f4a299a8274b@d77g2000hsb.googlegroups.com>
References: <4863c837.d83.1681692777@sybase.com> <4863e147.13a0.1681692777@sybase.com>
NNTP-Posting-Host: 71.165.35.183
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1214853174 21734 127.0.0.1 (30 Jun 2008 19:12:54 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 30 Jun 2008 19:12:54 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: d77g2000hsb.googlegroups.com; posting-host=71.165.35.183; posting-account=wjKAPwoAAABtEbTff5o9OO7GYdigbDts
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.12) Gecko/20080201 Firefox/2.0.0.12,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!nlpi057.nbdc.sbc.com!prodigy.net!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!d77g2000hsb.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10983
Article PK: 89594


On Jun 26, 11:34 am, sybfan wrote:
> Mark,
>
> Hi. I don't have a sufficient set of good test data to
> experiment with this yet. I'm just afraid if I make up some
> test data that's simple and small, I won't be able to really
> tell which is better.
>

Make up some test data that's simple and large.


Sherlock, Kevin Posted on 2008-06-30 23:07:51.0Z
From: "Sherlock, Kevin" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <4861523d.3754.1681692777@sybase.com>
Subject: Re: not in vs. not ( ... in )
Lines: 18
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48696747$1@forums-1-dub>
Date: 30 Jun 2008 16:07:51 -0700
X-Trace: forums-1-dub 1214867271 10.22.241.152 (30 Jun 2008 16:07:51 -0700)
X-Original-Trace: 30 Jun 2008 16:07:51 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10984
Article PK: 89596

No difference. They both normalize the same way.

<sybfan> wrote in message news:4861523d.3754.1681692777@sybase.com...
> For the following query:
>
> select ...
> from tba, tbb, ...
> where ...
> and tba.code not in ( "AA", "BB", ... )
> ...
>
> Would this query perform better(in general), if the the not
> in
> cond. is re-written as:
>
> and not ( tba.code in ( "AA", "BB", ... ) )


Sherlock, Kevin Posted on 2008-07-01 18:42:56.0Z
From: "Sherlock, Kevin" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <4861523d.3754.1681692777@sybase.com> <48696747$1@forums-1-dub>
Subject: Re: not in vs. not ( ... in )
Lines: 35
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <486a7ab0$1@forums-1-dub>
Date: 1 Jul 2008 11:42:56 -0700
X-Trace: forums-1-dub 1214937776 10.22.241.152 (1 Jul 2008 11:42:56 -0700)
X-Original-Trace: 1 Jul 2008 11:42:56 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10985
Article PK: 89595

To verify, use

dbcc traceon(3604,302)
go
select ...tba.code not in (...)
go
select ... not tba.code in (...)
go
dbcc traceoff(3604,302)
go

Both outputs should be the same.

"Sherlock, Kevin" <kevin.sherlock@teamsybase.com> wrote in message
news:48696747$1@forums-1-dub...
> No difference. They both normalize the same way.
>
> <sybfan> wrote in message news:4861523d.3754.1681692777@sybase.com...
>> For the following query:
>>
>> select ...
>> from tba, tbb, ...
>> where ...
>> and tba.code not in ( "AA", "BB", ... )
>> ...
>>
>> Would this query perform better(in general), if the the not
>> in
>> cond. is re-written as:
>>
>> and not ( tba.code in ( "AA", "BB", ... ) )
>
>