Dec 27, 2014

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. 

No comments:

Post a Comment