Table valued function query performance Sql server

Table valued function performance, optimization and tuning

Some developers are not aware of table valued function some of developers know about it but not able to use appropriate time. If there is need to join data from one table with part of data from another table in that case derived table may not perform better.  For example: There is business need to get the data of all users with their most recent post. There are many way to write the script to get that information:

  

Using derived table and ranking function:

SELECT U.*

     ,P.ntPostID

     ,P.xlPostData

     ,P.dtPostDate

FROM tblUser U

INNER JOIN (

     SELECT *

          ,ROW_NUMBER() OVER (

              PARTITION BY ntUserID ORDER BY dtPostDate DESC

              ) AS ntRowID

     FROM tblPost

     ) P ON U.ntUserID = P.ntUserID

WHERE ntRowID = 1

Execution Time: 26 Seconds

Using Table valued function:

CREATE FUNCTION udfGetMostRecentPost (@ntUserID BIGINT)

RETURNS TABLE

AS

RETURN

SELECT TOP (1) *

FROM tblPost

WHERE ntUserID = @ntUserID

ORDER BY dtPostDate DESC

SELECT U.*

     ,P.xlPostData

     ,P.dtPostDate

FROM tblUser U

CROSS APPLY dbo.udfGetMostRecentPost(U.ntUserID) P

Execution Time: 8 Seconds

Query cost comparison:

 

table valued function performnace analysis

So it is obvious in this example table valued function performing much better.