InLine UDF vs. Multistatement UDF? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

InLine UDF vs. Multistatement UDF?

Hi All,
I have been reading up alot on the differences between inline udf’s and multistatement UDFs. I know the main benefit of inline vs multi is that SQL cannot look inside a multi UDF to peformance a query analysis. So everyone says to go with the inline UDF. So after I updated 1 particular Stored Procedure to use a inline UDF, I ran some tests and compared the inline vs. multi execution plans in QA and SQL Profiler. To my great suprise I am noticing that the MultiStatement UDF is executing soo much quicker with 1/2 the amount of Reads. This is not the norm when comparing these 2 types of UDFs. My question to the group is can someone explain why the multistatement UDF would execute so much faster with less reads? they both use return TABLE types. I don’t know what else to look for to figure this out.. Thanks
Tom
Did you tested them indicidually or with a JOIN with some other tables?
Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com
I tested them with an INNER JOIN in a Stored Procedure. So all external factors are the same. Inside each UDF is a simple Select statement that retrieves 1 field(3700 rows). The issue I am having is how to explain to my boss why sometimes the multi UDF doesn’t execute fast enough and then other times, using a different stored procedure it executes faster than the inline. I need to be able to validate why I used 3 seperate inline UDFs instead of 1 multi UDF with IF/Else statements. Thanks
Tom
Not everybody is saying you should be using UDFs.[<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><br />In a query, use UDFs only in the column list, or for returning a table in the FROM clause. As soon as you put them in a WHERE clause, the whole table will be scanned in order to evaluate the UDF for each row.
Yes I do agree. However for this project we are converting inline code to UDF’s to centralize a security function. After running the execution plan i am noticing now that the Multistatement UDF is using a BOOKMARK LOOKUP to execute the query and the Inline UDF is using a TABLE SCAN. I am just looking to know if anyone can spot something out to the difference in performance.. Here is the SQL statement used in the Stored Procedure: Select * from table A INNER JOIN UDF_INLINE-Function() B on A.Office = B.Office Select * from table A INNER JOIN UDF_MULTI-Function(‘1’) B on A.Office = B.Office
Here is the a code snippet for each UDF: INLINE:
############################################################
RETURNS TABLE RETURN
(Select Office FROM TABLE C WHERE Area = ‘N’)
############################################################
MULTISTATEMENT:
#############################################################
RETURNS @ReturnTable TABLE ([Office] [varchar] (20)) If @Function = ‘1’
Begin
Insert @ReturnTable
Select Office FROM TABLE C WHERE Area = ‘N’
End
##############################################################
Use the version with the table definition, but add a PRIMARY KEY constraint to the definition.
]]>