non-deterministic functions

one of the functions that i use is found to be non-deterministic function. So what does this mean w.r.t performance?
will this function be recompiled every time it is used?

this function uses data from 2 oltp tables which will have new records very often. so for the same input values if i get 10 records for first run i might get 15 in the second run because of inserts in the underlying tables. i also use date calculations inside the function which will make the function non-deterministic. But without date calculations should this function be termed non-deterministic because of changes in the output for same input values?
JyozealAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>will this function be recompiled every time it is used?
no. non-deterministic has nothing to do with compilation/recompilation


>so for the same input values if i get 10 records for first run i might get 15 in the second run because of inserts in the underlying tables.

this also has nothing to do with "deterministic". as "same input + same output" also refers to the table data.

>But without date calculations should this function be termed non-deterministic because of changes in the output for same input values?
date calculations also have nothing to do with a function being deterministic or not.


let's take some concrete examples:
the getdate() function is non-deterministic, as the returned value will be different (for an obvious reason)
the random() funciton is non-deterministic.


if you have a function that accepts a date value, and does some maths on the parameter, will be (can be) deterministric, because as long as you pass the same value, it will return the same result.

for the list of deterministic functions in sql server, see here:
https://msdn.microsoft.com/en-us/library/ms178091.aspx
as you can see, some functions are sometimes deterministic, depending on the arguments


deterministic vs cache of plans:
there is no direct correlation between a function being deterministic (or not) vs the sql plan caching of the query.
if the values are bound or returned by functions, the sql engine cannot know, in advance, which values to check against, and will determine some plan, "hopefully" the best one.
the sql plan does consider the returned value(s) of functions vs the statistical data of the tables and indexes, so having a function in there that returns a value or directly a found value will not change the results

what CAN be cached is that if the function is deterministic, and has no correlated data input from the data record, it's output value will be "cached" for the sql run/execution and not be executed for each row.

for example, if you have this:
declare @p int
set @p = 100
select yourfunction(@p) x , t.* from your_table

the function shall be called once (if it's deterministic)  or once for each row of the table (if it's non-deterministic)
0
 
Dave BaldwinFixer of ProblemsCommented:
Yes.  non-deterministic basically means that you won't necessarily get the same answer each time.  That's not an error, just a condition.  http://en.wikipedia.org/wiki/Nondeterministic_algorithm
0
 
JyozealAuthor Commented:
thanks for your reply. i forgot another part of my question. so for non deterministic functions will sql cache the plans or not? will it make any difference in plan caching between using subquery and cte in this function because of its non-deterministic nature?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dave BaldwinFixer of ProblemsCommented:
I have no idea about that part.  Please click on "Request Attention" to get others to look at your question.  You could also open another question specifically about that.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
A non-deterministic function cannot be used to get an optimized query, the values are seen as varying and unknown. No caching of the corresponding condition can be done. This might cause degraded performance or not.
A CTE usually can be optimized, and hence improve speed.
In fact you can't tell for sure, as it depends on a lot of factors.
0
 
Eugene ZCommented:
...2 more cents :
<you can simply make a function deterministic by adding schemabinding option. it shoul make it much faster.>
see more @:

Slow query using non-deterministic user defined function
http://blogs.msdn.com/b/psssql/archive/2014/07/08/slow-query-using-non-deterministic-user-defined-function.aspx
0
 
JyozealAuthor Commented:
Thanks for your comments. I cannot make my function deterministic because i use getdate() .
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you always could add a date paramter to the function that will need to get passed the value of getdate() ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.