Scalar functions, inlining, and performance: An entertaining title for a boring post

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Greg_Linwood — Tue, 08 Aug 2006 05:15:28 GMT

Hey Adam - nicely written post

Another issue that complicates measuring IO from UDFs is that the Profiler Reads output contains Proc Cache lookups - which are also 8K IOs, whereas statistics io doesn't. In SQL 2000 UDFs, the Proc Cache was looked up per-invocation of the UDF (per row), potentially skewing the values returned by Reads depending on the size of the cache. I've seen scenarios where the same statement (involving a UDF) recorded signficantly variant Reads during peak processing periods (when the cache is bloated) vs during average processing periods (when the cache has reduced to a smaller size).

In SQL 2005, this has been sensibly scaled back so that the proc cache is only looked up for the first invocation in a rowset, improving UDF performance somewhat by eliminating the repetitive cache lookups performed in SQL 2000..

Cheers
Greg

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

glenntoy — Mon, 04 Sep 2006 05:53:30 GMT

Nice one.

When I started with SQL 2000 I was fascinated with its UDF feature, I used to do this way but my boss told to change it due to performance reason. As much as possible I do avoid performing a query inside the function, but in cases where it can't be avoided the best way is to optimized the query inside the function.

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Meher — Fri, 09 Feb 2007 18:44:15 GMT

Great Post Adam. Fascinating details of UDF. I always wondered how a Scalar UDF be converted to a Inline table valued UDF and this post actually shows that.

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Darren — Thu, 13 Dec 2007 19:02:32 GMT

great article, confirming what I've been seeing...really a surprise to me. Good suggestions for handling as table functions...will try that.

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

SJC — Fri, 01 Feb 2008 16:16:20 GMT

Thanks for this post Adam; I'm experiencing this problem at the moment and the article gives me great pointers on how to solve it to everyone's satisfaction.

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Matt Miller — Fri, 27 Jun 2008 14:30:11 GMT

Adam -

Since you're using a TVF, you could "improve readability" with a CROSS APPLY statement. Since it's a single value, the perf penalties that come in from CROSS APPLY don't seem to pop up...

So it would look like

SELECT ProductId,

MaxQty

FROM Production.Product

CROSS APPLY dbo.GetMaxProductQty_Inline(ProductId) MaxQT

ORDER BY ProductId

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

gene — Sun, 29 Jun 2008 20:27:39 GMT

Adam, this is a great post. I only want to add one practical detail to your post which support it.

Most of the time ( or in my experience of 15yr - all the time) programmers are not trained and do not care about performance tunning. However, application usually should be maintained for 3-5 years of it's life span.

This is usually overlooked. Finally, dba - person who did not develop the application should start tunning it and here is a big problem having UDF's and views.

When you say that UDF may bring benefits, did you take in count the fact that code which uses UDF's and views is almoust impossible to analize for performance improvements?

Try to traverse a code which use UDF which goes aganist view, which uses another 2 views or so!

This is a real price for 'sleek' UDF use. That's the real difference between experienced seasoned programmer who had met and learned this by his own experience and talanted 2-3 yr. programmer who measures his work by complexity of his queries and use of funcy new features like UDF.

I bet real good code is measured by it's ability to be troubleshouted easily too. And UDF's and views are sure means not to achieve that.

TSQL Scalar functions are evil.

SimonS Blog on SQL Server Stuff — Mon, 03 Nov 2008 21:49:38 GMT

I’ve been working with a number of clients recently who all have suffered at the hands of TSQL Scalar

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

d1rtyw0rm — Mon, 25 May 2009 14:15:16 GMT

Thanks alot !

That is really well explains and solve my problem.

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Tom VdP — Wed, 02 Dec 2009 15:53:26 GMT

Thank you! I have succesfully used this optimalisation to speed up our ETL process. We were doing quite a lot of date calculations via functions: having a date format yyyymmdd but needing date calculations like "substract 6 hours" made scalar UDFs a logical choice. It is now implemented using 'inline' table-valued functions. Speeds up the processing!

Thanks again, excellent post.

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Denise — Mon, 01 Mar 2010 21:50:06 GMT

We just went thru this issue and when I went to do the research for a possible fis, I found your post.

Invaluable!

Thanks very much.

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

RichB — Wed, 26 May 2010 08:40:50 GMT

I'm on a project migrating from Oracle to SQL Server. It turns out a lot of our reports call scalar UDFs for most of the projected columns. In Oracle, a typical query takes seconds. In SQL Server, 30 minutes.

Clearly, it's not that SQL Server can't solve the perf problem they have with scalar UDFs, but that they haven't prioritized it over other features.

So now I've found your great article (and a number of other similar articles on Cross Apply), that's what we will have to do.

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

ali dkj — Wed, 08 Sep 2010 08:48:05 GMT

nice post man,

keep up the good work

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

SP — Wed, 15 Sep 2010 11:30:28 GMT

Hurrr

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Dave — Wed, 18 Apr 2012 01:05:26 GMT

Great post. I would like to say though that Scalar functions can still be very useful, AS LONG AS you aren't using them in an SQL expression with loads of rows.

For example I have some scalar functions such as fn_Daysearch, which returns a date for a day previous or future, like last Wednesday or next Monday. I typically only call these functions once at the start of a stored proc, usually passing in a proc parameter and using the return value in the WHERE clause of my main query. As a rule if it's only getting called once or a few times, then I'll go scalar.

Also Matts CROSS APPLY above is a perfectly good option, although I'd prefer using MAX(sod.OrderQty) OVER (PARTITION BY sod.ProductId) myself.

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Adam Machanic — Wed, 18 Apr 2012 14:11:37 GMT

Dave, agreed. But make sure that you assign the results of your scalar UDF to a variable, and then use that in the WHERE clause. Otherwise, you A) don't know that it will only be called once, and B) may encounter some other issues (T-SQL scalar UDFs will inhibit parallelism).

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Brad — Wed, 23 May 2012 15:39:38 GMT

Great information!. I used this to streamline our ETL process. I am now saving an average of 10 hours process time per day. Unbelievable. Thanks for the post.

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Adam Machanic — Wed, 23 May 2012 17:49:48 GMT

Great news, Brad!

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Paul Barbin — Tue, 05 Jun 2012 15:01:56 GMT

Adam, it's amazing that you wrote this over 6 years ago and it's still relevant (amazingly good for you or bad for MS?).

We recently had an issue with a scalar UDF. Being aware that scalar functions could cause problems we were careful to test before rolling to production. Total run time on test machines was 5 minutes. Production was a faster box; we expected faster results. Wrong. Production ran for 45 minutes before we had to kill it.

Subsequent "tests" in production showed nearly 2000 reads in production for a single run of the UDF and 3 in test (not kidding). Same configuration, same settings, same db, same everything. Almost.

In production we had two server traces running that were capturing statement-level events. While both traces were filtering out most events, the server still collected them before discarding.

We stopped the traces, ran the UDF and it now shows 3 reads in production now...

This URL showed us the light:

http://sqlblog.com/blogs/linchi_shea/archive/2009/06/15/performance-impact-sql-trace-and-user-defined-scalar-functions.aspx

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Leandro — Tue, 19 Jun 2012 11:35:39 GMT

What I noticed is that an application that we develop, when using UDFs, increases a lot the RowCounts compared to when it's not using. I modified a query to check it and the query had to return 1250 rows, the RowCount using UDF was 5022 and, without it, the RowCount dropped to 1255. The reads count didn't differ so much between using and not using UDF, using it it was about 30 more reads than without it.

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Christian — Fri, 11 Jan 2013 15:01:12 GMT

Today, you are my hero.

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

JRStern — Sat, 26 Jan 2013 16:35:21 GMT

Had an interesting experience yesterday looking at the index usage across a new-ish database. The major tables showed usage of about 3,000,000. But a couple of small control tables showed usage of about 240,000,000. For a modest little app, 240m is a big number. So, what is going on?

Well, the index usage stats basically count plans that touch the index, not the number of rows touched. So, 240m plans??? Well yes - when you use a UDF in the where clause! Each invocation is a *plan*, not just a statement, not just a fncall.

So it's MUCH worse than a cursor.

And I will soon be a hero at work when we eliminate those UDFs, which are really there more to be pretty than because of need. And they are pretty, dammit. Has anyone at Microsoft ever talked about changing their scalar UDF implementation to be more like the TVFs, in that the optimizer can look inside? Microsoft basically botched the whole UDF project in SQL 2000, they got confused about stochastic and non-stochastic and never did figure out what they were really doing, ... at least that's my reading on it.

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Asit — Tue, 05 Mar 2013 08:33:15 GMT

Adam, i've been following you post for quite a long and find them of great help.

But in my production environment we have a great UDF usage(which are multi statement table values function). They have great bussiness logic in them but they are IO HOGS. To improve in IO i mostly removed the code into the calling procedure or created an inline table function. So i suggest the developer that avoid using multi statement table valued function as much as possible.

Your commetnts on that and reason of them hogging the IO

Regards

Asit

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Adam Machanic — Tue, 05 Mar 2013 17:30:01 GMT

@Asit: Multistatement UDFs use involve temp tables, so all of that I/O is almost certainly due to tempdb activity.

--Adam

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Adrian — Fri, 05 Apr 2013 15:26:33 GMT

Rewrite the original scalar UDF to the code below. It will make a difference.

CREATE FUNCTION GetMaxProductQty_Scalar

(

@ProductId INT

)

RETURNS INT

BEGIN

RETURN (

SELECT TOP 1

MAX(sod.OrderQty)

FROM

Sales.SalesOrderDetail sod

WHERE

sod.ProductId = @ProductId

);

END;

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Adam Machanic — Sun, 07 Apr 2013 18:44:37 GMT

Adrian:

Unfortunately doing that does not, in fact, make any difference whatsoever. The query plans will still be identical, performance implications will still be identical, and the query optimizer will still be unable to make use of various features, including parallelism.

--Adam

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

MarcVB — Mon, 22 Apr 2013 13:08:08 GMT

Very interesting and to the point, but not complete for my case. So, is there someone who can help me to higher the performance when you have to deal with a function that aggregates the results of a select in 1 line. Eg

CREATE FUNCTION [dbo].[Get_PaymentLines]

(@client nvarchar(255)

,@language nvarchar(255)

)

RETURNS nvarchar(500)

BEGIN

Declare

@dummy nvarchar(500),

@Line nvarchar(255)

SET @Dummy = ''

DECLARE Payment_cursor CURSOR

FOR SELECT convert(nvarchar(2), line) + ' ' +

convert(nvarchar(10), convert(money,amount))

FROM payments p

WHERE (p.client = @client and p.language = @language)

OPEN Payment_Cursor

FETCH NEXT FROM Payment_Cursor INTO @Line

WHILE @@FETCH_STATUS = 0

BEGIN

IF @dummy = '' SET @dummy = @Line ELSE SET @dummy = @dummy + ' ' + @Line

FETCH NEXT FROM Payment_Cursor INTO @Line

END

CLOSE Payment_Cursor

DEALLOCATE Payment_Cursor

RETURN @Dummy

END

Because applying this kind of functions for a couple of hundred lines is very slow on SQL server.

thanks

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Adam Machanic — Mon, 22 Apr 2013 14:49:25 GMT

@MarcVB:

The "aggregation" in your case is string concatenation. And although SQL Server does not include a string concat aggregate, we can fake it very easily by creating an XML document that lacks tags:

SELECT

x + ' ' AS [text()]

FROM

(

SELECT 'a'

UNION ALL

SELECT 'b'

) AS y (x)

FOR XML PATH('')

In conjunction with STUFF and a few other tricks you can better control the output. Google around for more information.

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Laura — Tue, 21 May 2013 19:40:27 GMT

@Adam:

I was able to improve the performance of simply selecting the value returned from my scalar udf by changing it to a table udf. But what if I need to then join to another table based on the value returned from my udf? This appears to still cause a major performance hit if I try to do more than simply select the value from the udf, and try to actually use it in my table join criteria. Does your work around only help with selecting the data and if I need to use it in the table join criteria I will still face the performance woe's of udf's?

Thanks for your ariticle. Very helpful.

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Laura — Tue, 21 May 2013 20:44:53 GMT

Adam,

Correction, I am seeing a performance improvement even when using the udf table function in my join criteria. I just realized the same query went from 3:41 to 1:31 by changing the function from scalar to table. Didn't realize this earlier since it it still taking over a minute and had not actually timed it yet.

re: Scalar functions, inlining, and performance: An entertaining title for a boring post

Xia — Thu, 13 Jun 2013 07:39:54 GMT

It's really appreciable article.it improved the query performance

thanks!