I upgraded an SQL Anywhere 9 database to the latest SQL Anywhere 17.0.10.6089 database. I know from the past that we need to check 2 options for our applications to run properly.

Option Ansi_substring should be set to 'Off'
Optimization_goal should be set to 'first-row' 

But after I upgraded the database and looked into the optimization_goal current value it is set to 'Response-time'. Is this a new valid value? I can't find anything in the help file or online documentation on this.

asked 02 Jul, 03:25

Frank's gravatar image

Frank
524111833
accept rate: 18%


As far as I can tell 'Response-time' has never been documented for optimization_goal since the option was introduced in V7 where the default was 'first-row'.

However it does appear that 'Response-time' is a synonym for 'First-row'... it's accepted by a V17 SET OPTION statement and it does get stored in the SYSOPTION table:

SET OPTION PUBLIC.Optimization_goal = 'Response-time';
SELECT * FROM SYS.SYSOPTIONS WHERE "option" = 'Optimization_goal';
user_name,option,setting
'PUBLIC','optimization_goal',Response-time

However, a subsequent ISQL SET statement displays 'First-row':

SET
optimization_goal   First-row


In your case the value 'Response-time' was probably carried forward by the upgrade process... there's no "AI" component in the upgrade that can decide "No, they really don't want the old value, they really want to change it to something better"...

...you are your own AI :)


For many (most?) applications, the application code DOES NOT receive and process (display) the first row of a large result set before the server has retrieved all the rows. Instead, the application does not display anything until the server has finished building the whole result set, and 'All-rows' is the value you want.

That's why 'All-rows' has been the default ever since Version 8.

See Tip: Choose the optimizer goal.

permanent link

answered 02 Jul, 09:59

Breck%20Carter's gravatar image

Breck Carter
30.6k494676989
accept rate: 21%

edited 02 Jul, 10:04

Searching within this forum is of help (and other search results might really anser your question):

Optimization_goal = 'Response-time'

IMVHO, this really just seems to be a synonym. You want to optimize the response time for the user waiting for the first results, right?

permanent link

answered 02 Jul, 03:53

Volker%20Barth's gravatar image

Volker Barth
36.4k343504757
accept rate: 34%

edited 02 Jul, 03:55

Yes. When we switch to all-rows users are complaining about performance of the application

(02 Jul, 10:11) Frank
Replies hidden

You want to optimize the "response time" for the user waiting for the "first results", right?

FWIW, I just had tried to make pun on the "synonym" goals of both options, it was no actual question...

(02 Jul, 10:22) Volker Barth

The equivalent optimization goal names.

Response-time == First-row

Resource-consumption == All-rows

permanent link

answered 03 Jul, 10:06

JBSchueler's gravatar image

JBSchueler
2.8k21253
accept rate: 18%

1

I presume you dug into the code, and thank you for that...

...or did you find actual documentation?

For the record...

IMO the term "Response-time" is hugely misleading because it implies the setting has a direct effect on the response time experienced by end users. That may be true for an application that presents the first row without waiting for the entire result set to be retrieved, but the opposite is true for an application that presents the result set after all rows have been retrieved (e.g., every PowerBuilder DataWindow application ever written :)

The terms "First-row" and "All-rows" are more useful and accurate because they describe what the server is doing. The server does not (and cannot) know what the application is doing with the data.

Note: First-row has no effect on many queries; e.g., SELECT COUNT(*).

(03 Jul, 10:45) Breck Carter
Replies hidden
1

Dug into code ... no justification from me ... and no idea why these alternate names exist ... but they've been there since October 2000 (last century). Perhaps you have provided a good reason as to why they aren't documented.

(03 Jul, 11:10) JBSchueler
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×26

question asked: 02 Jul, 03:25

question was seen: 228 times

last updated: 03 Jul, 11:10