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.

optimizer goal: all-rows vs. first-row

3 posts in General Discussion Last posting was on 2003-12-04 19:36:11.0Z
"Leo Tohill" <lit Posted on 2003-12-04 16:38:13.0Z
From: "Leo Tohill" <lit@spammersgoaway_cbord.com>
Newsgroups: ianywhere.public.general
Subject: optimizer goal: all-rows vs. first-row
Lines: 27
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: cbord.com
X-Original-NNTP-Posting-Host: cbord.com
Message-ID: <3fcf62f5@forums-1-dub>
Date: 4 Dec 2003 08:38:13 -0800
X-Trace: forums-1-dub 1070555893 12.33.64.34 (4 Dec 2003 08:38:13 -0800)
X-Original-Trace: 4 Dec 2003 08:38:13 -0800, cbord.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2179
Article PK: 4387

Our tests of this opmization setting has produced some curious results. We
have looked at the effect of this setting both in ASA 7.0.3.2124 and
8.0.2.3601. Our test involves multiple operations. All queries consume all
rows of the result, so we would expect the all-rows setting to be best.

Our finding: In ASA 7, it is true that all-rows is best. In ASA 8,
first-row is best.

The really ironic thing is that in ASA 8.0.2, ASA changed the default to
all-rows. So just as they changed the default to the setting that Should
give better performance, other engine changes have caused the setting to
give poorer results - at least for our tests.

In the back of my mind I'm wondering if there's not a bug somewhere in the
optimizer that causes it to mis-interpret the setting.

- leo


Robert Waywell Posted on 2003-12-04 19:30:41.0Z
From: "Robert Waywell" <nospam_rwaywell@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <3fcf62f5@forums-1-dub>
Subject: Re: optimizer goal: all-rows vs. first-row
Lines: 60
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
NNTP-Posting-Host: rwaywell-pc.sybase.com
X-Original-NNTP-Posting-Host: rwaywell-pc.sybase.com
Message-ID: <3fcf8b61$1@forums-1-dub>
Date: 4 Dec 2003 11:30:41 -0800
X-Trace: forums-1-dub 1070566241 172.31.142.236 (4 Dec 2003 11:30:41 -0800)
X-Original-Trace: 4 Dec 2003 11:30:41 -0800, rwaywell-pc.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2180
Article PK: 4385

Hi Leo,
How are you handling the result set? What actual operations are you
performing? Assuming that your application is manipulating the records
returned in some way, then first-row may be the best choice even when you
eventually want the entire result set.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

"Leo Tohill" <lit@spammersgoaway_cbord.com> wrote in message
news:3fcf62f5@forums-1-dub...
> Our tests of this opmization setting has produced some curious results.
We
> have looked at the effect of this setting both in ASA 7.0.3.2124 and
> 8.0.2.3601. Our test involves multiple operations. All queries consume
all
> rows of the result, so we would expect the all-rows setting to be best.
>
> Our finding: In ASA 7, it is true that all-rows is best. In ASA 8,
> first-row is best.
>
> The really ironic thing is that in ASA 8.0.2, ASA changed the default to
> all-rows. So just as they changed the default to the setting that Should
> give better performance, other engine changes have caused the setting to
> give poorer results - at least for our tests.
>
> In the back of my mind I'm wondering if there's not a bug somewhere in the
> optimizer that causes it to mis-interpret the setting.
>
> - leo
>
>
>
>
>
>
>
>
>
>


Glenn Paulley Posted on 2003-12-04 19:36:11.0Z
From: Glenn Paulley <paulley@ianywhere.com>
Reply-To: paulley@ianywhere.com
Organization: iAnywhere Solutions
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.2.1) Gecko/20021130
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: optimizer goal: all-rows vs. first-row
References: <3fcf62f5@forums-1-dub>
In-Reply-To: <3fcf62f5@forums-1-dub>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: paulley-xp.sybase.com
Message-ID: <3fcf8e44$1@forums-2-dub>
X-Original-Trace: 4 Dec 2003 11:43:00 -0800, paulley-xp.sybase.com
Lines: 68
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 4 Dec 2003 11:25:01 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 4 Dec 2003 11:36:11 -0800
X-Trace: forums-1-dub 1070566571 10.22.108.75 (4 Dec 2003 11:36:11 -0800)
X-Original-Trace: 4 Dec 2003 11:36:11 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2181
Article PK: 4388

There is absolutely no question in my view that changing the default to
'all-rows' in 8.0.2 was the right thing to do. Our thinking as we
released 8.0.0 was to set the default to 'first-row' as an attempt to
match the typical strategies generated in the 7.0.4 optimizer, and thus
minimally affect existing applications. With 20/20 hindsight, that
decision was flawed - we underestimated the extent to which to the new
8.0 optimizer would alter the execution strategies for typical applications.

We have also learned a great deal in the two years since 8.0.0 first
shipped, and have encorporated much of that knowledge into 8.0.2 and
into EBFs for the 8.0.2 release (note that the current 8.0.2EBF build of
4351 is 750 builds past 3601). In particular, we've made several changes
to the optimizer to reflect the advantage of utilizing index strategies
when a significant proportion of the table is cached. I believe that if
you moved up to a more current build of 8.0.2 your testing might yield
different results. On the other hand, every database application is
"tuned" to a particular DBMS release, either intentionally or not; and,
notwithstanding the enhancements that we've made to ASA, there is of
course no guarantee (despite our best efforts) that these changes will
benefit your application.

Glenn

Leo Tohill wrote:
> Our tests of this opmization setting has produced some curious results. We
> have looked at the effect of this setting both in ASA 7.0.3.2124 and
> 8.0.2.3601. Our test involves multiple operations. All queries consume all
> rows of the result, so we would expect the all-rows setting to be best.
>
> Our finding: In ASA 7, it is true that all-rows is best. In ASA 8,
> first-row is best.
>
> The really ironic thing is that in ASA 8.0.2, ASA changed the default to
> all-rows. So just as they changed the default to the setting that Should
> give better performance, other engine changes have caused the setting to
> give poorer results - at least for our tests.
>
> In the back of my mind I'm wondering if there's not a bug somewhere in the
> optimizer that causes it to mis-interpret the setting.
>
> - leo
>
>
>
>
>
>
>
>
>
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer