Which One Would You Choose

turnoffafterlife 2016-03-12 21:47:48

Noob Question. I'm new to this execution plan stuff.

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 🙂

SQLkiwi 2016-03-13 03:41:56
There's not much to choose between the two in this specific case (with low row counts).

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.

turnoffafterlife 2016-03-14 13:17:37
Statement #1 (with lowest reads) is an inline version of statement #2. I can look into decreasing the reads even further, but the question is directed at these 2 statements.

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?


SQLkiwi 2016-03-16 02:59:08
As I said in the answer, there would need to be a very good reason to choose a ms-TVF. There is no such reason in this case, so I would choose the in-line version.