September 18, 2008 at 12:42 pm
When SQL Server 2000 came out, UDF's (User-Defined Functions) where one of the more anticipated new features. I remember being both excited and then disapointed because of the double-edge sword effect of UDF's. There flexability and ease of use made UDF's very appealing however this came at a price and that was their performace.
From what I had read, Microsoft focused on making UDF's as flexable and versatile as possible firstand then in the next revision they'd work on performance. A piece in BOL 2005 title 'User-Defined Functa Basics' lists the benefits of using UDF's and one of these is:
They allow faster execution.
Similar to stored procedures, Transact-SQL user-defined functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times.
CLR functions offer significant performance advantage over Transact-SQL functions for computational tasks, string manipulation, and business logic. Transact-SQL functions are better suited for data-access intensive logic.
I've looked but been unable to find anything directly comparing the performance differences with UDF's on SQL 2000 and SQL 2005. Does anyone have any first-hand knowledge about these and how UDF's fair between 2000 & 2005 as well as whether or not they reduce Network Traffic (another feature point listed in this same BOL piece).
I'm looking at possibly using UDF's but I'd like to get some solid details on them in SQL 2005 BOL first.
Kindest Regards,
Just say No to Facebook!September 18, 2008 at 1:40 pm
I can't say I've done a direct compare from 2000 to 2005 on function execution. That being said, even IF the 2005 functions execute faster than their 2000 counterparts, the query optimizer still seems to send the outer execution plan into a tailspin, and by far and large, evaluates UDF's Row by row. So - assuming the execution plans are stored for the functions themselves, they STILL are (in a lot of cases) ripping the OUTER query's exec plan into a de facto cursor.
So - in most cases, there still is a fairly subtantial perf penalty for using UDF's when compared to running a comparable "stright" SQL statement with no UDF call.
I have seen a FEW scenarios where the compiled aspect of CLR functions can completely or mostly compensate for the performance penalty you see with T-SQL UDF's, but still - truly rare is the scenario where CLR will actually beat the "straight T-SQL".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply