Database option optimization_goal value after upgrading database from version 9 to 17

[fvestjens]

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.

[Breck_Carter]

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.

[jack_schueler]

The equivalent optimization goal names.

Response-time == First-row

Resource-consumption == All-rows

[Breck_Carter]

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(*).

[jack_schueler]

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.

[VolkerBarth]

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?

[fvestjens]

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

[VolkerBarth]

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...