Deterministic expressions and functions always return the same result if they are evaluated with the same set of input values. All Java functions in Adaptive Server are deterministic. As a result, if the parameters and input values in an expression involving a Java function do not change, Adaptive Server treats the entire expression as deterministic.
When Adaptive Server encounters a Java function in an expression, Adaptive Server calculates the expression immediately so that the calculation is performed only once and not repeated for each row. This improves performance, but may cause unexpected behavior.
Consider this example:
1> create table CaseTest 2> (TestValue varchar(50)) 3> go 1> insert into CaseTest values(’07’) 2> go (1 row affected) 1> declare @IntArray sybase.cpp.value.client/common.IntArray 2> select @IntArray = new sybase.cpp.value.client.common.IntArray() 3> SELECT CASE 4> WHEN CT.TestValue = ’07’ 5> THEN @IntArray >> setInt(new java.lang.Integer(10)) 6> ELSE @IntArray >> setInt(new java.lang.Integer(11)) 7> END 8> FROM CaseTest CT 9> select @IntArray >> getInt(0) as GetObjAfter0 10> select @IntArray >> getInt(1) as GetObjAfter1 11> select @IntArray >> getArraySize() as NumObjectsOnArray 12> go ---------------------------------------------- sybase.cpp.value.client.common.IntArray@22cc0f30 (1 row affected) GetObjAfter0 ---------------------------------------------- 11 (1 row affected) NumObjectsOnArray --------------- 2 (1 row affected)
You might expect one branch of the case statement
to evaluate to true and thus have only one value (10) inserted into
the integer array, but because the expressions setInt(new
java.lang.Integer(10))
and setInt(new java.lang.Integer(11))
are
deterministic, Adaptive Server “precalculates” the
result, and populates the array with both values.
You can make expressions nondeterministic by adding a reference to columns so that Adaptive Server does not know that the expressions produce the same result for each execution. For example, make these changes to the Transact-SQL statements in the example:
1> declare @IntArray Sybase.cpp.value.client.common.IntArray 2> select @IntArray = new sybase.cpp.value.client.common.IntArray() 3> SELECT CASE 4> WHEN CT.TestValue = ’07’ 5> THEN @IntArray >> setInt(new java.lang.Integer(10 + convert(int,CT.TestValue) - convert(int,CT.TestValue))) 6> ELSE @IntArray >> setInt(new java.lang.Integer(11 + convert(int,CT.TestValue) - convert(int,CT.TestValue))) 7> END 8> FROM CaseTest CT 9> select @IntArray >> getInt(0) as GetObjAfter0 10> select @IntArray >> getInt(1) as GetObjAfter1 11> select @IntArray >> getArraySize() as NumObjectsOnArray 12> go
By including the column references in the THEN and ELSE portions of the case statement, the optimizer no longer treats the statements as constants and does not precalculate the Java insert statement.