THE SQL Server Blog Spot on the Web

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

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

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

Scalar.  Function.

Wow.

Could any other combination of words evoke the same feeling of encapsulation, information hiding, and simplification of client code?  After years spent developing software in the procedural and OO worlds, it can be difficult--perhaps, even impossible--to migrate over to working with SQL Server and not consider how to architect your data access logic using some of the same techniques you'd use in the application tier.

In short: Why would you ever write the same piece of logic more than once?  Answer: You wouldn't (damn it!).  And so Microsoft bestowed upon the SQL Server community, in SQL Server 2000, the ability to write scalar user-defined functions.  And they could have been such beautiful things...

But alas, reality can be painful, and as developers tried these new tools they were struck with a strange feeling of sadness as their applications buckled under the weight of what otherwise would have been a wonderful idea. As it turned out, putting all but the simplest of logic into these scalar functions was a recipe for disaster. Why?  Because they're essentially cursors waiting to happen (but they don't look like cursors, so you may not know... until it's too late.)

The central problem is that when you wrap logic in a multistatement UDF, the query optimizer just can't unwrap it too easily. And so there's really only one way to evaluate a scalar UDF: call it once per row. And that is really nothing more than a cursor.

Seeing this behavior in action is easy enough; consider the following scalar function that some poor sap DBA working for AdventureWorks might be compelled to create:

CREATE FUNCTION GetMaxProductQty_Scalar
(
@ProductId INT
)
RETURNS INT
AS
BEGIN
DECLARE @maxQty INT

SELECT @maxQty = MAX(sod.OrderQty)
FROM Sales.SalesOrderDetail sod
WHERE sod.ProductId = @ProductId

RETURN (@maxQty)
END

Simple enough, right?  Let's pretend that AdventureWorks has a bunch of reports, each of which requires maximum quantity sold per product.  So the DBA, thinking he can save himself some time and keep everything centralized (and that is a good idea), puts all of the logic into a scalar UDF.  Now, when he needs this logic, he can just call the UDF.  And if the logic has a bug, or needs to be changed, he can change it in exactly one place.  And so life is great... Or is it?

Let's take a look at a sample query:

SELECT
ProductId,
dbo.GetMaxProductQty_Scalar(ProductId)
FROM Production.Product
ORDER BY ProductId

This query does nothing more than get the max quantity sold for each product in the Productin.Product table. And a look at the execution plan or the STATISTICS IO output might indicate that there's nothing too interesting going on here: The execution plan shows an index scan (to be expected, with no WHERE clause), followed by a compute scalar operation (the call to the UDF). And STATISTICS IO shows a mere 16 reads.

So why is this query so problematic? Because the real issue is hiding just beneath the surface.  The execution plan and STATISTICS IO didn't consider any of the code evaluated within the UDF! To see what's really going on, fire up SQL Server Profiler, turn on the SQL:BatchCompleted event, and make sure you're showing the Reads column. Now run the query again and you'll see that this seemingly-innocent block of T-SQL is, in fact, using 365,247 logical reads. Quite a difference!

Each of those "compute scalar" operations is really a call to the UDF, and each of the calls to the UDF is really a new query.  And all of those queries (all 504 of them -- the number of products in the Product table) add up to massive I/O.  Clearly not a good idea in a production environment.

But luckily, we're not done here yet (or this would be a very boring post). Because while the performance penalty is a major turnoff, I really do love the encapsulation afforded by scalar UDFs.  I want them (or a similar tool) in my toolbox... And so I got to thinking.

The answer to my dilemma, as it turns out, is to not use scalar UDFs at all, but rather to use inline table-valued UDFs and treat them like scalars. This means that queries get slightly more complex than with scalar UDFs, but because the funtions are inlined (treated like macros) they're optimized along with the rest of the query. Which means, no more under-the-cover cursors.

Following is a modified version of the scalar UDF posted above:

CREATE FUNCTION GetMaxProductQty_Inline
(
@ProductId INT
)
RETURNS TABLE
AS
RETURN
(
SELECT MAX(sod.OrderQty) AS maxqty
FROM Sales.SalesOrderDetail sod
WHERE sod.ProductId = @ProductId
)

This function is no longer actually scalar--in fact, it now returns a table. It just so happens that the table has exactly one column and exactly one row, and uses the same logic as the scalar UDF shown above. So it's still scalar enough for my purposes.

The query shown above, used to retrieve the maximum quantity sold for each product, will not quite work with this UDF as-is. Trying to substitute in the new UDF will result in nothing more than a variant on an "object not found" error.  Instead, you need actually treat this function like  it returns a table (due to the fact that it does).  And that means, in this case, a subquery:

SELECT
ProductId,
(
SELECT MaxQty
FROM dbo.GetMaxProductQty_Inline(ProductId)
) MaxQty
FROM Production.Product
ORDER BY ProductId

So there it is. We're now treating the table-valued UDF more or less just like a scalar UDF.  And the difference in I/O results is really quite astounding: 1267 logical reads in this case. Meaning that the scalar UDF solution is around 288 times more I/O intensive!

The question being, of course, was it worth it? The whole thing could have been written as one query, without the need for any UDFs at all. And the final query in this case is quite a bit more complex than the previous version, in addition to the fact that the encapsulation breaks down to some degree by forcing the caller to have some knowledge of how the UDF actually works. But I do feel that this sacrifice is warranted in some cases. Although the "greatest quantity sold" example shown here is simplistic, imagine other situations in which the same code fragments or logic are used over and over, due to lack of a good way of standardizing and centralizing them.  I know I've seen that a lot in my work, and some examples I can think of have included complex logic that might very well have been easier to maintain in a UDF.

This technique may not be perfect for every case, and it certainly has its tradeoffs. But it may be a useful trick to keep in the back of your mind for a rainy day in the data center when someone's scalar UDF solution starts breaking down and you need a fix that doesn't require a massive code rewrite.

Published Friday, August 04, 2006 12:07 AM by Adam Machanic

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

 

Greg_Linwood said:

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
August 8, 2006 1:15 AM
 

glenntoy said:

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.
September 4, 2006 1:53 AM
 

Meher said:

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.

February 9, 2007 1:44 PM
 

Darren said:

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

December 13, 2007 2:02 PM
 

SJC said:

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.

February 1, 2008 11:16 AM
 

Matt Miller said:

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

June 27, 2008 10:30 AM
 

gene said:

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.

June 29, 2008 4:27 PM
 

SimonS Blog on SQL Server Stuff said:

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

November 3, 2008 4:49 PM
 

d1rtyw0rm said:

Thanks alot !

That is really well explains and solve my problem.

May 25, 2009 10:15 AM
 

Tom VdP said:

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.

December 2, 2009 10:53 AM
 

Denise said:

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.

March 1, 2010 4:50 PM
 

RichB said:

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.

May 26, 2010 4:40 AM
 

ali dkj said:

nice post man,

keep up the good work

September 8, 2010 4:48 AM
 

SP said:

Hurrr

September 15, 2010 7:30 AM
 

Dave said:

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.

April 17, 2012 9:05 PM
 

Adam Machanic said:

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).

April 18, 2012 10:11 AM
 

Brad said:

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.

May 23, 2012 11:39 AM
 

Adam Machanic said:

Great news, Brad!

May 23, 2012 1:49 PM
 

Paul Barbin said:

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

June 5, 2012 11:01 AM
 

Leandro said:

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.

June 19, 2012 7:35 AM
 

Christian said:

Today, you are my hero.

January 11, 2013 10:01 AM
 

JRStern said:

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.

January 26, 2013 11:35 AM
 

Asit said:

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

March 5, 2013 3:33 AM
 

Adam Machanic said:

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

--Adam

March 5, 2013 12:30 PM
 

Adrian said:

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

CREATE FUNCTION GetMaxProductQty_Scalar

(

   @ProductId INT

)

RETURNS INT

AS

BEGIN

RETURN (

SELECT TOP 1

MAX(sod.OrderQty)

FROM

Sales.SalesOrderDetail sod

WHERE

sod.ProductId = @ProductId

);

END;

April 5, 2013 11:26 AM
 

Adam Machanic said:

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

April 7, 2013 2:44 PM
 

MarcVB said:

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)

AS

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

April 22, 2013 9:08 AM
 

Adam Machanic said:

@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.

April 22, 2013 10:49 AM
 

Laura said:

@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.

May 21, 2013 3:40 PM
 

Laura said:

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.

May 21, 2013 4:44 PM
 

Xia said:

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

thanks!

June 13, 2013 3:39 AM
 

Vladimir said:

Very interesting post. I'm using MS SQL 2008. I've been researching UDFs for a while trying to optimise my queries and discovered that everybody call UDFs evil. What you and other authors say about UDF is true and helped me to understand how it all works, but still in my particular case use of UDFs made my query run significantly faster.

In short, I have a relatively large table with logs - around 10 million rows. These logs are events that happen at approximately 2000 computers. Essentially, TableLogs has these columns:

ID int not null (primary key, identity)

ComputerID int not null (foreign key to the table of computers)

EventDateTime datetime not null (when it happened)

EventDurationMSec int not null (event duration in milliseconds)

Usually event duration is 10 - 20 seconds, but when things go wrong it is less than 5 seconds. Events normally should happen non-stop. If there are no events for a while for a given computer - there is a problem with the computer.

So, I have a monitoring screen that shows 2000 rows - one row for each computer. For each computer I show the timestamp of the last event that was longer than 5 seconds. Result is ordered by the timestamp desc and at the top of the list are computers that have problems.

I could use this simple query:

select

 ComputerID

 , max(EventDateTime) as LastTimeUp

from

 TableLogs

where

 ComputerID = @ParamComputerID

 EventDurationMSec >= 5000

group by ComputerID

order by LastTimeUp desc;

But, no matter what indexes I created it was still scanning the whole table (or index, but still it was a scan with plenty of reads).

Then I created scalar UDF with one parameter GetLastTimeUp(@ParamComputerID):

select @Result = max(EventDateTime)

from TableLogs

where ComputerID = @ParamComputerID AND EventDurationMSec >= 5000;

I created filtered index

CREATE NONCLUSTERED INDEX [IX_ComputerID_EventDurationMSec] ON [dbo].[TableLogs]

(

[ComputerID] ASC,

[EventDateTime] ASC

)

WHERE ([EventDurationMSec]>=(5000))

;

And changed query to:

select

ComputerID

,  dbo.GetLastTimeUp(ComputerID) as LastTimeUp

from TableComputers

order by LastTimeUp desc;

Each call to UDF now is not a scan of an index, it is an instant seek of a filtered index.

Yes, there are 2000 calls and 2000 seeks, but it is way better than scanning through 10 million of all old events, while I need only the latest event.

I would appreciate it if you could comment this. Thank you.

July 12, 2013 4:11 AM
 

Adam Machanic said:

Hi Vladimir,

There is no reason you can't get that same seek behavior with a join, subquery, or derived table expression. Did you try a FORCESEEK hint? I wonder if your indexing scheme isn't quite in line with the way the query processor works? (do you have, e.g., one filtered index per computer ID or something like that? I guess you can't have 2000 filtered indexes -- what are you filtering on?)

--Adam

July 12, 2013 1:28 PM
 

Adam Machanic said:

Sorry, Vladimir, I should have read your post more carefully. Now I see your filtered index.

I think a FORCESEEK hint would work fine here. You might also try replacing the MAX() with TOP(1) ... ORDER BY EventDateTime DESC. That optimizes slightly differently.

I'm certain that a UDF is not needed here, and I'm certain that you'll get even BETTER performance without using one.

July 12, 2013 1:31 PM
 

Vladimir said:

Hi Adam,

Thank you for reply. I didn't know about FORCESEEK hint - apparently it was added in 2008. I'll check it out.

At first I tried to make queries without the hint. I created test tables and UDF in tempdb and filled them with some data:

---------------------------------------------------------------------------

USE [tempdb]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[TableComputers](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](255) NOT NULL,

CONSTRAINT [PK_TableComputers] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

USE [tempdb]

GO

/****** Object:  Table [dbo].[TableLogs]    Script Date: 07/13/2013 19:26:37 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[TableLogs](

[ID] [int] IDENTITY(1,1) NOT NULL,

[ComputerID] [int] NOT NULL,

[EventDateTime] [datetime] NOT NULL,

[EventDurationMSec] [int] NOT NULL,

CONSTRAINT [PK_TableLogs] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TableLogs]  WITH CHECK ADD  CONSTRAINT [FK_TableLogs_TableComputers] FOREIGN KEY([ComputerID])

REFERENCES [dbo].[TableComputers] ([ID])

GO

ALTER TABLE [dbo].[TableLogs] CHECK CONSTRAINT [FK_TableLogs_TableComputers]

GO

USE [tempdb]

GO

/****** Object:  Index [IX_ComputerID]    Script Date: 07/13/2013 19:28:16 ******/

CREATE NONCLUSTERED INDEX [IX_ComputerID] ON [dbo].[TableLogs]

(

[ComputerID] 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]

GO

USE [tempdb]

GO

/****** Object:  Index [IX_ComputerID_Events]    Script Date: 07/13/2013 19:28:27 ******/

CREATE NONCLUSTERED INDEX [IX_ComputerID_Events] ON [dbo].[TableLogs]

(

[ComputerID] ASC,

[EventDateTime] ASC

)

WHERE ([EventDurationMSec]>=(5000))

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]

GO

-- generate computers

-- 8 rows

INSERT INTO [tempdb].[dbo].[TableComputers]

([Name])

VALUES

('ComputerName')

,('ComputerName')

,('ComputerName')

,('ComputerName')

,('ComputerName')

,('ComputerName')

,('ComputerName')

,('ComputerName')

;

-- generate 2048 rows

DECLARE @VarCount int = 1;

WHILE @VarCount < 9

BEGIN

INSERT INTO [tempdb].[dbo].[TableComputers]

([Name])

SELECT

[Name]

FROM [tempdb].[dbo].[TableComputers]

;

SET @VarCount = @VarCount + 1;

END;

-- generate events

-- 16 rows

INSERT INTO [tempdb].[dbo].[TableLogs]

([ComputerID]

,[EventDateTime]

,[EventDurationMSec])

VALUES

(1,'2000-01-01',2000)

,(1,'2000-01-02',11000)

,(1,'2000-01-03',12000)

,(1,'2000-01-04',13000)

,(1,'2000-01-05',14000)

,(1,'2000-01-06',15000)

,(1,'2000-01-07',16000)

,(1,'2000-01-08',17000)

,(1,'2000-01-09',10000)

,(1,'2000-01-10',11000)

,(1,'2000-01-11',12000)

,(1,'2000-01-12',13000)

,(1,'2000-01-13',14000)

,(1,'2000-01-14',15000)

,(1,'2000-01-15',16000)

,(1,'2000-01-16',17000)

;

-- generate 8192 rows

DECLARE @VarCount int = 1;

WHILE @VarCount < 10

BEGIN

INSERT INTO [tempdb].[dbo].[TableLogs]

([ComputerID]

,[EventDateTime]

,[EventDurationMSec])

SELECT

[ComputerID]

,DATEADD(minute, ROW_NUMBER() OVER(ORDER BY ID), [EventDateTime])

,[EventDurationMSec] + RAND()*1000

FROM [tempdb].[dbo].[TableLogs]

;

SET @VarCount = @VarCount + 1;

END;

-- generate 16769024 rows

INSERT INTO [tempdb].[dbo].[TableLogs]

([ComputerID]

,[EventDateTime]

,[EventDurationMSec])

SELECT

[dbo].[TableComputers].[ID]

,[dbo].[TableLogs].[EventDateTime]

,[dbo].[TableLogs].[EventDurationMSec]

FROM

[tempdb].[dbo].[TableComputers]

CROSS JOIN [tempdb].[dbo].[TableLogs]

WHERE [dbo].[TableComputers].[ID] > 1

;

USE [tempdb]

GO

/****** Object:  UserDefinedFunction [dbo].[GetLastTimeUp]    Script Date: 07/13/2013 19:30:17 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[GetLastTimeUp]

(

-- Add the parameters for the function here

@ParamComputerID int

)

RETURNS datetime

AS

BEGIN

-- Declare the return variable here

DECLARE @Result datetime;

-- Add the T-SQL statements to compute the return value here

SELECT

@Result = MAX(EventDateTime)

FROM    

dbo.TableLogs

WHERE  

(ComputerID = @ParamComputerID)

AND (EventDurationMSec >= 5000)

;

-- Return the result of the function

RETURN @Result;

END

GO

---------------------------------------------------------------------------

After I ran the scripts I rebuilt all indexes on the tables to update statistics and defragment indexes.

And I was comparing the following queries. I used profiler to get the number of reads (SQL:BatchCompleted):

(1)

SELECT

   ComputerID

   , MAX(EventDateTime) as LastTimeUp

FROM

   TableLogs

WHERE

   EventDurationMSec >= 5000

GROUP BY ComputerID

;

GO

(2)

SELECT

   ID

   , dbo.GetLastTimeUp(ID) as LastTimeUp

FROM

   dbo.TableComputers

;

GO

Results (reads from profiler):

(1): 37,724

(2):  6,159

The plan for the first query shows that there is a scan of IX_ComputerID_Events.

The plan for UDF shows that there is a seek on IX_ComputerID_Events.

Index IX_ComputerID_Events has 37139 pages and all of them were read in (1) + some overhead.

Each call of UDF requires 3 reads, so 3*2048 = 6144 + some overhead.

Strictly speaking, I wasn't comparing equivalent queries.

The first query reads only from big TableLogs, which has 16 million rows. Result set is a list of all ComputerIDs that have events. As far as I understand, the only way to get all ComputerIDs is to scan the index. It has to read all index pages.

In the second query I use the TableComputers, which has only 2048 rows.

It is a kind of cheating (or optimisation), because there is no need to build a list of ComputerIDs, they are already in the small table. Also, results are different. In the second case I get a row for each ComputerID, even if it doesn't have events (which is what I want for real data).

So, equivalent without UDF would be something like this:

(3)

SELECT

   TableComputers.ID

   , (

   SELECT

       MAX(EventDateTime)

   FROM

       TableLogs

   WHERE

       EventDurationMSec >= 5000

       AND TableLogs.ComputerID = TableComputers.ID

   ) as LastTimeUp

FROM TableComputers

;

GO

Profiler results: 37,764

How would you rewrite the query (3) to get performance (reads) comparable to UDFs (2)?

I don't know how to make use of TOP(1) instead of MAX in this case (3). I can understand how to do it in UDF, but not in subquery.

When I tried the FORCESEEK hint:

(4)

SELECT

   TableComputers.ID

   , (

   SELECT

       MAX(EventDateTime)

   FROM

       TableLogs WITH (FORCESEEK)

   WHERE

       EventDurationMSec >= 5000

       AND TableLogs.ComputerID = TableComputers.ID

   ) as LastTimeUp

FROM TableComputers

;

GO

The plan didn't use index IX_ComputerID_Events at all. It did seek on IX_ComputerID and lookup with predicate EventDurationMSec >= 5000. Obviously it is terrible.

Profiler results: 53,508,543 reads

Thank you for the comments.

I would really love to find a solution better than UDF.

July 13, 2013 6:09 AM
 

Vladimir said:

Hi Adam,

I tried a hint to force use the IX_ComputerID_Events together with FORCESEEK, but still it was far from UDF and worse than simple (3).

Then I thought about your suggestion to use TOP() instead of MAX. At first I didn't realise that it is possible to use it in subquery. So, I changed (3) into:

(5)

SELECT

TableComputers.ID

, (

SELECT TOP(1)

EventDateTime

FROM

TableLogs

WHERE

EventDurationMSec >= 5000

AND TableLogs.ComputerID = TableComputers.ID

ORDER BY EventDateTime DESC

) as LastTimeUp

FROM TableComputers

;

And this query has the plan that I was looking for - it is a Nested loop left join between TableComputers and seek&top of IX_ComputerID_Events index. Actual plan shows that actual number of rows and number of executions of Seek is 2048 - one for each computer ID.

Profiler revealed that number of reads is much better than (3) with MAX:

(3) 37,762

(5)  6,797

Variant (2) with UDF has 6,157 reads. Still slightly better.

I can only guess why.

With UDF (2) there is scan of TableComputers (11 pages), then 2048 calls of UDF, 3 reads each:

2048*3+11 = 6155 (almost 6157 that profiler tells me)

With (5) there are 2048 seeks, 3 reads each = 6144, so 6797-6144 = 653 reads must be these nested loops that join TableComputers and results of index seek. With UDF there is no join.

Thank you for your suggestions and comments. You really helped me to better understand internals of SQL server.

July 13, 2013 10:14 PM
 

Adam Machanic said:

Hi Vladimir,

I'm glad the suggestion worked for you! Sounds like you're on the right track.

Two comments:

 A) Don't believe everything Profiler tells you about the UDF reads. I can't recall the specifics offhand but I've seen mis-reporting issues in the past. Google around and you'll find numerous threads on the topic on various forums.

 B) Don't forget to consider CPU time as well.

--Adam

July 14, 2013 11:18 AM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement