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.

Query Plan

4 posts in General Discussion Last posting was on 2011-03-24 20:44:55.0Z
RGS Posted on 2011-03-22 14:44:53.0Z
Sender: 3c24.4d88b467.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Query Plan
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d88b5e5.3c57.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 22 Mar 2011 06:44:53 -0800
X-Trace: forums-1-dub 1300805093 10.22.241.41 (22 Mar 2011 06:44:53 -0800)
X-Original-Trace: 22 Mar 2011 06:44:53 -0800, 10.22.241.41
Lines: 33
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30063
Article PK: 79293

Hi!

Please your help with the next case:

I have two huge tables:

table1, nonclustered index: c11, c12

table2, nonclustered index: c21,c22,c23

table1 and table2 share the same value for fields: c11 and
c21

the next query:

select *
from table1,table2
where c11 = @w
and c21 = @w

Generate lower I/O's and best response time that:

select *
from table1,table2
where c11 = @w
and c21 = c11

The question is:

Is always better to write select sentences with compare with
fix values (@w) rather than do joins between tables? Why?

Thanks!


Bret Halford Posted on 2011-03-22 21:28:59.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.15) Gecko/20110303 Thunderbird/3.1.9
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Query Plan
References: <4d88b5e5.3c57.1681692777@sybase.com>
In-Reply-To: <4d88b5e5.3c57.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d89149b$1@forums-1-dub>
Date: 22 Mar 2011 13:28:59 -0800
X-Trace: forums-1-dub 1300829339 10.22.241.152 (22 Mar 2011 13:28:59 -0800)
X-Original-Trace: 22 Mar 2011 13:28:59 -0800, vip152.sybase.com
Lines: 56
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30068
Article PK: 79298


On 3/22/2011 8:44 AM, RGS wrote:
> Hi!
>
> Please your help with the next case:
>
> I have two huge tables:
>
> table1, nonclustered index: c11, c12
>
> table2, nonclustered index: c21,c22,c23
>
> table1 and table2 share the same value for fields: c11 and
> c21
>
> the next query:
>
> select *
> from table1,table2
> where c11 = @w
> and c21 = @w
>
> Generate lower I/O's and best response time that:
>
> select *
> from table1,table2
> where c11 = @w
> and c21 = c11
>
> The question is:
>
> Is always better to write select sentences with compare with
> fix values (@w) rather than do joins between tables? Why?
>
> Thanks!

What version?
What optimization criteria?
How are the query plans (set showplan on) output different?

It is generally better to provide the optimizer with as much
information as you can. In theory, search argument transitive closure
should automatically make the second query at least as good as the
first. But in my opinion, it would be best to not rely on that and
explicitly write the query as

select
*
from
table1,
table2
where
c11 = @w
and c21 = @w
and c21 = c11

-bret


Sherlock, Kevin [TeamSybase] Posted on 2011-03-22 21:36:03.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4d88b5e5.3c57.1681692777@sybase.com> <4d89149b$1@forums-1-dub>
Subject: Re: Query Plan
Lines: 62
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d891643$1@forums-1-dub>
Date: 22 Mar 2011 13:36:03 -0800
X-Trace: forums-1-dub 1300829763 10.22.241.152 (22 Mar 2011 13:36:03 -0800)
X-Original-Trace: 22 Mar 2011 13:36:03 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30069
Article PK: 79297

I would also ask for the output of sp_help for both tables.

"Bret Halford" <bret@sybase.com> wrote in message
news:4d89149b$1@forums-1-dub...
> On 3/22/2011 8:44 AM, RGS wrote:
>> Hi!
>>
>> Please your help with the next case:
>>
>> I have two huge tables:
>>
>> table1, nonclustered index: c11, c12
>>
>> table2, nonclustered index: c21,c22,c23
>>
>> table1 and table2 share the same value for fields: c11 and
>> c21
>>
>> the next query:
>>
>> select *
>> from table1,table2
>> where c11 = @w
>> and c21 = @w
>>
>> Generate lower I/O's and best response time that:
>>
>> select *
>> from table1,table2
>> where c11 = @w
>> and c21 = c11
>>
>> The question is:
>>
>> Is always better to write select sentences with compare with
>> fix values (@w) rather than do joins between tables? Why?
>>
>> Thanks!
>
> What version?
> What optimization criteria?
> How are the query plans (set showplan on) output different?
>
> It is generally better to provide the optimizer with as much
> information as you can. In theory, search argument transitive closure
> should automatically make the second query at least as good as the first.
> But in my opinion, it would be best to not rely on that and explicitly
> write the query as
>
> select
> *
> from
> table1,
> table2
> where
> c11 = @w
> and c21 = @w
> and c21 = c11
>
> -bret


RGS Posted on 2011-03-24 20:44:55.0Z
Sender: 3443.4d8ba5f5.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Re: Query Plan
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d8bad47.357f.1681692777@sybase.com>
References: <4d891643$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Mar 2011 12:44:55 -0800
X-Trace: forums-1-dub 1300999495 10.22.241.41 (24 Mar 2011 12:44:55 -0800)
X-Original-Trace: 24 Mar 2011 12:44:55 -0800, 10.22.241.41
Lines: 69
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30071
Article PK: 79299

So sorry but I don't have the data right now

Thanks anyway

> I would also ask for the output of sp_help for both
> tables.
>
> "Bret Halford" <bret@sybase.com> wrote in message
> news:4d89149b$1@forums-1-dub...
> > On 3/22/2011 8:44 AM, RGS wrote:
> >> Hi!
> >>
> >> Please your help with the next case:
> >>
> >> I have two huge tables:
> >>
> >> table1, nonclustered index: c11, c12
> >>
> >> table2, nonclustered index: c21,c22,c23
> >>
> >> table1 and table2 share the same value for fields: c11
> and >> c21
> >>
> >> the next query:
> >>
> >> select *
> >> from table1,table2
> >> where c11 = @w
> >> and c21 = @w
> >>
> >> Generate lower I/O's and best response time that:
> >>
> >> select *
> >> from table1,table2
> >> where c11 = @w
> >> and c21 = c11
> >>
> >> The question is:
> >>
> >> Is always better to write select sentences with compare
> with >> fix values (@w) rather than do joins between
> tables? Why? >>
> >> Thanks!
> >
> > What version?
> > What optimization criteria?
> > How are the query plans (set showplan on) output
> different? >
> > It is generally better to provide the optimizer with as
> > much information as you can. In theory, search
> > argument transitive closure should automatically make
> > the second query at least as good as the first. But in
> my opinion, it would be best to not rely on that and
> > explicitly write the query as
> >
> > select
> > *
> > from
> > table1,
> > table2
> > where
> > c11 = @w
> > and c21 = @w
> > and c21 = c11
> >
> > -bret
>
>