Question Re: Database option optimization_goal value after upgrading database from version 9 to 17 in Technology Q&A

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

fvestjens — Thu, 02 Jul 2020 03:25:17 GMT

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.

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

VolkerBarth — Thu, 02 Jul 2020 03:53:09 GMT

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?

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

Breck_Carter — Thu, 02 Jul 2020 09:59:41 GMT

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.

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

fvestjens — Thu, 02 Jul 2020 10:11:44 GMT

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

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

VolkerBarth — Thu, 02 Jul 2020 10:22:08 GMT

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

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

jack_schueler — Fri, 03 Jul 2020 10:06:25 GMT

The equivalent optimization goal names.

Response-time == First-row

Resource-consumption == All-rows

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

Breck_Carter — Fri, 03 Jul 2020 10:45:23 GMT

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

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

jack_schueler — Fri, 03 Jul 2020 11:10:57 GMT

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.