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

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

Hugo Kornelis — Sun, 20 May 2012 20:21:18 GMT

I guess I should add (thank you for pointing it out, Adam) that all of the above applies to user-defined function that are created in T-SQL. You can also create user-defined functions with .Net languages (CLR). I have not done much research into them yet, I do know that they usually perform better than their T-SQL counterparts.

I may do some investigation into CLR user-defined functions in the future, and if I do I'll write my findings in a blog post.

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

Randall — Mon, 21 May 2012 12:19:45 GMT

Why hasn't Microsoft done anything to fix the problems with scalar T-SQL UDFs? They've had the opportunity in SQL Server 2008 and 2012. Why don't they care about SQL Server performance?

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

Hugo Kornelis — Mon, 21 May 2012 12:51:40 GMT

@Randall: I would not go as far as to say that Microsoft "don't care about SQL Server performance". There have been many changes to help performance, just not in the area of scalar T-SQL UDFs.

I can't tell you why MS has chosen not to invest in the area of scalar T-SQL UDF performance. Only they can tell you. I can give you my best guess: they had more things they wanted to do than they had developer hours available, so they had to prioritize, and other issues were considered more important.

Personally, I'd *love* to see some work done here. Just adding a special syntax for an inline scalar UDF (similar to the inline table-valued UDF) would be a great addition. There is a very popular connect item for this (281 up votes; 2 down votes) at https://connect.microsoft.com/SQLServer/feedback/details/273443/the-scalar-expression-function-would-speed-performance-while-keeping-the-benefits-of-functions - if you haven't voted yet, do so now!

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

Geoff — Mon, 21 May 2012 18:31:49 GMT

"Why hasn't Microsoft done anything to fix the problems with scalar T-SQL UDFs? They've had the opportunity in SQL Server 2008 and 2012. Why don't they care about SQL Server performance?"

Agreed.

There is no reason that this still has to be a problem. At the very least, SQL Server could inline the code when it is compiled or give you the option to have that happen.

I can't fathom why this hasn't been fixed. We've recently in-lined a bunch of functions to improve performance in a number of our procedures, and it would be much nicer to have the logic contained in one place.

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

Hugo Kornelis — Mon, 21 May 2012 21:45:28 GMT

@Geoff: "We've recently in-lined a bunch of functions to improve performance in a number of our procedures, and it would be much nicer to have the logic contained in one place."

There is a way to do that. I plan to cover it in a future blog post, but if you're impatient you can also read about it here:

http://sqlblog.com/blogs/adam_machanic/archive/2006/08/04/scalar-functions-inlining-and-performance-an-entertaining-title-for-a-boring-post.aspx

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

GrumpyOldDBA — Tue, 22 May 2012 11:29:17 GMT

We've really known the issues with T-SQL UDFs since SQL 2000. It's a key point of much performance tuning to just remove UDFs. Typically queries which ran for hours now run in secs and with reduced io usually.

I did try a bunch of ( simple ) CLR functions to replace T-SQL functions but couldn't reproduce in test the issues I had in production so the results were inconclusive, some time I will have to revisit.

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

Geoff — Tue, 22 May 2012 14:34:03 GMT

Hugo, thanks.

We can't use an inline TVF because we are still stuck on 2000 compatibility mode. We've made the changes to get us off of compatibility mode, but they have to be tested.

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

Ted — Fri, 25 May 2012 16:00:19 GMT

SQL Server 2012 should make this easier with a DETERMINISTIC keyword for the function like Oracle has had for years.

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

Hugo Kornelis — Fri, 25 May 2012 18:13:40 GMT

Ted, SQL Server uses WITH SCHEMABINDING to mark user-defined functions as deterministic, so no need for a DETERMINISTIC keyword.

The difference between SQL Server, apart from the term used, is that SQL Server can't force determinism on a function that is actually not deterministic. If what I just found on internet is correct, Oracle doesn't check; if you declare a non-deterministic function to be deterministic, you'll simply get unexpected results.

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

Hugo Kornelis — Fri, 25 May 2012 18:21:52 GMT

NOTE:

I just found that I made a mistake with the rowcount in the table used for the demo. I corrected this by editing the text in blog post - everywhere where I first had 10,000 has been changed to 100,000; and all occurences of a million have been changed to read ten million. The actual code has not been changed; the text now accurately describes the actual number mof rows in the table,

My apologies for this stupid mistake.

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

TheSQLGuru — Sat, 26 May 2012 14:23:03 GMT

>>>>>>>>>>>>>

There is no reason that this still has to be a problem. At the very least, SQL Server could inline the code when it is compiled or give you the option to have that happen.

>>>>>>>>>>>>>

VERY good reason:javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$_$ctl00$_$ctl00$ctl00$bcr$ctl00$_$form$_$btnSubmit", "", true, "", "", false, true)) It is really, REALLY f'in HARD to get the engine to know about, understand, and integrate the virtually infinite array of code that could go into any UDF into the optimizer and engine of SQL Server. Since there are known workarounds (often several) and SOOOOO many other things that need to be fixed/improved in the engine, integrating UDFs has and never will make the cut IMNSOH. ESPECIALLY with Steve Balmer's "the cloud - we're ALL IN" mantra redirecting hundreds of thousands of developer hours to windows/sql azure. :(

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

Hugo Kornelis — Sat, 26 May 2012 14:45:02 GMT

Kevin, I totally agree with your remark if you mean that it would cost too many developer hours to have the optimizer automatically inline all scalar user-defined functions. However, if you think about extending the syntax to allow a special kind of inline functions that allows only a single expression (similar to how inline table-valued funcions allow only a single query) and inlining them automatically, that would cost far less developer hours, place the burden of rewriting functions to a single expression on us, but give us the possiblity to encapsulate logic without takinng a performance hit.

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

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' — Sat, 26 May 2012 15:00:44 GMT

In a previous blog post , I demonstrated just how much you can hurt your performance by encapsulating

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

TheSQLGuru — Sat, 26 May 2012 16:07:29 GMT

Still disagree. I can write a thousand-line-long SELECT that returns a single scalar value. Absolutely no way that can be effectively integrated. You are also ignoring the optimization shortcoming entirely - as in how would an estimate of number of rows or value distribution possibly be extracted from arbitrary code, especially when such output is dependent on the per-row input from the outer calling query?

Perhaps you are speaking of a non-data-hitting scalar, something like RETURN int AS (@input + 3)? Again I can make an arbitrarily complex statement that could take extraordinary efforts to get accurate statistics sucked out of it.

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

RBarryYoung — Sat, 26 May 2012 18:24:37 GMT

The strange thing is that Microsoft has had an object-type for inline scalar functions since SQL Server 2005, and they even document how they work in a query in every version of the doc since then. They just never included the syntax to implement one. Weird.

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

Adam Machanic — Sat, 26 May 2012 18:46:29 GMT

Kevin, I'm not sure what data access has to do with anything. The query optimizer generally has no problem inlining even large/complex correlated scalar subqueries or table expressions. I write large table expressions -- even scalar-valued -- all the time that the QO has absolutely no issue producing accurate statistics for.

And in cases where expressions can't be inlined, they're still much faster when evaluated via a nested subtree rather than in a separate context, as occurs with scalar UDFs.

... you can read my prior blog post on the topic, linked above in Hugo's response to Geoff, for more information.

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

Hugo Kornelis — Sat, 26 May 2012 20:31:21 GMT

@RBarry: Interesting, I didn't know that. Can you create such objects through the object model, or can you only read them?

Kevin: It's not about the number of lines, or the number of values returned - it's about what goes between the BEGIN and END of the function definition. For the inline scalar function I would like to see added to the product, that would only be one statement: RETURN, followed by an expression that evaluates to a single scalar value. And that expression can be data-hitting (by including subqueries), or not.

Yes, you can make it complicated, and that may be hard to optimize. How does that differ from manually copying and pasting that same complicated expression in the query? I don't expect a query that uses an inline scalar function to perform better than one where I inline the expression myself, but it would be great if they would perform the same. That would be a huge step forward. And it is possible, since all it takes is for the parser to replace the reference to the function with its definition, as a sort of macro expansion. Exactly how currently views and inline table-valued function are already treated.

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

Frantz — Sat, 26 May 2012 22:40:57 GMT

Nice article.

A fast way to optimize code that makes use of a scalar function in a situation where the logic is too complex to be re-written is to create a table with all the values and join it.

It is not elegant and I don't know if it performs well under most situations but it helped us cut the execution time tremendously many times. Something like:

SELECT

D.DataVal,

T.MaxTriple

FROM (

SELECT

DataVal,

'MaxTriple' = dbo.Triple(DataVal)

FROM (

SELECT DISTINCT DataVal

FROM

dbo.LargeTable) T) T

INNER JOIN

dbo.LargeTable D

ON D.DataVal = T.DataVal

Frantz

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

TheSQLGuru — Sun, 27 May 2012 20:00:50 GMT

Here are 2 exceptionally simple UDFs, one scalar and one TVF. Both result in horrible estimates and suboptimal executions, including memory grants 2 and 3 times larger. I am sure I am missing something Adam, but am not sure what it is.

USE AdventureWorks2008R2

SET NOCOUNT ON

--useful index

CREATE NONCLUSTERED INDEX [idx_fullname] ON [Person].[Person]

(

[LastName] ASC,

[FirstName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,

DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE FUNCTION dbo.fn_FullName (@FirstName nvarchar(50), @MiddleName nvarchar(50), @LastName nvarchar(50))

RETURNS nvarchar(153) WITH SCHEMABINDING

BEGIN

RETURN @LastName + N', ' + @FirstName + N' ' + ISNULL(@MiddleName, N'')

END

--show actual execution plan and not estimated/actual differences and the query shape/operations

SELECT dbo.fn_FullName(p.FirstName, p.MiddleName, p.LastName) as FullName,

od.ProductID,

SUM((UnitPrice - UnitPriceDiscount) * OrderQty) AS TotalPrice

FROM Sales.SalesOrderHeader oh

INNER JOIN Sales.SalesOrderDetail od ON od.SalesOrderID = oh.SalesOrderID

INNER JOIN Sales.SalesPerson sp ON sp.BusinessEntityID = oh.SalesPersonID

INNER JOIN Person.Person p ON p.BusinessEntityID = sp.BusinessEntityID

WHERE dbo.fn_FullName(p.FirstName, p.MiddleName, p.LastName) = N'Abbas, Syed E'

GROUP BY p.LastName, p.FirstName, p.MiddleName, od.ProductID

.13 duration, .15 cpu, 1231 reads, 2192 memory grant

significant disparity between estimated and actual rowcounts

SELECT p.LastName + N', ' + p.FirstName + N' ' + ISNULL(p.MiddleName, '') as FullName,

od.ProductID,

SUM((UnitPrice - UnitPriceDiscount) * OrderQty) AS TotalPrice

FROM Sales.SalesOrderHeader oh

INNER JOIN Sales.SalesOrderDetail od ON od.SalesOrderID = oh.SalesOrderID

INNER JOIN Sales.SalesPerson sp ON sp.BusinessEntityID = oh.SalesPersonID

INNER JOIN Person.Person p ON p.BusinessEntityID = sp.BusinessEntityID

WHERE p.LastName = N'Abbas'

AND p.FirstName = N'Syed'

AND p.MiddleName = N'E'

GROUP BY p.LastName, p.FirstName, p.MiddleName, od.ProductID

0 duration, 0 cpu, 63 reads, 1024 (default sized?) memory grant

CREATE FUNCTION dbo.tvf_FullName (@FirstName nvarchar(50), @MiddleName nvarchar(50), @LastName nvarchar(50))

RETURNS TABLE WITH SCHEMABINDING

RETURN (SELECT @LastName + N', ' + @FirstName + N' ' + ISNULL(@MiddleName, N'') AS FullName)

SELECT tvp.FullName,

od.ProductID,

SUM((UnitPrice - UnitPriceDiscount) * OrderQty) AS TotalPrice

FROM Sales.SalesOrderHeader oh

INNER JOIN Sales.SalesOrderDetail od ON od.SalesOrderID = oh.SalesOrderID

INNER JOIN Sales.SalesPerson sp ON sp.BusinessEntityID = oh.SalesPersonID

INNER JOIN Person.Person p ON p.BusinessEntityID = sp.BusinessEntityID

CROSS APPLY dbo.tvf_FullName (p.FirstName, p.MiddleName, p.LastName) tvp

WHERE tvp.FullName = N'Abbas, Syed E'

GROUP BY tvp.FullName, od.ProductID

.13 duration, .15 cpu, 1231 reads, 3712 memory grant

significant disparity between estimated and actual rowcounts

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

Dave Maiden — Mon, 28 May 2012 02:03:42 GMT

I personally think this started when Microsoft decided to create the .NET Framework and go OOP, I appreciate SQL as a language but have since moved onto VB.NET/C#, but still actively use SQL. When you compile your code within the Framework the COMPILER is designed to calibrate your code (and it varies between each language on the the result).

I assume (incorrectly) that the in-built SQL functions are written within the Framework, which would make more sense to me as a change in one function would not have a devastating affect. First problem LEGACY if there is a problem then they either change it or leave it, the latter being the problem for compatibility. I do however understand that legacy systems are in use and use some of the depreciated commands.

You should test CLRs, I use them all the time and in these days of Asynchronous results surely we should have a essential bible (framework) which everything relies on and is standardised throughout. For example DATEDIFF("D") is different to Microsoft.VisualBasic.DateDiff(DateInterval.Day) as it uses Timespan.

Dave

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

Mr Tea — Fri, 17 Aug 2012 11:07:32 GMT

If I use timestamps e.g. SYSUTCDATETIME() instead of statistics time then I see a different picture. Its still bad in comparison but using STATISTICS TIME has a direct and fundamental effect on the observation.

My Figures are:

Using SYSUTCDATETIME with LargeTable

multiplication: ~24 ms

dbo.triple: ~250ms ms

Using STATISTICS TIME with LargeTable

multiplication: ~24 ms

dbo.triple: ~450ms ms

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

Madhukar Sreeramoju — Mon, 17 Dec 2012 05:59:05 GMT

How to know the DB - Memory Consumption in Sql Server using the query.

madhukars@live.com

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

Michael — Mon, 07 Jan 2013 17:49:20 GMT

let A,B,x in R such that x>0 if A>B then xA>xB....so you should select 3*max(DataVal) instead of select max(3*DataVal)