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:
So it is obvious in this example table valued
function performing much better.
No comments:
Post a Comment