SQL Server中怎么改写内联表值函数

这篇文章主要讲解了"SQL Server中怎么改写内联表值函数"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"SQL Server中怎么改写内联表值函数"吧！

问题SQL：

SELECT TOP 1001 ha.HuntApplicationID ,           ha.PartyNumber ,           mht.Name AS MasterHuntTypeName ,           htly.LicenseYear ,           lStatus.[Status] AS DrawTicketStatus ,           isnull(dbo.udf_GetHuntApplicationPartyCount(ha.HuntApplicationID), 0) AS MemberCount ,           count( won.DrawTicketLicenseID) AS DrawnMemberCount ,           won.drawticketid ,           dt.PreDrawNonResidentMemberCount AS NRMemberCount ,           dbo.udf_GetAvgPreferencePoints(dt.DrawTicketID) AS PreferencePointAverage ,           CASE               WHEN ha.Quantity > 1 THEN NULL               ELSE dt.PreDrawRandomNumber           END AS PreDrawRandomNumber ,           dsm.Name AS DrawSelectionMethodName ,           dt.DrawnSequence ,           dt.PreferencePointRank ,           dt.DrawID ,           dt.RandomRankFROM dbo.HuntApplication haJOIN dbo.HuntTypeLicenseYear htly ON ha.HuntTypeLicenseYearID = htly.HuntTypeLicenseYearIDJOIN dbo.MasterHuntType mht ON htly.MasterHuntTypeID = mht.MasterHuntTypeIDLEFT JOIN dbo.HuntApplicationLicense hal ON ha.HuntApplicationID = hal.HuntApplicationIDLEFT JOIN dbo.DrawTicket dt ON ha.HuntApplicationID = dt.HuntApplicationIDLEFT JOIN dbo.DrawTicketLicense won ON dt.DrawTicketID = won.DrawTicketIDAND won.WasDrawn = 1LEFT JOIN dbo.DrawSelectionMethod dsm ON dt.DrawSelectionMethodID = dsm.DrawSelectionMethodIDLEFT JOIN dbo.StatusCode lStatus ON dt.StatusCodeID = lStatus.StatusCodeIDJOIN dbo.DrawTicketHuntChoice dthc ON dt.DrawTicketID = dthc.DrawTicketIDCROSS APPLY dbo.tvf_GetHuntApplicationPartyCount(ha.HuntApplicationID) hapcCROSS APPLY dbo.tvf_GetAvgPreferencePoints(dt.DrawTicketID) appWHERE 1 = 1  AND htly.MasterHuntTypeID = @iMasterHuntTypeID  AND htly.LicenseYear = @iLicenseYear  AND dt.StatusCodeID = @iDrawTicketStatusCodeID  AND dthc.WasDrawn = @iHuntChoiceWasDrawnGROUP BY ha.HuntApplicationID,         ha.PartyNumber,         mht.[Name],         htly.LicenseYear,         lStatus.[Status],         isnull(dbo.udf_GetHuntApplicationPartyCount(ha.HuntApplicationID), 0),         won.DrawTicketID,         dt.PreDrawNonResidentMemberCount,         dbo.udf_GetAvgPreferencePoints(dt.DrawTicketID),         CASE             WHEN ha.Quantity > 1 THEN NULL             ELSE dt.PreDrawRandomNumber         END,         dsm.[Name],         dt.DrawnSequence,         dt.PreferencePointRank,         dt.DrawID,         dt.RandomRankORDER BY htly.LicenseYear DESC,         mht.Name,         lStatus.[Status],         dt.DrawID,         PreferencePointAverage DESC,         PreDrawRandomNumber,         ha.PartyNumber

静态函数：

CREATE FUNCTION [dbo].[udf_GetAvgPreferencePoints](@DrawTicketID INT)RETURNS NUMERIC (18, 3)ASBEGIN    RETURN        (                SELECT TOP 1                                CONVERT(DECIMAL, dt.PreDrawPreferencePointTotal) / NULLIF(CONVERT(DECIMAL, dt.PreDrawMemberCount),0)                FROM dbo.DrawTicket dt                        WHERE dt.DrawTicketID = @DrawTicketID        )END

执行时间40s

这是典型可以进行静态函数改写内联表值函数的sql：

改写后：

SELECT TOP 1001 ha.HuntApplicationID ,           ha.PartyNumber ,           mht.Name AS MasterHuntTypeName ,           htly.LicenseYear ,           lStatus.[Status] AS DrawTicketStatus ,           --isnull(dbo.udf_GetHuntApplicationPartyCount(ha.HuntApplicationID), 0) AS MemberCount ,                   isnull(hapc.MemberCount, 0) AS MemberCount,           count( won.DrawTicketLicenseID) AS DrawnMemberCount ,           won.drawticketid ,           dt.PreDrawNonResidentMemberCount AS NRMemberCount ,           --dbo.udf_GetAvgPreferencePoints(dt.DrawTicketID) AS PreferencePointAverage ,                   app.PreferencePointAverage PreferencePointAverage,           CASE               WHEN ha.Quantity > 1 THEN NULL               ELSE dt.PreDrawRandomNumber           END AS PreDrawRandomNumber ,           dsm.Name AS DrawSelectionMethodName ,           dt.DrawnSequence ,           dt.PreferencePointRank ,           dt.DrawID ,           dt.RandomRankFROM dbo.HuntApplication haJOIN dbo.HuntTypeLicenseYear htly ON ha.HuntTypeLicenseYearID = htly.HuntTypeLicenseYearIDJOIN dbo.MasterHuntType mht ON htly.MasterHuntTypeID = mht.MasterHuntTypeIDLEFT JOIN dbo.HuntApplicationLicense hal ON ha.HuntApplicationID = hal.HuntApplicationIDLEFT JOIN dbo.DrawTicket dt ON ha.HuntApplicationID = dt.HuntApplicationIDLEFT JOIN dbo.DrawTicketLicense won ON dt.DrawTicketID = won.DrawTicketIDAND won.WasDrawn = 1LEFT JOIN dbo.DrawSelectionMethod dsm ON dt.DrawSelectionMethodID = dsm.DrawSelectionMethodIDLEFT JOIN dbo.StatusCode lStatus ON dt.StatusCodeID = lStatus.StatusCodeIDJOIN dbo.DrawTicketHuntChoice dthc ON dt.DrawTicketID = dthc.DrawTicketIDCROSS APPLY dbo.tvf_GetHuntApplicationPartyCount(ha.HuntApplicationID) hapcCROSS APPLY dbo.tvf_GetAvgPreferencePoints(dt.DrawTicketID) appWHERE 1 = 1  AND htly.MasterHuntTypeID = @iMasterHuntTypeID  AND htly.LicenseYear = @iLicenseYear  AND dt.StatusCodeID = @iDrawTicketStatusCodeID  AND dthc.WasDrawn = @iHuntChoiceWasDrawnGROUP BY ha.HuntApplicationID,         ha.PartyNumber,         mht.[Name],         htly.LicenseYear,         lStatus.[Status],         --isnull(dbo.udf_GetHuntApplicationPartyCount(ha.HuntApplicationID), 0),                 isnull(hapc.MemberCount, 0),         won.DrawTicketID,         dt.PreDrawNonResidentMemberCount,         --dbo.udf_GetAvgPreferencePoints(dt.DrawTicketID),                 app.PreferencePointAverage,         CASE             WHEN ha.Quantity > 1 THEN NULL             ELSE dt.PreDrawRandomNumber         END,         dsm.[Name],         dt.DrawnSequence,         dt.PreferencePointRank,         dt.DrawID,         dt.RandomRankORDER BY htly.LicenseYear DESC,         mht.Name,         lStatus.[Status],         dt.DrawID,         PreferencePointAverage DESC,         PreDrawRandomNumber,         ha.PartyNumber

对应的表值函数：

CREATE FUNCTION [dbo].[tvf_GetAvgPreferencePoints](@DrawTicketID INT)RETURNS TABLE WITH SCHEMABINDINGASRETURN(        SELECT TOP 1                        CONVERT(DECIMAL, dt.PreDrawPreferencePointTotal) / NULLIF(CONVERT(DECIMAL, dt.PreDrawMemberCount),0) as PreferencePointAverage        FROM dbo.DrawTicket dt                WHERE dt.DrawTicketID = @DrawTicketID)GO

改写后执行时间从40s降低到16s，对于倾斜列的优化速度更为明显

感谢各位的阅读，以上就是"SQL Server中怎么改写内联表值函数"的内容了，经过本文的学习后，相信大家对SQL Server中怎么改写内联表值函数这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！