I am writing a Table Value Function, calling the function takes 10x as long as directly running the code. I traced this to a call to a multi-line scalar function inside the TVF. The call to the scalar function is excessively slow WHEN called from within the TVF. The scalar function takes 3 int parameters and returns a single int result.
What would cause it to be slower from within a TVF?
First, the TVF is basically a sort of pivot table, returning just one row, with 13 columns.
The scalar function is a multiline scalar, it looks for a matched set of keys (ie input is columnA, output is columnB, where input = columnA and active) in one of two tables. Searching table 1, then table 2 and finally table 1 again with a slightly changed where clause.
Usage was originally a cte query with a join clause:
With cte as ( select count(*) over (partition by c.c1) recs, c.setId, c.c1, c.c2, n.name From tbl c Inner join tbl n on n.id = schma.scal(c.c1, c.c2, c.c3) Where c.setId=@setId and c.endDt is null )
This cte was originally called 11 times, returning from (about half the time) zero to maybe 3 rows (with a where clause like c1=42). So, I thought, hey, it's getting called a lot, since there are really only 4-15 rows in total, and the base tables are ~1,000,000, I'll cut that down to just 15 calls by putting the values into a table variable, and then doing an update. This wasn't really any faster but it did let me prove that it was the call to the scalar function that was slowing things down.
That looked like:
Insert into @cte(recs, setId, c1, c2) select count(*) over (partition by c.c1) recs, c.setId, c.c1, c.c2 From tbl c Where c.setId=@setId and c.endDt is null; Update c Set nId =schma.scal(c.c1, c.c2, c.c3) From @cte c
The @cte had, as I said, from 4 to 15 rows (11 in my most used setId). Putting a return first after, and then before this update is how I concluded that it was the scalar function causing the problem. Basically it was tens of milliseconds, more than a second, another few milliseconds and done.
The query plan from SSMS wasn't useful, and I was thinking about seeing if I could get more details using the plans in the DMVs.
Knowing that TVFs can be used to replace scalars, and that this can improve performance, I gave that a try, and it worked (times in ~100 ms range for the whole query when called inside the original TVF).
I'm still scratching my head as to why it would take longer to call from within the TVF than from outside.
From outside the TVF, I get comparable times using the scalar as I do using the new TFV inside the original TFV.