August 1, 2012 at 11:19 am
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.
August 1, 2012 at 11:27 am
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.
August 1, 2012 at 12:20 pm
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.
August 1, 2012 at 12:57 pm
Nicely done. Something to keep in mind when testing.
August 1, 2012 at 1:17 pm
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.
-Roy
August 1, 2012 at 3:55 pm
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
August 1, 2012 at 9:54 pm
Very interesting article and it will make me think about the way I use functions (and statistics!) from now on.
I hit a problem with recursion using the InitialCapFaster function
SELECT b.string FROM #TestTable a CROSS APPLY dbo.InitialCapFaster('lots of words test to see how it can handle the different amount of iterations and i am just adding words now for the sake of it. i need more words to test the maximum amount of recursion that can be handled by this function. with one of my tests it complained that the maximum amount of recursion is 100, so hopefully this is enough words to hit that limit') b
with the result of
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
I also tried something very similar to a previous post by GSquared and was posting it until I read that.
Nice little problem though
August 1, 2012 at 10:25 pm
Yowch! My most sincere apologies for a lack of replies, folks. I've been head down into a hurricane at work these last couple of weeks and especially this week. As a result, I totally forgot this article was coming out today. I'll try to respond to your excellent posts on this subject tomorrow night. I'm really sorry about this.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 1, 2012 at 11:06 pm
Msg 530, Level 16, State 1, Line 1The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
If it is supposed to work on a really long strings you should add option(maxrecursion 0); Like this:
select b.string FROM dbo.InitialCapFaster('...very long str..') b option(maxrecursion 0);
August 2, 2012 at 3:08 am
Great article! I learned something so simple and elegant with just one line.
August 2, 2012 at 4:04 am
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).
Here is my first shot at it with tally table (I am sure there could be more room for improvement and could have a bug or two as I did not test it thoroughly). Always been a big fan and keen follower of yours.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[InitialCapiTVF] (@String VARCHAR(8000))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT InitialCapString =
UPPER(LEFT(@String,1)) --FIRST LETTER OF THE STRING MUST BE IN CAPS
--REST OF THE STRING LETTERS HANDLING GOES HERE
+ (( SELECT CASE WHEN
/*====CHECK IF NOT LIKE A-z OR NOT EQUAL TO ''==========*/
-- USE OF LIKE WOULD YIELD BETTER PERFORMANCE AS COMPARED
-- TO NOT LIKE */
SUBSTRING(@String, [N] , 1) LIKE '[^A-Za-z'']'
--IF ABOVE CRITERIA FULFILLS THEN TURN THE NEXT LETTER IN CAPS
THEN UPPER(SUBSTRING(@String, [N]+1, 1))
--IF ABOVE CRITERIA DOES NOT FULFILL THEN
--EITHER TURN THE NEXT LETTER IN SMALL CAPS
--OR LEAVE IT AS THE SAME (CHOICE IS YOURS)
--ELSE LOWER(SUBSTRING(@String, [N]+1, 1))
ELSE SUBSTRING(@String, [N]+1, 1)
END
FROM [dbo].[Tally]
WHERE [N] <= LEN(@String)
ORDER BY N
FOR XML PATH('')
, TYPE).value('text()[1]', 'varchar(8000)')) ;
August 2, 2012 at 4:47 am
It would be interesting to see how this simplified example function performs against a view with a cross join to a tally table providing the parameters. If you know paramaters are only integers and in small ranges you can actually use such a setup in SQL Server 2000.
Will that solution be faster or slower then the iTVF?
And what if we replace the tally table with a generated one using a CTE?
Anyone up for this?
August 2, 2012 at 4:48 am
GSquared (8/1/2012)
Here's a function I tested, and the test I used:
USE [ProofOfConcept]GO
/****** Object: UserDefinedFunction [dbo].[InitialCapTest1] Script Date: 08/01/2012 11:20:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[InitialCapTest1] (@String VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000);
WITH Seeds(Seed)
AS (SELECT Val
FROM ( VALUES ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1) ) AS Vals (Val)),
Numbers(Number)
AS (SELECT TOP (8000)
ROW_NUMBER() OVER (ORDER BY S10.Seed)
FROM Seeds AS S10
CROSS JOIN Seeds AS S100
CROSS JOIN Seeds AS S1000
CROSS JOIN Seeds AS S10000)
SELECT @Output = ( SELECT CASE WHEN SUBSTRING(@String, Number - 1, 1) NOT LIKE '[a-z]'
THEN UPPER(SUBSTRING(@String, Number, 1))
ELSE LOWER(SUBSTRING(@String, Number, 1))
END
FROM Numbers
WHERE Number <= LEN(@String)
ORDER BY Number
FOR XML PATH(''),
TYPE).value('.[1]', 'varchar(8000)');
RETURN @Output;
END;
I created that, and the function referenced in the article, then populated a test environment as follows:
CREATE TABLE ##T(ID INT IDENTITY
PRIMARY KEY,
Col1 VARCHAR(8000));
INSERT INTO ##T
(Col1)
SELECT 'Scalar UDFs are generally accepted as one of the worst things you can do performance-wise in T-SQL. However, they may have earned a reputation that they simply don''t deserve because a common method of measuring performance (SET STATISTICS TIME ON) injects a major performance problem of its own when used to measure the performance of Scalar UDFs.
That, notwithstanding, some Scalar UDFs really are a lot slower than running direct code but direct code means there is no encapsulation and may not be as easy, quick, or consistent to support development efforts. Even Scalar UDFs have the advantage of getting everyone to do things the same way. Of course, everyone doing all the same thing the slow way isn''t an advantage at all.
In some cases and if the Scalar UDF code can be written as a single query, there can be a highly significant performance gain by converting (or writing originally) the Scalar UDF as an "Inline Scalar Function" (iSF), which is the same as writing an "Inline Table Valued Function" (iTVF) that returns only one value.
Doing so is not a performance panacea, though. Although it hasn''t been proven within the confines of this article, it''s a well known fact that "all in one query" solutions are frequently performance problems themselves. To be sure of any benefit in converting Scalar UDFs to iSFs, you must test and it must be with a lot of rows to really do it right.
Last and certainly not least, understand that anything you measure will be changed especially when it comes to SET STATISTICS TIME ON. Although I''ll personally continue to use it on forums to conveniently show performance differences between coding methods, I''ll do so only after I''ve made absolutely sure that it''s not injecting major but artificial performance problems itself. '
FROM dbo.Numbers;
(You may recognize the text.) I wanted to test on a reasonable number of rows, on bigger strings. Perhaps a weird test, but it's what I wanted to check. 10,001 rows in ##T from that based on my Numbers table.
The tests:
DECLARE @T TABLE (Col1 VARCHAR(8000));DECLARE @Start DATETIME = GETDATE();
INSERT INTO @T
(Col1)
SELECT dbo.InitialCap(Col1)
FROM ##T;
SELECT DATEDIFF(millisecond, @Start, GETDATE());
DECLARE @T TABLE (Col1 VARCHAR(8000));DECLARE @Start DATETIME = GETDATE();
INSERT INTO @T
(Col1)
SELECT dbo.InitialCapTest1(Col1)
FROM ##T;
SELECT DATEDIFF(millisecond, @Start, GETDATE());
InitialCap runtime, per DateDiff = 182,526 ms
InitialCapTest1 = 21,673
"Single-query" version, even without being iSF, is about 9X faster, per this test.
Tested again, with "Bill O'Malley" as the string, 10k rows.
TRUNCATE TABLE ##T;INSERT INTO ##T (Col1)
SELECT 'Bill O''Malley'
FROM dbo.Numbers;
(I picked that string, because InitialCaps handled it "incorrectly". It's per-spec, but not "human right".)
Same tests as above.
InitialCaps runtime = 100 ms
InitialCapsTest1 runtime = 9836 ms
So, on a short string, the InitialCaps version is much faster, while on a long string, the InitialCapsTest1 version is much faster.
Summary: Know your data patterns. Picking which function to use depends a lot on what you'll be running it on, not just on "one test said".
Post-script: Name-case coding will be wrong much of the time. Bill O'Malley will likely end up as Bill O'malley, or Bob's will end up as Bob'S, unless you code some seriously smart algorithms into it. Any code that handles MacDonald correctly will probably mangle Macy or Macey, and vice-versa. The more "correct" you make it, the more complex it will be, and the slower it will run, and (likely) the buggier it will get.
Definitely an interesting point about checking timing with Stats On. Traces are usually better, in my experience, and I've used the GetDate() trick for years. The GetDate() one is especially useful if you need to test multiple statements in a single script.
@GSquared Any reason for not turning it into iTVF/iSF? I guess then the it could have been much performant? Also, fixing TOP(8000) ROW_NUMBER in the function could be causing the major slowdown while comparing shorter strings. I will try convert it into iTVF/iSF with LEN check in my testing to see the difference.
For Bill O'Malley kind of stuff, another approach could have been to leave the post-letter/alphabet after the single quote as it is? But I am always impressed by the detailed analysis you come up with. (I wish I could analyze things with such details and ease)
Last but not least knowing your data pattern is a must but sometimes a difficult task itself 😉
August 2, 2012 at 5:08 am
Sorry, since not everyone have the tally table physically on their servers, here is a version using cteTally
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[InitialCapiTVF_CteTally] (@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)) ROW_NUMBER() OVER (ORDER BY [N]) FROM E4
)
SELECT InitialCapString =
UPPER(LEFT(@String,1)) --FIRST LETTER OF THE STRING MUST BE IN CAPS
--REST OF THE STRING LETTERS HANDLING GOES HERE
+ (( SELECT CASE WHEN
/*====CHECK IF NOT LIKE A-z OR NOT EQUAL TO ''==========*/
-- USE OF LIKE WOULD YIELD BETTER PERFORMANCE AS COMPARED
-- TO NOT LIKE */
SUBSTRING(@String, [N] , 1) LIKE '[^A-Za-z'']'
--IF ABOVE CRITERIA FULFILLS THEN TURN THE NEXT LETTER IN CAPS
THEN UPPER(SUBSTRING(@String, [N]+1, 1))
--IF ABOVE CRITERIA DOES NOT FULFILL THEN
--EITHER TURN THE NEXT LETTER IN SMALL CAPS
--OR LEAVE IT AS THE SAME (CHOICE IS YOURS)
--ELSE LOWER(SUBSTRING(@String, [N]+1, 1))
ELSE SUBSTRING(@String, [N]+1, 1)
END
FROM [cteTally]
WHERE [N] <= LEN(@String)
ORDER BY N
FOR XML PATH('')
, TYPE).value('text()[1]', 'varchar(8000)')) ;
August 2, 2012 at 6:14 am
Using GSquared's testing technique (tweaked the insert script a bit to avoid optimizer's table spool intelligent duplicate string handling)
--DATA POPULATION SCRIPT
CREATE TABLE ##T
(
ID INT IDENTITY
PRIMARY KEY
,Col1 VARCHAR(8000)
) ;
INSERT INTO ##T
(
Col1
)
SELECT TOP 10000
'Scalar UDFs are ge''nerally accepted as one of the worst things you can do performance-wise in T-SQL. However, they may have earned a reputation that they simply don''t deserve because a common method of measuring performance (SET STATISTICS TIME ON) injects a major performance problem of its own when used to measure the performance of Scalar UDFs.
That, notwithstanding, some Scalar UDFs really are a lot slower than running direct code but direct code means there is no encapsulation and may not be as easy, quick, or consistent to support development efforts. Even Scalar UDFs have the advantage of getting everyone to do things the same way. Of course, everyone doing all the same thing the slow way isn''t an advantage at all.
In some cases and if the Scalar UDF code can be written as a single query, there can be a highly significant performance gain by converting (or writing originally) the Scalar UDF as an "Inline Scalar Function" (iSF), which is the same as writing an "Inline Table Valued Function" (iTVF) that returns only one value.
Doing so is not a performance panacea, though. Although it hasn''t been proven within the confines of this article, it''s a well known fact that "all in one query" solutions are frequently performance problems themselves. To be sure of any benefit in converting Scalar UDFs to iSFs, you must test and it must be with a lot of rows to really do it right.
Last and certainly not least, understand that anything you measure will be changed especially when it comes to SET STATISTICS TIME ON. Although I''ll personally continue to use it on forums to conveniently show performance differences between coding methods, I''ll do so only after I''ve made absolutely sure that it''s not injecting major but artificial performance problems itself. '
+ CAST(N AS VARCHAR(6))
FROM dbo.[Tally] AS T ;
--DDL SCRIPTS
--InitialCapiTVF_CteTally FUNCTION using cteTally
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[InitialCapiTVF_CteTally] (@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)) ROW_NUMBER() OVER (ORDER BY [N]) FROM E4
)
SELECT InitialCapString =
UPPER(LEFT(@String,1)) --FIRST LETTER OF THE STRING MUST BE IN CAPS
--REST OF THE STRING LETTERS HANDLING GOES HERE
+ (( SELECT CASE WHEN
/*====CHECK IF NOT LIKE A-z OR NOT EQUAL TO ''==========*/
-- USE OF LIKE WOULD YIELD BETTER PERFORMANCE AS COMPARED
-- TO NOT LIKE */
SUBSTRING(@String, [N] , 1) LIKE '[^A-Za-z'']'
--IF ABOVE CRITERIA FULFILLS THEN TURN THE NEXT LETTER IN CAPS
THEN UPPER(SUBSTRING(@String, [N]+1, 1))
--IF ABOVE CRITERIA DOES NOT FULFILL THEN
--EITHER TURN THE NEXT LETTER IN SMALL CAPS
--OR LEAVE IT AS THE SAME (CHOICE IS YOURS)
ELSE LOWER(SUBSTRING(@String, [N]+1, 1))
--ELSE SUBSTRING(@String, [N]+1, 1)
END
FROM [cteTally]
--WHERE [N] <= LEN(@String)
ORDER BY N
FOR XML PATH('')
, TYPE).value('text()[1]', 'varchar(8000)')) ;
GO
--GSquared's function turned into iTVF with LEN check
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[InitialCapTest1_iTVF] (@String VARCHAR(8000))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH Seeds(Seed)
AS (SELECT Val
FROM ( VALUES ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1) ) AS Vals (Val)),
Numbers(Number)
AS (SELECT TOP (ISNULL(DATALENGTH(@String),0))
ROW_NUMBER() OVER (ORDER BY S10.Seed)
FROM Seeds AS S10
CROSS JOIN Seeds AS S100
CROSS JOIN Seeds AS S1000
CROSS JOIN Seeds AS S10000)
SELECT InitialCapString = (( SELECT CASE WHEN SUBSTRING(@String, Number - 1, 1) NOT LIKE '[A-Za-z'']'
THEN UPPER(SUBSTRING(@String, Number, 1))
ELSE LOWER(SUBSTRING(@String, Number, 1))
END
FROM Numbers
--WHERE Number <= LEN(@String)
ORDER BY Number
FOR XML PATH(''),
TYPE).value('.[1]', 'varchar(8000)'));
GO
-- PERFORMANCE TEST
DECLARE @T TABLE (Col1 VARCHAR(8000));
DECLARE @Start DATETIME
SET @Start = GETDATE();
INSERT INTO @T
(Col1)
SELECT dbo.InitialCap(Col1)
FROM ##T;
SELECT DATEDIFF(millisecond, @Start, GETDATE());
EXECUTION TIME = 232,080 ms
DECLARE @T TABLE (Col1 VARCHAR(8000));
DECLARE @Start DATETIME
SET @Start = GETDATE();
INSERT INTO @T
(Col1)
SELECT InitialCapString
FROM ##T
CROSS APPLY dbo.[InitialCapiTVF_CteTally](Col1)
OPTION(MAXDOP 1);
SELECT DATEDIFF(millisecond, @Start, GETDATE());
EXECUTION TIME = 16,263 ms
DECLARE @T TABLE (Col1 VARCHAR(8000));
DECLARE @Start DATETIME
SET @Start = GETDATE();
INSERT INTO @T
(Col1)
SELECT InitialCapString
FROM ##T
CROSS APPLY dbo.[InitialCapTest1_iTVF](Col1)
OPTION(MAXDOP 1);
SELECT DATEDIFF(millisecond, @Start, GETDATE());
EXECUTION TIME = 18,076 ms
EDIT:
Just realize that the LEN check was redundant in the code. Removal of that check could result in more performance gain.
Viewing 15 posts - 16 through 30 (of 102 total)
You must be logged in to reply to this topic. Login to reply