T-SQL User-Defined Functions: the good, the bad, and the ugly (part 3)

re: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 3)

Adam Machanic — Fri, 29 Jun 2012 20:10:56 GMT

Nice post, Hugo!

re: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 3)

Jānis — Tue, 03 Jul 2012 08:38:55 GMT

T-SQL function is not determined as it don't have "With Schemabinding"..

re: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 3)

Alejandro Mesa — Mon, 09 Jul 2012 15:53:13 GMT

Nice series, Hugo!

Would you mind testing it one more time, but this time making the UDF determistic by adding the function option SCHEMABINDING, as Jānis suugested?

May be that is the cause for the marginal difference between both elapsed times.

Improving query plans with the SCHEMABINDING option on T-SQL UDFs

http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/596424.aspx

re: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 3)

Hugo Kornelis — Mon, 09 Jul 2012 22:12:49 GMT

Jānis and Alejandro make a great point. I had indeed overlooked the missing schemabinding, that makes the dbo.Distance function non-deterministic. To check the effect, I created another function, with the same body but "WITH SCHEMABINDING" added. Then I tested both versions repeatedly.

The first thing that struck me was that the results appear to fluctuate. Many tests (for either of the functions) took about 15 seconds, but every so often, suddenly a few test runs would jump to around 30 seconds. That would then last for two or three tests before falling back to the normal 15-second time.

So I ran a lot of iterations of the two tests. 56, to be precise. Then, I tallied all results. For the original (not schembound, not deterministic) version, the average CPU time was 16.83 seconds, and the average elapsed time was 18.36 second. For the schemabound and deterministic alternative, those numbers were 16.89 and 18.39, so the schemabound version appears to be a tad slower. However, this difference is so small that I think it is just statistical error.

For the record, the minimum CPU and elapsed time for the functions were 12.78, 14.06, 12.64 and 13.99; and the maximum values were 31.79, 34.84, 32.93 and 35.89. Just to indicate how wide apart some observation were.

re: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 3)

Paul White — Thu, 30 Aug 2012 01:39:28 GMT

Hi Hugo,

Regarding the sentence, "The only explanation I can come up with is that invoking a T-SQL involves a lot more overhead than invoking a CLR function.". See the Books Online :Performance of CLR Integration" topic (http://msdn.microsoft.com/en-us/library/ms131075.aspx).

There's some useful information there, including the statement, "CLR functions benefit from a quicker invocation path than that of Transact-SQL user-defined functions.".

SQLCLR scalar functions without data access are indeed highly preferable to T-SQL scalar functions, and can often perform better than native T-SQL code (without function calls) for surprisingly simple activities. One recent example that springs to mind was implementing DATEFROMPARTS in CLR versus a bunch of nested T-SQL functions.

I do think your summary statement, "As a rule of thumb, those [CLR scalar functions] are bad for performance too" is over-stated. Avoiding a function altogether (or using an in-line TVF) will often out-perform a 'pointless' CLR scalar function, but failing to apply common sense should not really result in something as strong as a "rule of thumb", should it?

Paul

re: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 3)

Robert Folkerts — Mon, 24 Sep 2012 20:59:30 GMT

What on Earth does, "lying until it is black in its face" mean? If I lie a great deal, I may turn red in the face from a lack of breathing or embarrassment, but how do you lie until you are black in the face?