February 2, 2010 at 10:52 am
I am looking at a stored procedure that uses a lot of functions. When I look at the DMV's and DMF's I see astronomical numbers in total_worker_time, total_logical_reads, and total_elapsed_time. I am not so familiar with functions, so can someone please explain why functions (and not all functions) would have performance issues.
February 2, 2010 at 10:59 am
Depends on the type of function, but a common error is to do data access inside a scalar function (for example). The idea sounds reasonable until you realise that the function is called once per matching row of the input, which may be quite large. The data access inside the function therefore also executes once per row, quite separately, and without the benefits of the set-based processing SQL Server is so good at.
In effect, it creates a socking great cursor, where the only available join back to the driving data set is a loop join. It is unutterably horrible.
Functions should be thought of as the mathematical type - not the programming language type. I can't think of one valid reason to do data access from inside one - and come to think of it, I can't think of a case where a T-SQL function would match the performance of a SQLCLR function either.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 2, 2010 at 10:51 pm
Paul,
I assume you are talking about data access within scalar functions, not inline table-valued functions. Data access through ITVF's can be ruthlessly efficient (best I could do to match "unutterably horrible").
Best regards,
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 2, 2010 at 11:05 pm
The Dixie Flatline (2/2/2010)
I assume you are talking about data access within scalar functions, not inline table-valued functions. Data access through ITVF's can be ruthlessly efficient (best I could do to match "unutterably horrible").
Hi Bob,
Yes - talking about scalar functions that do data access (I did mention the word in the first sentence, but not subsequently). In-line TVFs are quite different - the ITVF query plan is incorporated directly into the overall plan, and the whole thing can be optimized in the usual way. Nice.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 2, 2010 at 11:13 pm
Curiousity question. Have you seen any documentation about the performance of scalar functions which do NOT do data access compared to inline table valued functions which produce the same results? If not, I must do some experimenting.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 3, 2010 at 1:26 am
The Dixie Flatline (2/2/2010)
Curiousity question. Have you seen any documentation about the performance of scalar functions which do NOT do data access compared to inline table valued functions which produce the same results? If not, I must do some experimenting.
I blogged this a little while back..
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/10/15/udf-overhead-a-simple-example.aspx
Important to note this to though
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/11/24/the-observer-effect-in-action.aspx
February 3, 2010 at 4:39 am
The Dixie Flatline (2/2/2010)
Curiousity question. Have you seen any documentation about the performance of scalar functions which do NOT do data access compared to inline table valued functions which produce the same results? If not, I must do some experimenting.
This is a very interesting question. As Mr. Ballantyne's tests show, the in-line TVF can produce the fastest possible plan. Some results from my machine, based very much on Dave's blog entires, but including a CLR scalar function too:
[font="Courier New"]In-line T-SQL TVF: 85ms
CLR scalar function: 238ms
T-SQL scalar function: 447ms[/font]
(results are worker times - run the full script for more detail)
-- You need this sample database to run these tests
USE AdventureWorks;
GO
-- Turn off stuff we don't want to affect the results
SET NOCOUNT ON;
SET STATISTICS IO, TIME OFF;
GO
-- Reset the system
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
DBCC FREESYSTEMCACHE('ALL');
GO
-- Warm the data cache
DECLARE @m MONEY;
SELECT @m = UnitPrice
FROM Sales.SalesOrderDetail;
GO
-- CLR functionality required
IF NOT EXISTS(SELECT * FROM sys.configurations WHERE name = N'clr enabled' AND value_in_use = 1)
BEGIN
EXECUTE sp_configure 'clr enabled', 1;
RECONFIGURE;
END;
GO
-- Scalar function
CREATE FUNCTION Sales.CalcCommission(@Price MONEY)
RETURNS MONEY
WITH SCHEMABINDING
AS
BEGIN
RETURN (@Price/$100.00) * $5;
END;
GO
-- Inline TVF
CREATE FUNCTION Sales.InlineCalcCommission(@Price MONEY)
RETURNS TABLE
AS
RETURN SELECT (@Price/$100.00) * $5 AS Commission;
GO
-- CLR assembly
CREATE ASSEMBLY [Test]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103004859694B0000000000000000E00002210B010800000A000000060000000000004E290000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000F428000057000000004000000803000000000000000000000000000000000000006000000C000000482800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000005409000000200000000A000000020000000000000000000000000000200000602E72737263000000080300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C00000000600000000200000010000000000000000000000000000040000042000000000000000000000000000000003029000000000000480000000200050078200000D00700000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000720F00281200000A1B16161618731300000A281400000A731500000A2A1E02281600000A2A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C0000002C020000237E000098020000E802000023537472696E67730000000080050000080000002355530088050000100000002347554944000000980500003802000023426C6F620000000000000002000001471500000900000000FA01330016000001000000150000000200000002000000020000001600000010000000010000000200000000000A0001000000000006003D0036000A00650050000A00A10086000600CB00B9000600E200B9000600FF00B90006001E01B90006003701B90006005001B90006006B01B90006008601B9000600BE019F010600D2019F010600E001B9000600F901B90006002902160243003D02000006006C024C0206008C024C020A00B40286000600C90236000000000001000000000001000100010010001800000005000100010050200000000096006E000A0001006D2000000000861880001100030000000000000000000100B300190080001100210080002800290080002800310080002800390080002800410080002800490080002800510080002800590080002800610080002D00690080002800710080002800790080002800810080003200910080003800990080001100A100800011001100D1028501A90080008A01A900DB029301110080009C0109008000110020008B003D0024000B0015002E003300A2012E001300A2012E001B00B1012E002300B1012E002B00B1012E005B00CF012E00830018022E004300B1012E005300B1012E003B00B7012E006B00F9012E00730006022E007B000F0244000B001500048000000100000000000000000000000000AA02000002000000000000000000000001002D000000000002000000000000000000000001004400000000000000003C4D6F64756C653E005363616C61725544462E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C4D6F6E657900636C7243616C63436F6D6D697373696F6E002E63746F72004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650050726963650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005363616C61725544460053716C46756E6374696F6E41747472696275746500446563696D616C006765745F56616C7565006F705F4D756C7469706C790000000320000000000090C68AFC833B9C4DA93D2CDF815DB9F90008B77A5C561934E0890600011109110903200001120100010054020A49734E756C6C61626C6500042001010E042001010205200101114504200101088146010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E6973746963015402094973507265636973650104200011550820050108080802050800021155115511550520010111550E0100095363616C6172554446000005010000000017010012436F7079726967687420C2A920203230313000002901002439313139353039302D666463332D343636622D613034312D38613262333061313638643300000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000004859694B00000000020000008E00000064280000640A0000525344531393840746FAC54A9EDE159AF21CB88F04000000433A5C446F63756D656E747320616E642053657474696E67735C5061756C5C4D7920446F63756D656E74735C56697375616C2053747564696F20323030385C50726F6A656374735C5363616C61725544465C5363616C61725544465C6F626A5C52656C656173655C5363616C61725544462E7064620000001C29000000000000000000003E290000002000000000000000000000000000000000000000000000302900000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000B00200000000000000000000B00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00410020000010053007400720069006E006700460069006C00650049006E0066006F000000EC01000001003000300030003000300034006200300000003C000A000100460069006C0065004400650073006300720069007000740069006F006E00000000005300630061006C00610072005500440046000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D00650000005300630061006C00610072005500440046002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005300630061006C00610072005500440046002E0064006C006C00000034000A000100500072006F0064007500630074004E0061006D006500000000005300630061006C00610072005500440046000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000503900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
/*
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None,SystemDataAccess = SystemDataAccessKind.None,IsDeterministic = true,IsPrecise = true)]
[return: SqlFacet(IsNullable = false)]
public static SqlMoney clrCalcCommission([SqlFacet(IsNullable = false)] SqlMoney Price)
{ return new SqlMoney(Price.Value * 0.05M); }
};
*/
GO
-- CLR scalar function
CREATE FUNCTION dbo.clrCalcCommission(@Price MONEY)
RETURNS MONEY
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME Test.UserDefinedFunctions.clrCalcCommission;
GO
-- T-SQL Scalar function
DECLARE @Bitbucket MONEY;
SELECT @Bitbucket =
Sales.CalcCommission(UnitPrice)
FROM Sales.SalesOrderDetail;
GO
-- T-SQL inline TVF
DECLARE @Bitbucket MONEY;
SELECT @Bitbucket =
Commission
FROM Sales.SalesOrderDetail
CROSS
APPLY Sales.InlineCalcCommission(UnitPrice);
GO
-- CLR scalar function
DECLARE @Bitbucket MONEY;
SELECT @Bitbucket =
dbo.clrCalcCommission(UnitPrice)
FROM Sales.SalesOrderDetail;
GO
-- Results
SELECT [rank] = RANK() OVER (ORDER BY QS.total_elapsed_time ASC),
ST.text,
QS.execution_count,
elapsed_time_ms = QS.total_elapsed_time / 1000,
logical_reads = QS.total_logical_reads,
cpu_time_ms = QS.total_worker_time / 1000
FROM sys.dm_exec_query_stats QS
CROSS
APPLY sys.dm_exec_sql_text (QS.sql_handle) ST
WHERE ST.text LIKE '%@BitBucket%'
AND ST.text NOT LIKE '%sys.dm_exec_query_stats%'
ORDER BY
[rank] ASC;
GO
-- Tidy up
DROP FUNCTION Sales.CalcCommission;
DROP FUNCTION Sales.InlineCalcCommission;
DROP FUNCTION dbo.clrCalcCommission;
DROP ASSEMBLY Test;
-- End
So, the ITVF is fastest by quite some margin here. The reason being, of course, that the optimizer is able to completely remove the APPLY operation, and place the ITVF computation directly in a single Compute Scalar in the final plan.
Whether this will always occur for more complex requirements is hard to say. I guess it depends on the optimizer - if it is able to omit the APPLY operation completely and represent the computation efficiently then it's hard to see how to beat the ITVF. I suppose I should also mention that ITVF solutions are required to express all the computational logic in a single SELECT statement.
If I get a minute, I might try some more complex examples and post them here if they are interesting.
Paul
edit: for layout
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 3, 2010 at 6:45 pm
Thanks, Dave. Exactly the kind of information I was looking for.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 3, 2010 at 8:23 pm
Thanks to you as well, Emperor Paulpatine.
I'm not surprised that the ITVFs are faster than the scalar functions, but I am surprised by the percentage difference for functions that don't access data themselves. I was also surprised by the CLR performance. I would have expected the overhead to be greater for calling CLR routines than for internal user functions, but obviously my expectations were misplaced. If you find anything interesting please share.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 3, 2010 at 11:42 pm
The Dixie Flatline (2/3/2010)
Thanks to you as well, Emperor Paulpatine. I'm not surprised that the ITVFs are faster than the scalar functions, but I am surprised by the percentage difference for functions that don't access data themselves. I was also surprised by the CLR performance. I would have expected the overhead to be greater for calling CLR routines than for internal user functions, but obviously my expectations were misplaced. If you find anything interesting please share.
Cheers Bob - I will. On the subject of SQLCLR overhead for scalar functions, BOL says (under Performance of CLR Integration):
"CLR functions benefit from a quicker invocation path than that of Transact-SQL user-defined functions. Additionally, managed code has a decisive performance advantage over Transact-SQL in terms of procedural code, computation, and string manipulation. CLR functions that are computing-intensive and that do not perform data access are better written in managed code. Transact-SQL functions do, however, perform data access more efficiently than CLR integration."
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 4, 2010 at 7:22 am
Thanks Paul. I really do have to get on the CLR train, don't I? I will get to it right after I rewrite all of our scalar functions to be inline table variable functions. I'm getting a lot of practice using CTEs to mimic variables.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 4, 2010 at 7:38 am
The Dixie Flatline (2/4/2010)
Thanks Paul. I really do have to get on the CLR train, don't I?
No - my future consulting daily rates depend on the majority of skilled SQL Server people ignoring SQLCLR 😀
The Dixie Flatline (2/4/2010)
I will get to it right after I rewrite all of our scalar functions to be inline table variable functions. I'm getting a lot of practice using CTEs to mimic variables.
Console yourself with the fact that your current work sounds a good deal more interesting than mine!
Luckily I work harder on 'fun stuff' like SSC than anything else - just don't tell anyone...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 4, 2010 at 2:40 pm
Mum's the word.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 4, 2010 at 2:51 pm
I think the snake's out of the bag on that one 🙂
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply