SQL Anywhere database - Deterministic functions

Author

Deterministic functions

Andrew Giulinn

2005-06-15, 3:23 am

Hi all

I am looking for some guidance when creating stored functions in ASA9.

The Help gives two extremes when describing the NOT DETERMINISTIC clause.
[color=darkred]

Functions that have side effects such as modifying the underlying data
should be declared as NOT DETERMINISTIC. For example, a function that
generates primary key values and is used in an INSERT ... SELECT statement
should be declared NOT DETERMINISTIC.
Functions may be declared as DETERMINISTIC if they always return the same
value for given input parameters.

<<

But what about functions that might return a different result each time they
are called because the data they are working on changes? For example, you
give it an ID, it looks up the record identified by that ID and it returns a
piece of data about that thing, maybe manipulated in some way. That means
that the data in that record *might* have changed since the last time it was
called. So it doesn't fit either description from the help.

This raises the point: since I would've expected that the bulk of stored
functions would be non-deterministic (since their results *might* change
between calls) - and since getting them around the wrong way could result in
incorrect data (as opposed to the better performance resulting from caching
of results), why is DETERMINISTIC the default?

Thanks

--
Andrew Giulinn
Senior Analyst/Programmer
Integrated Aviation Software Pty Ltd

GeoffAtDatagaard

2005-06-15, 3:23 am

Andrew Giulinn wrote:
> Hi all
>
> I am looking for some guidance when creating stored functions in ASA9.
>
> The Help gives two extremes when describing the NOT DETERMINISTIC clause.
>
>
>
> Functions that have side effects such as modifying the underlying data
> should be declared as NOT DETERMINISTIC. For example, a function that
> generates primary key values and is used in an INSERT ... SELECT statement
> should be declared NOT DETERMINISTIC.
> Functions may be declared as DETERMINISTIC if they always return the same
> value for given input parameters.
>
> <<
>
> But what about functions that might return a different result each time they
> are called because the data they are working on changes? For example, you
> give it an ID, it looks up the record identified by that ID and it returns a
> piece of data about that thing, maybe manipulated in some way. That means
> that the data in that record *might* have changed since the last time it was
> called. So it doesn't fit either description from the help.
>
> This raises the point: since I would've expected that the bulk of stored
> functions would be non-deterministic (since their results *might* change
> between calls) - and since getting them around the wrong way could result in
> incorrect data (as opposed to the better performance resulting from caching
> of results), why is DETERMINISTIC the default?
>
> Thanks
>
I havent played around with setting DETERMINISTIC wrong but, I have
noticed that when I fail to specify it, ASA produces an error *warning*,
if the results are non-deterministic, at the time it is called. I
learned this after importing and testing dozens of ASA 6 functions into
ASA 9. If it is smart enough to know that the results of a function
will be non deterministic, would it return incorrect data ?

Andrew Giulinn

2005-06-15, 3:23 am

Geoff

Hmm, I haven't seen any DETERMINISTIC-related warnings. Most calls to
functions in our system are within SQL calls from PowerBuilder datawindows,
so that might have suppressed them. That said, I just tried calling one
from ISQL and didn't get told anything either. So, while I can see the
logic of your statement ("If it is smart enough to know that the results of
a function will be non deterministic, would it return incorrect data?")
based on what you've seen, I hesitate to agree, not having seen any warnings
myself.

So, for me, it still comes back to:

1) What is meant by DETERMINISTIC/NON-DETERMINISTIC? That is, aside from
the extremes described in the help, what functions need to be what?
2) What is the default? That is, if I can't see either setting anywhere
for my functions, what will they be run as.

Cheers

Andrew

"GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
news:42afd51c@forums
-2-dub...
> Andrew Giulinn wrote:
> I havent played around with setting DETERMINISTIC wrong but, I have
> noticed that when I fail to specify it, ASA produces an error *warning*,
> if the results are non-deterministic, at the time it is called. I learned
> this after importing and testing dozens of ASA 6 functions into ASA 9. If
> it is smart enough to know that the results of a function will be non
> deterministic, would it return incorrect data ?
>
>
>

David Kerber

2005-06-15, 7:23 am

In article <42afd832@forums-2-dub>, NO.oscar.SPAM@dsa.com.au says...
> Geoff
>
> Hmm, I haven't seen any DETERMINISTIC-related warnings. Most calls to
> functions in our system are within SQL calls from PowerBuilder datawindows,
> so that might have suppressed them. That said, I just tried calling one
> from ISQL and didn't get told anything either. So, while I can see the
> logic of your statement ("If it is smart enough to know that the results of
> a function will be non deterministic, would it return incorrect data?")
> based on what you've seen, I hesitate to agree, not having seen any warnings
> myself.
>
> So, for me, it still comes back to:
>
> 1) What is meant by DETERMINISTIC/NON-DETERMINISTIC? That is, aside from
> the extremes described in the help, what functions need to be what?
> 2) What is the default? That is, if I can't see either setting anywhere
> for my functions, what will they be run as.

My understanding is simply that a function is deterministic if calling
it with the same parameters will always return the same result, and non-
deterministic otherwise. For example, any mathematical function would
be deterministic, while most functions involving data in your database
will be non-deterministic, unless you KNOW that the data won't change in
a way which would affect your results.

....

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Breck Carter [TeamSybase]

2005-06-15, 9:23 am

Are you sure the warning was caused by a non-deterministic function
that was defaulting to DETERMINISTIC?

On 15 Jun 2005 00:13:35 -0700, GeoffAtDatagaard
<geoff@datagaard.com.au> wrote:

>Andrew Giulinn wrote:
>I havent played around with setting DETERMINISTIC wrong but, I have
>noticed that when I fail to specify it, ASA produces an error *warning*,
>if the results are non-deterministic, at the time it is called. I
>learned this after importing and testing dozens of ASA 6 functions into
> ASA 9. If it is smart enough to know that the results of a function
>will be non deterministic, would it return incorrect data ?
>
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Paul Horan[TeamSybase]

2005-06-15, 9:23 am

Andrew,
This is MY understanding - so it may not be 100% accurate...

The key factor to consider when making a function DETERMINISTIC or NOT DETERMINISTIC is the scope of the call.
It's only considered during a single query execution. When you define a function as DETERMINISTIC, you're telling the
engine, "if, during the course of executing THIS query, you see a second call to the function with the same exact
inputs, there's no need to re-invoke the function. Just use the result that you've already cached."
With NON-DETERMINISTIC, the engine can't use cached function results, and must re-invoke the function for each row.

An example of a NON-DETERMINISTIC function would be one that does inserts/updates to the database - during the course of
a single query, the same inputs to the function might return a different result. You gave an example where data might
change "between" calls - that's not pertinent.

--
Paul Horan[TeamSybase]

"Andrew Giulinn" <NO.oscar.SPAM@dsa.com.au> wrote in message news:42afa6ba$1@foru
ms-1-dub...
> Hi all
>
> I am looking for some guidance when creating stored functions in ASA9.
>
> The Help gives two extremes when describing the NOT DETERMINISTIC clause.
>
>
> Functions that have side effects such as modifying the underlying data should be declared as NOT DETERMINISTIC. For
> example, a function that generates primary key values and is used in an INSERT ... SELECT statement should be declared
> NOT DETERMINISTIC.
> Functions may be declared as DETERMINISTIC if they always return the same value for given input parameters.
>
> <<
>
> But what about functions that might return a different result each time they are called because the data they are
> working on changes? For example, you give it an ID, it looks up the record identified by that ID and it returns a
> piece of data about that thing, maybe manipulated in some way. That means that the data in that record *might* have
> changed since the last time it was called. So it doesn't fit either description from the help.
>
> This raises the point: since I would've expected that the bulk of stored functions would be non-deterministic (since
> their results *might* change between calls) - and since getting them around the wrong way could result in incorrect
> data (as opposed to the better performance resulting from caching of results), why is DETERMINISTIC the default?
>
> Thanks
>
> --
> Andrew Giulinn
> Senior Analyst/Programmer
> Integrated Aviation Software Pty Ltd
>

Breck Carter [TeamSybase]

2005-06-15, 9:23 am

On 15 Jun 2005 00:26:45 -0700, "Andrew Giulinn"
<NO.oscar.SPAM@dsa.com.au> wrote:

>Geoff
>
>Hmm, I haven't seen any DETERMINISTIC-related warnings. Most calls to
>functions in our system are within SQL calls from PowerBuilder datawindows,
>so that might have suppressed them. That said, I just tried calling one
>from ISQL and didn't get told anything either. So, while I can see the
>logic of your statement ("If it is smart enough to know that the results of
>a function will be non deterministic, would it return incorrect data?")
>based on what you've seen, I hesitate to agree, not having seen any warnings
>myself.
>
>So, for me, it still comes back to:
>
>1) What is meant by DETERMINISTIC/NON-DETERMINISTIC? That is, aside from
>the extremes described in the help, what functions need to be what?

If you want the function body to be executed every time the function
is called, make it NOT DETERMINISTIC. In particular: if the function
can return different values when passed the same (or no) input
parameter values, OR the function has side-effects, make it NOT
DETERMINISTIC.

Be careful, though: a function reference in a FROM or WHERE clause may
not be called as often as you think it might be, and that has to do
with query optimization, not function caching. The function has to be
actually *called* at runtime for the NOT DETERMINISTIC or
DETERMINISTIC to matter.

>2) What is the default? That is, if I can't see either setting anywhere
>for my functions, what will they be run as.

DETERMINISTIC, meaning the return value is determined by the input
parameters. Like ABS ( -1 ) always returns 1 no matter what. The side
effect is that side effects might not get executed :)

Note that case sensitive string comparisons are used, even if the
database isn't.

Breck

>
>Cheers
>
>Andrew
>
>"GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
> news:42afd51c@forums
-2-dub...
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Ivan T. Bowman

2005-06-15, 9:23 am

There are actually three settings of determinism for a function:
1. UNSPECIFIED (the default)
2. DETERMINISTIC (the function always returns the same result for a given
list of argument values)
3. NOT DETERMINISTIC (the function does not meet the DETERMINISTIC criteria,
and, further, it is required to avoid relying on cached results of the
function).

The database server uses caching for functions that are either UNSPECIFIED
or DETERMINISTIC. Caching has three forms: same-row caching, function
caching, and common sub-expression elimination.

Same-Row caching:
SELECT F( R.x ), R.y, S.y
FROM R, S
WHERE S.y = R.y
Let's assume that the optimizer uses a nested loops join with R first in the
strategy. A single R row may join to several rows of S. When the first
joined row is returned, F(R.x) is computed. If same-row caching is used,
then F(R.x) is not re-computed until another row is fetched from R. This
saves re-evaluating F() for all the rows of S that match that particular R
row.

Function caching:

If you have an expensive function F(x,y), then it would be cheaper to store
the result of previous evaluations in a hash table. When a new value
F(x1,y1) is needed, the server first looks in the hash table for values
(x1,y1) to see if there is a cached value available. If so, it uses the
cached value, avoiding a function call.

Common sub-expression elimination:

SELECT F(R.x)
FROM R
WHERE F(R.x) = 0
The two uses of function F(R.x) can be matched by common-sub-expression
elimination so that the function is evaluated only one time.

Same-row caching, function caching, and common sub-expression elimination
are used for both UNSPECIFIED and NOT DETERMINISTIC functions. These caching
methods save the server a significant amount of work, and generally lead to
more efficient execution. However, there are cases where it is important to
the semantics of a function that a stored value should not be used. For
builtin functions, the RAND() and NEWID() functions are examples where it is
important to the way people expect these to work that the value NOT be
cached by any of the above mechanisms.

For not-deterministic functions, the server ensures that common
sub-expression elimination, function caching, and same-row caching are not
used. This means that the function is evaluated every time its value is
needed, which can lead to a significant increase in the number of
executions.

The recommendation for DETERMINISTIC / NOT DETERMINISTIC when creating a
function should be the following:
1. Leave determinism unspecified unless you have a particular need.
2. Mark a function as NOT DETERMINISTIC if you want to force it not to be
cached. This will result in restrictions on the use of the function (can't
reference aliases, etc.), and will lead to extra cost.
3. Mark a function as DETERMINISTIC only if it will always return the same
value for the same inputs.

It may be the case that a stale answer may be given by the server for a
function that is declared UNSPECIFIED or DETERMINISTIC. This staleness
results from using the result of a previous evaluation to answer a query. If
this difference is of particular importance to your application, you can use
the NOT DETERMINISTIC declaration to avoid the stale result, but the
tradeoff is additional cost.

--
Ivan Bowman
ASA Query Processing
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

Breck Carter [TeamSybase]

2005-06-15, 8:24 pm

What exactly is the difference between UNSPECIFIED and DETERMINISTIC?

On 15 Jun 2005 07:30:09 -0700, "Ivan T. Bowman"
<ibowman@ianywhere.NOSPAM.com> wrote:

>There are actually three settings of determinism for a function:
>1. UNSPECIFIED (the default)
>2. DETERMINISTIC (the function always returns the same result for a given
>list of argument values)
>3. NOT DETERMINISTIC (the function does not meet the DETERMINISTIC criteria,
>and, further, it is required to avoid relying on cached results of the
>function).
>
>The database server uses caching for functions that are either UNSPECIFIED
>or DETERMINISTIC. Caching has three forms: same-row caching, function
>caching, and common sub-expression elimination.
>
>Same-Row caching:
> SELECT F( R.x ), R.y, S.y
> FROM R, S
> WHERE S.y = R.y
>Let's assume that the optimizer uses a nested loops join with R first in the
>strategy. A single R row may join to several rows of S. When the first
>joined row is returned, F(R.x) is computed. If same-row caching is used,
>then F(R.x) is not re-computed until another row is fetched from R. This
>saves re-evaluating F() for all the rows of S that match that particular R
>row.
>
>Function caching:
>
>If you have an expensive function F(x,y), then it would be cheaper to store
>the result of previous evaluations in a hash table. When a new value
>F(x1,y1) is needed, the server first looks in the hash table for values
>(x1,y1) to see if there is a cached value available. If so, it uses the
>cached value, avoiding a function call.
>
>Common sub-expression elimination:
>
> SELECT F(R.x)
> FROM R
> WHERE F(R.x) = 0
>The two uses of function F(R.x) can be matched by common-sub-expression
>elimination so that the function is evaluated only one time.
>
>Same-row caching, function caching, and common sub-expression elimination
>are used for both UNSPECIFIED and NOT DETERMINISTIC functions. These caching
>methods save the server a significant amount of work, and generally lead to
>more efficient execution. However, there are cases where it is important to
>the semantics of a function that a stored value should not be used. For
>builtin functions, the RAND() and NEWID() functions are examples where it is
>important to the way people expect these to work that the value NOT be
>cached by any of the above mechanisms.
>
>For not-deterministic functions, the server ensures that common
>sub-expression elimination, function caching, and same-row caching are not
>used. This means that the function is evaluated every time its value is
>needed, which can lead to a significant increase in the number of
>executions.
>
>The recommendation for DETERMINISTIC / NOT DETERMINISTIC when creating a
>function should be the following:
>1. Leave determinism unspecified unless you have a particular need.
>2. Mark a function as NOT DETERMINISTIC if you want to force it not to be
>cached. This will result in restrictions on the use of the function (can't
>reference aliases, etc.), and will lead to extra cost.
>3. Mark a function as DETERMINISTIC only if it will always return the same
>value for the same inputs.
>
>It may be the case that a stale answer may be given by the server for a
>function that is declared UNSPECIFIED or DETERMINISTIC. This staleness
>results from using the result of a previous evaluation to answer a query. If
>this difference is of particular importance to your application, you can use
>the NOT DETERMINISTIC declaration to avoid the stale result, but the
>tradeoff is additional cost.

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Breck Carter [TeamSybase]

2005-06-15, 8:24 pm

On 15 Jun 2005 06:54:33 -0700, "Paul Horan[TeamSybase]"
< paulhATvcisolutionsD
OTcom> wrote:

>You gave an example where data might
>change "between" calls - that's not pertinent.

It might be pertinent, considering Ivan's discussion.

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Ivan T. Bowman

2005-06-15, 8:24 pm

Paul is right, and thank you for pointing that out.
The database server does not currently cache results across statements.

The 'staleness' refers to a function return value used within a single
statement. Note that, for cursors, there could be intervening updates from
the same transaction that would otherwise be noted in the function result.

open crsr [select F(R.x) from R]
fetch crsr
update T
fetch crsr <-- might return a value of F(R.x) that is 'stale' in that it
does not reflect update to T
close crsr

If you really want to ensure that you see the updated F(R.x), you need to
mark it as NOT DETERMINISTIC (and also use a sensitive cursor / disable
prefetching).

But, if you have something like:
[select F(R.x) from R where R.y=1]
update T
[select F(R.x) from R where R.y=2]

The second query will not re-use cached results from the previous statement.

A fly in the ointment here is computed columns. If you declare an
appropriate computed column over a function, the server will detect when the
computed column can be used in place of evaluating the function. This is a
form of caching across statements, but it is at the explicit request of the
person that created the computed column. NOT DETERMINISTIC functions can not
be used in computed columns. At some point, ASA might be changed so that
computed columns can only refer to functions that are explicitly marked as
DETERMINISTIC; I don't know of plans to enforce that [see my reply to
Breck's other post].

--
Ivan Bowman
ASA Query Processing
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:ka31b19snj6m5ij
13i4ese39o2sqebfkuq@
4ax.com...
> On 15 Jun 2005 06:54:33 -0700, "Paul Horan[TeamSybase]"
> < paulhATvcisolutionsD
OTcom> wrote:
>
>
> It might be pertinent, considering Ivan's discussion.
>
> Breck
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Ivan T. Bowman

2005-06-15, 8:24 pm

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:j531b1140bhe8i3
sjq80doje7jvt703mui@
4ax.com...
> What exactly is the difference between UNSPECIFIED and DETERMINISTIC?

At present, the server does not treat UNSPECIFIED and DETERMINISTIC
differently, so in practice the two have the same semantics at present. In
the future, we may introduce restrictions that require DETERMINISTIC
functions in certain contexts. For example, we might require that computed
columns reference only deterministic functions.

Logically, the server considers that a function marked as DETERMINISTIC will
_always_ return the same output for a given input, regardless of the
contents of the database, the current date, or other external factors. This
means that caching across statements would be possible with functions
explicitly marked as DETERMINISTIC. At present, ASA does not implement such
caching, with the exception of computed columns.

I can consider optimizations that would, for example, evaluate DETERMINISTIC
functions once within stored procedures (provided that all of the parameters
are constants). This optimization would not be acceptable with an
UNSPECIFIED function. At present we do not have such optimizations, so the
difference between UNSPECIFIED and DETERMINISTIC is purely theoretical.

I suppose that for completeness we should have a specific setting such as
ADETERMINISTIC that means what UNSPECIFIED means: the determinism is not
specified by the creator of the function, and the server should not cache
across statement boundaries but should also not go out of its way to
re-evaluate the function.

--
Ivan Bowman
ASA Query Processing
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

Andrew Giulinn

2005-06-15, 8:24 pm

Ahhh! It is the *within* a statement aspect that I have missed - now I see
why the reference to side effects! One call may leave a change, eg a new
row, that means that the result of the subsequent call cannot be the same as
the previous result, eg if the function checks the rowcount.

So, if I have a function that has no side effects but looks at data in
tables (that might change between calls) and that function is called by the
SQL behind a PB datawindow, it is OK to leave it as UNSPECIFIED (to use
Ivan's terminology) because it will be executed (and therefore consider any
new/changed data) each time that datawindow retrieves. But if that function
appears twice in that SQL it will only be executed one, with the result
cached (which is fine, since there is no reliance on any side effects from
the the earlier call).

So, in summary,

(a) I should set any functions that I know will always have the same result
for the same arguments, eg a function simply existing to perform a
calculation using the arguments (like the mathematical function example
someone gave in this thread), without reference to volatile data, to
DETERMINISTIC explicitly, thus ensuring definitely caching occurs now and in
future.
(b) I should set any functions that have side effects (I think I am well
trained to avoid such a thing but I will check for any) that I want
subsequent calls to pick up, to be NON DETERMINISTIC explicitly, and
(c) I should (for now) leave other functions as UNSPECIFIED for now,
changing them only if we find there is a problem.

Thanks all. I hope that this discussion helped more than just me to
understand this area.

Cheers

--
Andrew Giulinn
Senior Analyst/Programmer
Integrated Aviation Software Pty Ltd

"Ivan T. Bowman" <ibowman@ianywhere.NOSPAM.com> wrote in message
news:42b0b18c$1@foru
ms-1-dub...
> Paul is right, and thank you for pointing that out.
> The database server does not currently cache results across statements.
>
> The 'staleness' refers to a function return value used within a single
> statement. Note that, for cursors, there could be intervening updates from
> the same transaction that would otherwise be noted in the function result.
>
> open crsr [select F(R.x) from R]
> fetch crsr
> update T
> fetch crsr <-- might return a value of F(R.x) that is 'stale' in that
> it
> does not reflect update to T
> close crsr
>
> If you really want to ensure that you see the updated F(R.x), you need to
> mark it as NOT DETERMINISTIC (and also use a sensitive cursor / disable
> prefetching).
>
> But, if you have something like:
> [select F(R.x) from R where R.y=1]
> update T
> [select F(R.x) from R where R.y=2]
>
> The second query will not re-use cached results from the previous
> statement.
>
> A fly in the ointment here is computed columns. If you declare an
> appropriate computed column over a function, the server will detect when
> the
> computed column can be used in place of evaluating the function. This is a
> form of caching across statements, but it is at the explicit request of
> the
> person that created the computed column. NOT DETERMINISTIC functions can
> not
> be used in computed columns. At some point, ASA might be changed so that
> computed columns can only refer to functions that are explicitly marked as
> DETERMINISTIC; I don't know of plans to enforce that [see my reply to
> Breck's other post].
>
> --
> Ivan Bowman
> ASA Query Processing
> iAnywhere Solutions Engineering
>
> Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
> Developer Community at http://www.ianywhere.com/developer
>
> "Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
> message news:ka31b19snj6m5ij
13i4ese39o2sqebfkuq@
4ax.com...
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
>
>

Breck Carter [TeamSybase]

2005-06-16, 7:23 am

This insight is *great*. As far as I'm concerned, your explanations
could be dropped into the Help as-is, or a book, no editing required.
And yes, please do put ADETERMINISTIC into the syntax.

Breck

On 15 Jun 2005 16:02:45 -0700, "Ivan T. Bowman"
<ibowman@ianywhere.NOSPAM.com> wrote:

>"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
>message news:j531b1140bhe8i3
sjq80doje7jvt703mui@
4ax.com...
>
>At present, the server does not treat UNSPECIFIED and DETERMINISTIC
>differently, so in practice the two have the same semantics at present. In
>the future, we may introduce restrictions that require DETERMINISTIC
>functions in certain contexts. For example, we might require that computed
>columns reference only deterministic functions.
>
>Logically, the server considers that a function marked as DETERMINISTIC will
>_always_ return the same output for a given input, regardless of the
>contents of the database, the current date, or other external factors. This
>means that caching across statements would be possible with functions
>explicitly marked as DETERMINISTIC. At present, ASA does not implement such
>caching, with the exception of computed columns.
>
>I can consider optimizations that would, for example, evaluate DETERMINISTIC
>functions once within stored procedures (provided that all of the parameters
>are constants). This optimization would not be acceptable with an
>UNSPECIFIED function. At present we do not have such optimizations, so the
>difference between UNSPECIFIED and DETERMINISTIC is purely theoretical.
>
>I suppose that for completeness we should have a specific setting such as
>ADETERMINISTIC that means what UNSPECIFIED means: the determinism is not
>specified by the creator of the function, and the server should not cache
>across statement boundaries but should also not go out of its way to
>re-evaluate the function.

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Andrew Giulinn

2005-06-19, 8:23 pm

And, since that is what tripped me up, if a documentation/Help change is
going to be made, please include something more to ensure that people like
me understand that these settings (at least for now) apply within a
statement, not across statement boundaries (with some examples - like those
that helped me in this thread - of what "within a statement" and "across
statement boundaries" actually means!).

Cheers

Andrew

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:jrn2b1de53ft1h5
4qo6jl7g357iarjs3r6@
4ax.com...
> This insight is *great*. As far as I'm concerned, your explanations
> could be dropped into the Help as-is, or a book, no editing required.
> And yes, please do put ADETERMINISTIC into the syntax.
>
> Breck
>
> On 15 Jun 2005 16:02:45 -0700, "Ivan T. Bowman"
> <ibowman@ianywhere.NOSPAM.com> wrote:
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Breck Carter [TeamSybase]

2005-06-20, 7:23 am

You can increase the chances that someone will act on your suggestions
by posting them in the product_futures_disc
ussion newsgroup.

Breck

On 19 Jun 2005 16:57:18 -0700, "Andrew Giulinn"
<NO.oscar.SPAM@dsa.com.au> wrote:

>And, since that is what tripped me up, if a documentation/Help change is
>going to be made, please include something more to ensure that people like
>me understand that these settings (at least for now) apply within a
>statement, not across statement boundaries (with some examples - like those
>that helped me in this thread - of what "within a statement" and "across
>statement boundaries" actually means!).
>
>Cheers
>
>Andrew
>
>"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
>message news:jrn2b1de53ft1h5
4qo6jl7g357iarjs3r6@
4ax.com...
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links