這篇文章主要講解了“SQL Server中怎么改寫內(nèi)聯(lián)表值函數(shù)”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“SQL Server中怎么改寫內(nèi)聯(lián)表值函數(shù)”吧！
問題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.RandomRank FROM dbo.HuntApplication ha JOIN dbo.HuntTypeLicenseYear htly ON ha.HuntTypeLicenseYearID = htly.HuntTypeLicenseYearID JOIN dbo.MasterHuntType mht ON htly.MasterHuntTypeID = mht.MasterHuntTypeID LEFT JOIN dbo.HuntApplicationLicense hal ON ha.HuntApplicationID = hal.HuntApplicationID LEFT JOIN dbo.DrawTicket dt ON ha.HuntApplicationID = dt.HuntApplicationID LEFT JOIN dbo.DrawTicketLicense won ON dt.DrawTicketID = won.DrawTicketID AND won.WasDrawn = 1 LEFT JOIN dbo.DrawSelectionMethod dsm ON dt.DrawSelectionMethodID = dsm.DrawSelectionMethodID LEFT JOIN dbo.StatusCode lStatus ON dt.StatusCodeID = lStatus.StatusCodeID JOIN dbo.DrawTicketHuntChoice dthc ON dt.DrawTicketID = dthc.DrawTicketID CROSS APPLY dbo.tvf_GetHuntApplicationPartyCount(ha.HuntApplicationID) hapc CROSS APPLY dbo.tvf_GetAvgPreferencePoints(dt.DrawTicketID) app WHERE 1 = 1 AND htly.MasterHuntTypeID = @iMasterHuntTypeID AND htly.LicenseYear = @iLicenseYear AND dt.StatusCodeID = @iDrawTicketStatusCodeID AND dthc.WasDrawn = @iHuntChoiceWasDrawn GROUP 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.RandomRank ORDER BY htly.LicenseYear DESC, mht.Name, lStatus.[Status], dt.DrawID, PreferencePointAverage DESC, PreDrawRandomNumber, ha.PartyNumber
靜態(tài)函數(shù)：
CREATE FUNCTION [dbo].[udf_GetAvgPreferencePoints] (@DrawTicketID INT) RETURNS NUMERIC (18, 3) AS BEGIN RETURN ( SELECT TOP 1 CONVERT(DECIMAL, dt.PreDrawPreferencePointTotal) / NULLIF(CONVERT(DECIMAL, dt.PreDrawMemberCount),0) FROM dbo.DrawTicket dt WHERE dt.DrawTicketID = @DrawTicketID ) END
執(zhí)行時(shí)間40s
這是典型可以進(jìn)行靜態(tài)函數(shù)改寫內(nèi)聯(lián)表值函數(shù)的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.RandomRank FROM dbo.HuntApplication ha JOIN dbo.HuntTypeLicenseYear htly ON ha.HuntTypeLicenseYearID = htly.HuntTypeLicenseYearID JOIN dbo.MasterHuntType mht ON htly.MasterHuntTypeID = mht.MasterHuntTypeID LEFT JOIN dbo.HuntApplicationLicense hal ON ha.HuntApplicationID = hal.HuntApplicationID LEFT JOIN dbo.DrawTicket dt ON ha.HuntApplicationID = dt.HuntApplicationID LEFT JOIN dbo.DrawTicketLicense won ON dt.DrawTicketID = won.DrawTicketID AND won.WasDrawn = 1 LEFT JOIN dbo.DrawSelectionMethod dsm ON dt.DrawSelectionMethodID = dsm.DrawSelectionMethodID LEFT JOIN dbo.StatusCode lStatus ON dt.StatusCodeID = lStatus.StatusCodeID JOIN dbo.DrawTicketHuntChoice dthc ON dt.DrawTicketID = dthc.DrawTicketID CROSS APPLY dbo.tvf_GetHuntApplicationPartyCount(ha.HuntApplicationID) hapc CROSS APPLY dbo.tvf_GetAvgPreferencePoints(dt.DrawTicketID) app WHERE 1 = 1 AND htly.MasterHuntTypeID = @iMasterHuntTypeID AND htly.LicenseYear = @iLicenseYear AND dt.StatusCodeID = @iDrawTicketStatusCodeID AND dthc.WasDrawn = @iHuntChoiceWasDrawn GROUP 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.RandomRank ORDER BY htly.LicenseYear DESC, mht.Name, lStatus.[Status], dt.DrawID, PreferencePointAverage DESC, PreDrawRandomNumber, ha.PartyNumber
對(duì)應(yīng)的表值函數(shù)：
CREATE FUNCTION [dbo].[tvf_GetAvgPreferencePoints] (@DrawTicketID INT) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT TOP 1 CONVERT(DECIMAL, dt.PreDrawPreferencePointTotal) / NULLIF(CONVERT(DECIMAL, dt.PreDrawMemberCount),0) as PreferencePointAverage FROM dbo.DrawTicket dt WHERE dt.DrawTicketID = @DrawTicketID ) GO
改寫后執(zhí)行時(shí)間從40s降低到16s，對(duì)于傾斜列的優(yōu)化速度更為明顯
感謝各位的閱讀，以上就是“SQL Server中怎么改寫內(nèi)聯(lián)表值函數(shù)”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對(duì)SQL Server中怎么改寫內(nèi)聯(lián)表值函數(shù)這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！
重慶分公司
重慶分公司
