1 Reply Latest reply on Dec 6, 2012 12:55 PM by Steven Hawkins

    Understanding deterministic vs non-deterministic functions

    Mark Addleman Master

      We have a user defined function that is, in fact, deterministic but was incorrectly marked as non-deterministic.  It takes multiple columns of data and concatenates them in some app-specific way.  When the function was marked as non-deterministic, we found that it was being executed many hundreds of thousands of times in a query that, ultimately, we cancelled because it ran too long.  When it is correctly marked as deterministic, the function is only called a few thousand times and the query returns in a timely manner. My question is, how does Teiid processing differ for deterministic vs non-deterministic functions?  Eyeballing the query plans for deterministic vs non-deterministic, it appears that they're the same but I could be missing something.

       

      Our query is something like this:

      SELECT *

      FROM t1 JOIN t2 ON t2.c=fn(t1.c1, t1.c2)

       

      (I've simplified the actual query quite a bit for this example but I think it captures what we're trying to do).