Dec 27, 2014

Multi-statement vs. In line Table Valued Function performance SQL server

Query tuning best practices recommendation  

There are two types of user defined table valued function.
a. Multi-statement table valued function
b. In line table valued function

In general in line table valued function performs better than multi-statement table value function. For example:

CREATE FUNCTION udfInlineGetMostRecentPost (@ntUserID BIGINT)
RETURNS TABLE
AS
RETURN

SELECT TOP (1) *
FROM tblPost
WHERE ntUserID = @ntUserID
ORDER BY dtPostDate DESC
GO

SELECT U.*
     ,P.xlPostData
     ,P.dtPostDate
FROM tblUser U
CROSS APPLY dbo.udfInlineGetMostRecentPost(U.ntUserID) P

   
Execution Time: 8 Seconds

CREATE FUNCTION udfMultiGetMostRecentPost (@ntUserID BIGINT)
RETURNS @tblRecentPost TABLE (
     ntPostID BIGINT
     ,xlPostData XML
     ,dtPostDate DATETIME
     )
AS
BEGIN
     INSERT INTO @tblRecentPost
     SELECT TOP (1) ntPostID
          ,xlPostData
          ,dtPostDate
     FROM tblPost
     WHERE ntUserID = @ntUserID
     ORDER BY dtPostDate DESC

     RETURN
END
GO

SELECT U.*
     ,P.xlPostData
     ,P.dtPostDate
FROM tblUser U
CROSS APPLY dbo.udfMultiGetMostRecentPost(U.ntUserID) P

Execution time: 19 seconds

But it is not possible to write in line table valued function always but if it is possible then we should in line table valued function instead of multi-statement table valued function. 

No comments:

Post a Comment