I am trying to optimize the rollup code for my company and ran into a very peculiar issue. I converted many Scalar functions to be TVFs and they all seem to run more quickly than the original, which is great. However, in the queries that call them, they end up running significantly slower than the original. Here is a basic outline of my update:
SELECT col1, ..., colx, (CASE WHEN x <= 0 OR y <= 0 OR z <= 0 OR z = x THEN output WHEN valX <= 0 THEN output WHEN minimum.min < 1.0 THEN 1.0 ELSE minimum.min END) AS Q, FROM Tbl1...tblx (series of inner joins) CROSS APPLY dbo.inlinemin(val1, val2) AS minimum
This is a basic outline of the original:
SELECT col1, ..., colx, (CASE WHEN x <= 0 OR y <= 0 OR z <= 0 OR z = x THEN output WHEN valX <= 0 THEN output ELSE maximum(minimum(val1,val2),1.0) END) AS Q, FROM Tbl1...tblx (series of inner joins)
The numbers are more or less the same, as is the logic. The only difference is my function 'inlineMin' is a TVF as opposed to 'maximum' and 'minimum' the original Scalar functions. These functions are exceedingly simple and just return the max or min between the two passed parameters. Even the execution plan is more or less the same. There is a change from merge join to hash match at one point, however, the cost of this difference is minimal and could not account for the drastic change in elapsed time and cpu time.
When I run the functions outside of the rollup query my function is faster than the original for large sets of data. This makes sense given how TVFs work in comparison to scalar UDFs. However, when I call them in the query, my updated version runs roughly 6x slower. The cross apply is (seemingly) not the issue since leaving the cross apply and simply using the old functions
SELECT ... ELSE maximum(minimum(val1,val2),1.0) END) AS Q, FROM Tbl1...tblx (series of inner joins) CROSS APPLY inlinemin(val1, val2) AS NotUsedHere
Is roughly as efficient as the original code. It is only when I include the output of my function in the select that the query becomes significantly slower.
As I understand, the function is called and runs at the cross apply, meaning that it should be calculating a value even if it is not in the select, so why would it be faster not to include it in the select? Further, if the above is false, why would my function itself be faster but run significantly slower when used inside of a query?
Here is the Inline TVF that I have written to replace the original
CREATE FUNCTION [dbo].[InlineMin](@val1 FLOAT, @val2 FLOAT) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT minVal = CASE WHEN @val1 < @val2 THEN @val1 ELSE ISNULL(@val2,@val1) END
Here is the anonymized query plan for my rewrite: https://www.brentozar.com/pastetheplan/?id=ryKR_Q0Em
and the anonymized query plan for the original: https://www.brentozar.com/pastetheplan/?id=SJsS1-A4X