欧美一区二区三区老妇人-欧美做爰猛烈大尺度电-99久久夜色精品国产亚洲a-亚洲福利视频一区二区

SQLServer中怎么改寫(xiě)內(nèi)聯(lián)表值函數(shù)-創(chuàng)新互聯(lián)

這篇文章主要講解了“SQL Server中怎么改寫(xiě)內(nèi)聯(lián)表值函數(shù)”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“SQL Server中怎么改寫(xiě)內(nèi)聯(lián)表值函數(shù)”吧！

公司主營(yíng)業(yè)務(wù)：網(wǎng)站制作、成都做網(wǎng)站、移動(dòng)網(wǎng)站開(kāi)發(fā)等業(yè)務(wù)。幫助企業(yè)客戶(hù)真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競(jìng)爭(zhēng)能力。成都創(chuàng)新互聯(lián)是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開(kāi)放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對(duì)我們的高要求，感謝他們從不同領(lǐng)域給我們帶來(lái)的挑戰(zhàn)，讓我們激情的團(tuán)隊(duì)有機(jī)會(huì)用頭腦與智慧不斷的給客戶(hù)帶來(lái)驚喜。成都創(chuàng)新互聯(lián)推出洞頭免費(fèi)做網(wǎng)站回饋大家。

問(wèn)題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ù)改寫(xiě)內(nèi)聯(lián)表值函數(shù)的sql：

改寫(xiě)后：

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

改寫(xiě)后執(zhí)行時(shí)間從40s降低到16s，對(duì)于傾斜列的優(yōu)化速度更為明顯

感謝各位的閱讀，以上就是“SQL Server中怎么改寫(xiě)內(nèi)聯(lián)表值函數(shù)”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)SQL Server中怎么改寫(xiě)內(nèi)聯(lián)表值函數(shù)這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！

分享標(biāo)題：SQLServer中怎么改寫(xiě)內(nèi)聯(lián)表值函數(shù)-創(chuàng)新互聯(lián)
轉(zhuǎn)載來(lái)于：http://chinadenli.net/article14/digode.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供App開(kāi)發(fā)、營(yíng)銷(xiāo)型網(wǎng)站建設(shè)、用戶(hù)體驗(yàn)、定制網(wǎng)站、微信公眾號(hào)、網(wǎng)站營(yíng)銷(xiāo)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)

h5響應(yīng)式網(wǎng)站建設(shè)