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
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.)
asked 16 Jul '10, 15:15
The documentation is correct that UDFs whose determinism is unspecified are treated as if
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.
answered 16 Jul '10, 21:42