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