Thursday, 31 May 2012

Is Table-Valued UDF is Performance Killer


One my previous article "Can view takes input parameters" I got some response from various sources like "Linked In" etc.
Here some of my readers give me some comments like
"A view might be more efficient than a table valued functions"
I searched different blogs and article related to it and find that there are a common misconnect is the Table value function is a poor performer.
So I decide to wire this article related to the performance factor of Table Valued function by collecting some notes from different sources.
How many type of Table valued function does MS SQL Server supports
There are 2 types of table valued function
1.    Single-statement table valued user defined functions, also called in-line
2.     Multi-statement table valued user defined functions
The simple example of both is mentioned bellow
Single Statement Table Valued function
CREATE FUNCTION dbo.fn_TableVal
                (@p_sal DECIMAL(20,2))
RETURNS TABLE
AS RETURN
(
      SELECT *
      FROM   emp_matsre
      WHERE  empSal>@p_sal
)
GO
Multi-statement table valued Function
CREATE FUNCTION dbo.fn_TableVal
                (@p_sal DECIMAL(20,2))
RETURNS @Ret_Table TABLE
        (idno    INT,
         empName VARCHAR(50),
         empSal  DECIMAL(20,2))
AS
BEGIN
    INSERT INTO @Ret_Table
      SELECT *
      FROM   emp_matsre
      WHERE  empSal>@p_sal
     
      RETURN

END
GO

The single-statement table valued function sometimes called the views with parameters.
Execution of these UDF's will be incorporated directly into the execution plan with the rest of the associated query. Indexes may be used if the query within the UDF is well constructed. It is generally safe constructs to use within your queries.
Multi-statement UDF's are built upon the structure of table variables.
These are not incorporated within the rest of the execution plan and usually show up as a table scan against a table with one row. The problem is your query may have more than one row. When the multi-statement UDF's are used on large sets of data they cause very serious performance problems. They won't scale well, and the performance issues are masked by the apparent low cost of the operations involved. We need to be extremely careful when using the multi-statement table valued UDF's. The problem is that the system sees the BEGIN/END and runs the PROCEDURE in a different context. Without it, it takes the subquery in the return() call and can simplify it out.
Non-inline functions do not contribute towards the cost reported by an execution plan. You need to use Profiler to see their effect.

Hope you like it.

Posted by: MR. JOYDEEP DAS

2 comments:

  1. nice piece of information, I had come to know about your internet site from my friend vinay, delhi,i have read atleast 12 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new post, once again hats off to you! Thanx a ton once again, Regards, Single Row Function in sql



    ReplyDelete
  2. brillant piece of information, I had come to know about your web-page from my friend hardkik, chennai,i have read atleast 9 posts of yours by now, and let me tell you, your webpage gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanx a million once again, Regards, Single Row Function in sql




    ReplyDelete