August 5, 2012 at 3:51 pm
David Rueter (8/1/2012)
Another great article, Jeff. Thanks! I had no idea that an inline table-valued function could be so much faster than an scalar UDF.I confirmed the "times two" results on my machine, measuring elapsed time only:
1 Million rows:
No function213 ms
Scalar UDF2063 ms
Inline Table-Valued UDF183 ms
Table-Valued UDF85620 ms
10 Million rows:
No function1980 ms
Scalar UDF20760 ms
Inline Table-Valued UDF1913 ms
Table-Valued UDFstill running after 8 minutes
This was also a nice reminder that inline table-valued functions are MUCH faster than a table-valued function.
Thank you for the feedback and for posting the runtimes from your machine. It helps dispell the ol' "Well, it ran fine on my machine" syndrome.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2012 at 3:56 pm
JJ B (8/1/2012)
Thanks! I learned a couple things here.So well written. Thanks for taking the time to write this article in a way so understandable and sure to educate.
I try to write pretty much as when I'm sitting with someone and trying to teach one-on-one in front of a computer. Thank you for the very kind words, JJ.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2012 at 4:00 pm
David In BC (8/1/2012)
Nicely done. Something to keep in mind when testing.
Roy Ernest (8/1/2012)
Great article as usual Jeff. I did not know about the Set Statistics. Thx. I will keep this in mind when I have to look at performance.
Open Minded (8/2/2012)
Great article! I learned something so simple and elegant with just one line.
Thank you, folks. It was a bit of a revelation for me when I first ran into the symptom. I wonder how many times I've personally made the wrong recommendation because of this problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2012 at 4:08 pm
David.Poole (8/1/2012)
So SET STATISTICS TIME ON is the sames as WATCH SCHRODINGERS CAT:hehe:I learnt something from this article.
Note that STATISTICS TIME ON reports the execution time and the CPU time where as the GETDATE() method reports only the execution time.
Simon Sabin did an interesting talk at SQLBits some time ago about the way in which T-SQL functions hide their cost from execution plans and how you can get a good estimate of their true cost.
One point to bear in mind in the T-SQL functions Vs inline code debate is the readability and maintainability of the code.
If the action of a function is necessarily complicated then the chances are it is not the sort of thing you are going to want as inline SQL except if it is code that exists in very few places.
With any calculation I would ask the following questions
- Should it be done in the database layer?
- .....Really????
- Is ultimate performance the absolute priority?
- Is the code of a level of complexity that justifies a function?
- Is the code going to be used in enough places to justify a function?
I absolutely agree with asking those questions. It's been too often where I've seen folks do things like build a "time stripping" function (the ol' DATEADD/DATEDIFF trick) instead of just doing inline. Shifting gears, I'm one of the first to say that performance is always a prime factor but, then again, I'm not likely to use something such as a CLR even if it turns out to be faster (and, it doesn't always) so I really appreciate you bringing these questions up. Thanks, David.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2012 at 4:35 pm
Usman Butt (8/2/2012)
As always great article. But I am sure there must be something up your sleeve 😉 After following you so much I am not believing that you would be using the scalar function at all (Unless the String parameter is of small length).
I wish I could say the it was something so elegant but it's nothing like that. Somewhere along the line, I apparently made a serious mistake in testing. :blush: Thanks to you folks, I'll go back (It was more than a year ago) and see what the heck I did wrong. I'll be sure to correct the article, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2012 at 5:08 pm
rragno (8/1/2012)
It is irritating how big the gap is between the performance here and high-performance code, though. Why should we have to be impressed that multiplying a million numbers takes 800 ms? A simplistic implementation of the same process, applying a function call, in C# clocks in at 0.8 ms on my old laptop. That is 1000x faster. Obviously, there are reasons for some slowdown, but this magnitude is hard to accept.Also skipped here is simply using a CLR-implemented function. Something like:
[Microsoft.SqlServer.Server.SqlFunction(
DataAccess = DataAccessKind.None, IsDeterministic = true,
IsPrecise = false, SystemDataAccess = SystemDataAccessKind.None
)]
public static SqlInt32 Mult2(SqlInt32 n)
{
return n * 2;
}
This gives much better performance.
My measurements on a modern machine, running SQL Server 2012:
Baseline (questionable due to optimization, but...):
414 ms.
150 ms - w/o SET STATISTICS
TSQL Function:
2403 ms.
1653 ms - w/o SET STATISTICS
iSF Function:
263 ms.
203 ms - w/o SET STATISTICS
CLR Function:
349 ms.
346 ms - w/o SET STATISTICS
While the iSF beats the CLR function, the CLR version can be used just like the normal TSQL one.
The optimizer could be ruining all of these measurements, of course.
And as I started with, all of this is orders of magnitude away from simple code for the same task.
Any chance of seeing the C# code you used for the multiplication test from your first paragraph above?
Also, thank you very much for posting your timing results. It really does show the impact that SET STATISTICS makes even on inline code.
IIRC, SET STATISTICS doesn't measure resources consumed by the CLR itself but don't take my word for it. I just can't find the URL where I saw someone prove it.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2012 at 8:23 pm
That is the C# code! The cruft needed to deploy might be difficult, but they made some things elegant. That method I posted is the whole method.
I can send over a little package... Once you compile that into a DLL, it is a one-liner to add the assembly into your DB, and then another to declare a function that calls this method. Otherwise, the testing is identical to the TSQL function.
The statistics might not record much for CLR functions, but the wall clock time is also consistent - it seriously stomps on the TSQL time.
The standalone C# code I threw together, to make it 1000x faster without using the database at all, I can dig up; it isn't that special, of course.
August 5, 2012 at 10:00 pm
I can't find the faulty experiment I did but it does't matter now. Usman's fine bit of T-SQL prestidigitation will do.
I made a couple of minor changes as noted in the revision history. I haven't tested it for all possibilities but is does seem to handle apostropied names, possessives, and some oddities such as the "Roaring 20's" with only a minor decrease in overall performance. I also tweaked the code with some of the other suggestions made and some of my own.
DROP FUNCTION [dbo].[InitialCap]
GO
CREATE FUNCTION dbo.InitialCap
/*******************************************************************************
Purpose:
Capitalize the first letter of the given string and any letter that follows a
non-letter character except for those letters which follow a single quote. All
other letters will be changed to lower case.
Usage notes:
1. This is an "Inline Scalar Function" or "iSF" for short. In reality, it's
an iTVF (Inline Table Valued Function) that returns a single value. It must
be used either in a FROM clause or a CROSS APPLY because it cannot be called
like a normal Scalar User Defined Function.
2. The key to understanding this function is that t.N is always 1 less than the
character being concatenated.
3. This code will correctly handle apostrophied and possessive names like
O'Hare's, up to 2 letter contractions, and "odd" things like the "Roaring
20's".
Revision History:
Rev 00 - 02 Aug 2012 - Usman Butt
- Original Code
Rev 01 - 05 Aug 2012 - Jeff Moden
- Reformatted and additional comments added.
- Made a couple of minor optimizations including handling some of the
slower, non-default collations.
- Altered the code to handle things found in "User Note #3) above.
*******************************************************************************/
--===== Declare the IO of the function
(@String VARCHAR(8000))
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000).
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of
-- rows right up front for both a performance gain and
-- prevention of accidental "overruns".
SELECT TOP (ISNULL(DATALENGTH(@String),0)) --Keeps trailing spaces
ROW_NUMBER() OVER (ORDER BY [N]) FROM E4
)
--===== Force the first character of the string to upper case.
-- Obviously, non-letter values will not be changed by UPPER.
SELECT InitialCapString = UPPER(LEFT(@String,1))
+ ( --=== If the current character in the given string isn't a letter then
-- concatenate the next character as an UPPER case character.
-- Otherwise, make it lower case character.
-- The COLLATE clause speeds up non-default collations.
SELECT CASE
WHEN SUBSTRING(@String, t.N , 1) COLLATE Latin1_General_BIN
LIKE '[^A-Za-z'']' COLLATE Latin1_General_BIN
OR SUBSTRING(@String, t.N , 4) COLLATE Latin1_General_BIN
LIKE '[^A-Za-z][A-Za-z][A-Za-z][A-Za-z]' COLLATE Latin1_General_BIN
THEN UPPER(SUBSTRING(@String, t.N+1, 1))
ELSE LOWER(SUBSTRING(@String, t.N+1, 1))
END
FROM cteTally t --No WHERE clause needed because of TOP above
ORDER BY t.N
FOR XML PATH(''), TYPE
).value('text()[1]', 'varchar(8000)')
;
GO
The following code...
SELECT * FROM dbo.InitialCap('DON''T YOU THINK THEY''RE O''HARE''S VERSION OF THE ROARING 20''S')
SELECT * FROM dbo.InitialCap('don''t you think they''re o''hare''s version of the roaring 20''s')
... returns the following output.
InitialCapString
------------------------------------------------------------
Don't You Think They're O'Hare's Version Of The Roaring 20's
(1 row(s) affected)
InitialCapString
------------------------------------------------------------
Don't You Think They're O'Hare's Version Of The Roaring 20's
(1 row(s) affected)
That being said, I believe this is one of those places where a CLR and some REGEX may be faster.
Of course, this isn't proper "Title" case... words like "of" and "the" shouldn't be capitalized unless they're the first word but hat's off on making a fast T-SQL version of "Initial Caps".
--Jeff Moden
Change is inevitable... Change for the better is not.
August 6, 2012 at 12:43 am
Jeff Moden (8/5/2012)
I can't find the faulty experiment I did but it does't matter now. Usman's fine bit of T-SQL prestidigitation will do.
Can I safely assume that you have approved my Anti-RBAR alliance membership? 😉
That being said, I believe this is one of those places where a CLR and some REGEX may be faster.
Of course, this isn't proper "Title" case... words like "of" and "the" shouldn't be capitalized unless they're the first word but hat's off on making a fast T-SQL version of "Initial Caps".
Totally agreed. "Intitial Cap"-ping needs prior English grammar knowledge etc embedded in the system and TSQL is definitely not the best place to handle it.
August 6, 2012 at 1:03 am
Jeff Moden (8/5/2012)
mmilodragovich (8/1/2012)
Thanks for the detailed and METHODICAL analysis. I once was troubleshooting a performance problem in a large, complicated application and it came down to a "set quoted identifer on" statement in a scalar function. The statement was not needed, was probably generated by a developer who used a right-click-on-object to generate the "create" statement. The function was being called massive number of times so even the slightest performance difference was hugely magnified. I've been wary ever since but know that knowledge is power, so thanks for the article.You're welcome and thanks for the feedback. I've had similar not-easy-to-explain things happen in this wonderful world of T-SQL. For example, I discover quite by accident about a week ago that the current rendition of the DelimitedSplit8K and 4K functions from the "Tally OH!" article actually run faster if you select both of its outputs (ItemNumber and Item) rather than just the Item.
Aghhh...I guess this is another debatable topic. I have observed it oppositely for most part of my testing (I hope you remember I did some working on this). At that time I asked the question to myself that is the ItemNumber really needed since the sequence of the strings were not relevant in my case? So I did omit the ItemNumber (Even only while executing the query as the QO was smart enough to remove the ItemNumber-ing ) and hence saw performance gain as some cpu cycles were saved.
But on the other hand with larger strings especially while testing the VARCHAR(MAX) version, I intentionally converted the ItemNumber from data-type BIGINT to NUMERIC(38) etc to give more memory to the query execution. This way I wanted to avoid any tempdb spilling etc.
So as always "it depends" 😉 I remember, I did find some other interesting things but my bad I did not document them (which is a must for people like me with such short memory 🙁 ).
August 6, 2012 at 6:20 am
Of course, if you really want to see how complex this kind of thing can get, try to make it deal with things like "USA" (or worse, "USA's"), or names like "deGas", "MacDonald" (make sure it doesn't blow up on "Macy" when you do that one), and so on.
There's a lot to this kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2012 at 11:46 am
Usman Butt (8/6/2012)
Can I safely assume that you have approved my Anti-RBAR alliance membership? 😉
Heh... make no "Butts" about it. 🙂 I know, I know... you've had "Mo-den" enough of those kinds of jokes. :w00t:
--Jeff Moden
Change is inevitable... Change for the better is not.
August 6, 2012 at 11:50 am
GSquared (8/6/2012)
Of course, if you really want to see how complex this kind of thing can get, try to make it deal with things like "USA" (or worse, "USA's"), or names like "deGas", "MacDonald" (make sure it doesn't blow up on "Macy" when you do that one), and so on.There's a lot to this kind of thing.
Heh... since I'm getting older, "deGas" has a special meaning to me, lately. 😀
I agree... Initial Caps is a whole lot easier than Proper or Title casing. And as soon as you make a rule to properly case "MacDonald", someone will want a lower case version. Even AI can't handle such exceptions. You need human intervention or for humans to do it right the first time.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 6, 2012 at 12:05 pm
Jeff Moden (8/6/2012)
GSquared (8/6/2012)
Of course, if you really want to see how complex this kind of thing can get, try to make it deal with things like "USA" (or worse, "USA's"), or names like "deGas", "MacDonald" (make sure it doesn't blow up on "Macy" when you do that one), and so on.There's a lot to this kind of thing.
Heh... since I'm getting older, "deGas" has a special meaning to me, lately. 😀
I agree... Initial Caps is a whole lot easier than Proper or Title casing. And as soon as you make a rule to properly case "MacDonald", someone will want a lower case version. Even AI can't handle such exceptions. You need human intervention or for humans to do it right the first time.
I was just wondering if anyone would draw a connection between McDonald's and deGas, honestly. :w00t:
Anyway, it's a good ICaps function. Slow on longer strings, but that's going to be a very rare real-world need.
And the key to the article was the bit about inline 1X1 UDFs as "scalar" functions with better performance, and the point about Set Stats. Regardless of ICaps functionality, those are both very good points.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 7, 2012 at 3:59 am
Jeff Moden (8/6/2012)
Usman Butt (8/6/2012)
Can I safely assume that you have approved my Anti-RBAR alliance membership? 😉Heh... make no "Butts" about it. 🙂 I know, I know... you've had "Mo-den" enough of those kinds of jokes. :w00t:
:laugh: Since living in this "Mo-den" era we had no other option but to enjoy such creativity 😉 Keep them coming. I wonder haven't you got any request to write a super joke book yet :hehe:
Viewing 15 posts - 46 through 60 (of 102 total)
You must be logged in to reply to this topic. Login to reply