THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

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

This blog has moved! You can find this content at the following new location:

https://SQLServerFast.com/blog/hugo/2012/06/t-sql-user-defined-functions-the-good-the-bad-and-the-ugly-part-3/

Published Friday, June 29, 2012 9:44 PM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

a.m. said:

Nice post, Hugo!

June 29, 2012 4:10 PM
 

Jānis said:

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

July 3, 2012 4:38 AM
 

Alejandro Mesa said:

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

July 9, 2012 11:53 AM
 

Hugo Kornelis said:

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.

July 9, 2012 6:12 PM
 

Paul White said:

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

August 29, 2012 9:39 PM
 

Robert Folkerts said:

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?

September 24, 2012 4:59 PM
 

greg elliott said:

you need to add "with schemabinding" to your function definitions.  otherwise there is a huge overhead in each call, as SQL checks to see if the function definition has changed.

It has been our experience that this change removes the overhead.

January 23, 2015 11:57 AM
 

greg elliott said:

so the schemabound version appears to be a tad slower.

===============

we have not seen this in our testing.  quite the opposite, with dramatic improvements after adding with schemabinidng.

January 23, 2015 12:00 PM

Leave a Comment

(required) 
(required) 
Submit

About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement