SAP SQL Anywhere

Question

A question based on do-functions-really-default-to-deterministic:

When creating functions without the [NOT] DETERMINISTIC clause, the server is free to treat them as deterministic.

This is documented both for ASA 8 and for newer versions up to SA 12.

I'm about to add that particular clause for all functions (and a lot of them do have side-effects), but beforehand I'd like to know if the server's treatment of functions has (significantly) changed between ASA 8 and SA 12 w.r.t. deterministic behaviour - i.e. will SA 12 make more use of function result caching than ASA 8?

Note that I'm generally using those functions with side-effects in simple queries such as

select fnMyFunction('blah');

so there's no need to call the same function multiple times within the same query. (For queries that use functions inside select lists, where clauses and the like, I would expect there have been optimizations to cache results.)

Accepted Answer

3

The documentation is correct that UDFs whose determinism is unspecified are treated as if DETERMINISTIC was actually declared.

It is difficult to answer whether or not SQL Anywhere 11/12 makes "more" use of cached results than was the case with Version 8. Even with Version 8, one could offer no guarantees to the number of times a function was evaluated because changes in context (ie access plan changes from invocation to invocation) could change the pattern of evaluation, and the sequence of parameter values used to invoke the function. All of that is equally true with Version 11/12, and even more so because predicate optimization in Version 11/12 is completely different than what existed in Version 8.

permanent link

answered 16 Jul '10, 21:42

Glenn Paulley
10.8k●5●76●106
accept rate: 43%

So I conclude that if I want to have guaranteed not-deterministic behaviour, I should specify the appropriate clause. I'm gonna do that - for both kinds of functions:)

(17 Jul '10, 10:25) Volker Barth

Excellent idea, if for no other reason than self-documentation.

(17 Jul '10, 11:56) Glenn Paulley

flat view

SAP SQL Anywhere

Is the treatment of "deterministic-unknown" functions similar in ASA 8 and SA 12?

Follow this question

Related questions