Which One Would You Choose
This is a capture of running 2 TVFs that return the same data.
Basically I rewrote a multi-statement TVF as an inline TVF.
The first statement is the ITVF and the second is the original MSTVF.
The question I have is do I choose the one with less reads, or the one with the lest Est Cost% and less Duration and CPU time?
Thanks 🙂
In general, I would choose to write a function inline wherever possible. There would need to be a very good reason to write it as a multi-statement function, rather than the other way around. I have seen such examples, but only rarely.
Never rely on estimated cost percentage alone, especially with things that aren't really costed at all, like multi-statement TVFs. The number of reads can be helpful, but I tend to give much more weight to elapsed time and worker time in most cases. Care is needed when measuring things that include a nested execution scope, like scalar and multi-statement functions.
Ignoring the difference between the 2, both return the same data, which one would you pick?
The elapsed times are only a couple thousandth of a seconds different. I would assume with this in mind, the reads would be the deciding for you then?
Thanks.