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.