記一次SQL調優過程
環境:Microsoft SQL Server 2016 (SP2-CU3)企業版
問題SQL:
select RowNumber = ROW_NUMBER() OVER ( -- This ordering is from the various Fulfillment Map sort orders to match the fulfillment app's row order. ORDER BY htly.LicenseYear, mht.Name, h.HuntFirstOpenDate, h.DisplayOrder, h.HuntCode, ci_orderby.LastName, ci_orderby.FirstName, fmu.FulfillmentMailingUnitID ), ShippingName = ISNULL(fism_aot.ShippingName, dbo.udf_GetCustomerName(c.CustomerID)), FulfillmentMailingUnitID = fmu.FulfillmentMailingUnitID, GoID = goid.IdentityValue, MailingZip = ISNULL(fism_zc.ZipCode, zc.ZipCode), TransactionID = fism_th.TransactionID, TransactionHeaderID = fism_th.TransactionHeaderID, HuntDate = h.HuntFirstOpenDate, HuntCode = h.HuntCode, -- Header info BatchNumber = fmulg.FulfillmentMailingUnitLockGroupID, PrintedByUserName = au.UserName, LockedDate = fmulg.LockedDate from dbo.FulfillmentMailingUnitLockGroup fmulg cross join dbo.Enum_IdentityType eit cross join dbo.Enum_LicenseActionType elat inner join dbo.FulfillmentMailingUnitLock fmul on fmulg.FulfillmentMailingUnitLockGroupID = fmul.FulfillmentMailingUnitLockGroupID inner join dbo.FulfillmentMailingUnit fmu on fmul.LockedFulfillmentMailingUnitID = fmu.FulfillmentMailingUnitID inner join dbo.ApplicationUser au on fmulg.LockedByApplicationUserID = au.ApplicationUserID -- Getting to the Transaction Header by FulfillmentInternetSalesMap OR FulfillmentDrawIssuanceMap left join dbo.FulfillmentInternetSalesMap fism on fmu.FulfillmentMailingUnitID = fism.FulfillmentMailingUnitID left join dbo.FulfillmentDrawIssuanceMap fdim on fmu.FulfillmentMailingUnitID = fdim.FulfillmentMailingUnitID left join dbo.TransactionHeader th on fism.TransactionHeaderID = th.TransactionHeaderID or fdim.TransactionHeaderID = th.TransactionHeaderID left join dbo.TransactionHeader fdim_th on fdim.TransactionHeaderID = fdim_th.TransactionHeaderID -- Getting to License from FulfillmentDrawNotificationMap left join dbo.FulfillmentDrawNotificationMap fdnm on fmu.FulfillmentMailingUnitID = fdnm.FulfillmentMailingUnitID left join dbo.DrawTicketLicense fdnm_dtl on fdnm.DrawTicketLicenseID = fdnm_dtl.DrawTicketLicenseID left join dbo.License fdnm_l on fdnm_dtl.LicenseID = fdnm_l.LicenseID left join dbo.DrawTicket fdnm_dt on fdnm_dtl.DrawTicketID = fdnm_dt.DrawTicketID left join dbo.DrawTicketHuntChoice fdnm_dthc on fdnm_dt.DrawTicketID = fdnm_dthc.DrawTicketID and ( -- If the draw ticket is a winner, link to the hunt choice that won. (fdnm_dt.WasDrawn = 1 and fdnm_dthc.WasDrawn = 1) -- Else if the draw ticket was not a winner, link to the first hunt choice since -- Losing and Alternate notifications are not valid for multi-choice hunts or (fdnm_dt.WasDrawn = 0 and fdnm_dthc.OrderIndex = 1) ) left join dbo.TransactionDetail fdim_td on fdim.TransactionHeaderID = fdim_td.TransactionHeaderID left join dbo.LicenseAction fdim_la on fdim_td.TransactionDetailID = fdim_la.TransactionDetailID -- This might be silly since it should only be Issued for issuance... (currently it's sold in the stored proc that issues tags) and (fdim_la.LicenseActionTypeID = elat.Sold or fdim_la.LicenseActionTypeID = elat.Issued or fdim_la.LicenseActionTypeID = elat.Duplicated) left join dbo.License fdim_l on fdim_la.LicenseID = fdim_l.LicenseID left join dbo.Hunt h on fdnm_dthc.HuntID = h.HuntID or fdim_l.HuntID = h.HuntID left join dbo.HuntTypeLicenseYear htly on h.HuntTypeLicenseYearID = htly.HuntTypeLicenseYearID left join dbo.MasterHuntType mht on htly.MasterHuntTypeID = mht.MasterHuntTypeID left join dbo.Customer c on fdnm_l.CustomerID = c.CustomerID or th.CustomerID = c.CustomerID left join dbo.CustomerIndividual ci on c.CustomerID = ci.CustomerID left join dbo.CustomerIdentity goid on c.CustomerID = goid.CustomerID and goid.IdentityTypeID = eit.GOID and goid.[Status] = 1 left join dbo.AddressDetail ad on c.MailingAddressID = ad.AddressID and ad.IsActive = 1 left join dbo.ZipCode zc on ad.ZipCodeID = zc.ZipCodeID left join dbo.CustomerIndividual ci_orderby on fdnm_l.CustomerID = ci_orderby.CustomerID or fdim_th.CustomerID = ci_orderby.CustomerID left join dbo.TransactionHeader fism_th on fism.TransactionHeaderID = fism_th.TransactionHeaderID left join dbo.ActiveOutdoorsTransaction fism_aot on fism_aot.TransactionID = fism_th.TransactionID left join dbo.AddressDetail fism_ad on fism_aot.ShippingAddressID = fism_ad.AddressID and fism_ad.IsActive = 1 left join dbo.ZipCode fism_zc on fism_ad.ZipCodeID = fism_zc.ZipCodeID where fmulg.FulfillmentMailingUnitLockGroupID = @FulfillmentMailingUnitLockGroupID
該SQL執行192s後出記錄,分析一下sql的執行計劃:
分析一:
最終的排序消耗了大量的cost:
分析二:
該SQL存在大量多表連線,MSSQL引擎由於統計資訊的演算法單一,在處理大量級聯連線時,實際資料可能嚴重偏離統計資訊
連線中存在Actual Rows和Estimated Rows嚴重不一致的情況,隨著連線表數目增加,該不一致更加嚴重:
經過分析,最佳化的目標是減少多表連線的統計資訊不一致導致的執行計劃錯誤並且對最終的排序操作進行外推。
最佳化的手法主要是利用臨時表固化統計資訊,外推排序:
最終最佳化SQL:
select fmu.FulfillmentMailingUnitID ,elat.Sold ,elat.Issued ,elat.Duplicated ,fmulg.FulfillmentMailingUnitLockGroupID ,au.UserName ,fmulg.LockedDate ,eit.GOID into #temp from dbo.FulfillmentMailingUnitLockGroup fmulg cross join dbo.Enum_IdentityType eit cross join dbo.Enum_LicenseActionType elat inner join dbo.FulfillmentMailingUnitLock fmul on fmulg.FulfillmentMailingUnitLockGroupID = fmul.FulfillmentMailingUnitLockGroupID inner join dbo.FulfillmentMailingUnit fmu on fmul.LockedFulfillmentMailingUnitID = fmu.FulfillmentMailingUnitID inner join dbo.ApplicationUser au on fmulg.LockedByApplicationUserID = au.ApplicationUserID where fmulg.FulfillmentMailingUnitLockGroupID = @FulfillmentMailingUnitLockGroupID select fdnm_l.CustomerID fdnm_l_CustomerID, th.CustomerID th_CustomerID, fdim_th.CustomerID fdim_th_CustomerID, t.FulfillmentMailingUnitID, h.HuntFirstOpenDate, h.HuntCode, t.FulfillmentMailingUnitLockGroupID, t.UserName, LockedDate, t.GOID, htly.LicenseYear, mht.Name, h.DisplayOrder, --ci_orderby.LastName, --ci_orderby.FirstName, fism.TransactionHeaderID into #temp1 from #temp t -- Getting to the Transaction Header by FulfillmentInternetSalesMap OR FulfillmentDrawIssuanceMap left join dbo.FulfillmentInternetSalesMap fism on t.FulfillmentMailingUnitID = fism.FulfillmentMailingUnitID left join dbo.FulfillmentDrawIssuanceMap fdim on t.FulfillmentMailingUnitID = fdim.FulfillmentMailingUnitID left join dbo.TransactionHeader th on fism.TransactionHeaderID = th.TransactionHeaderID or fdim.TransactionHeaderID = th.TransactionHeaderID left join dbo.TransactionHeader fdim_th on fdim.TransactionHeaderID = fdim_th.TransactionHeaderID -- Getting to License from FulfillmentDrawNotificationMap left join dbo.FulfillmentDrawNotificationMap fdnm on t.FulfillmentMailingUnitID = fdnm.FulfillmentMailingUnitID left join dbo.DrawTicketLicense fdnm_dtl on fdnm.DrawTicketLicenseID = fdnm_dtl.DrawTicketLicenseID left join dbo.License fdnm_l on fdnm_dtl.LicenseID = fdnm_l.LicenseID left join dbo.DrawTicket fdnm_dt on fdnm_dtl.DrawTicketID = fdnm_dt.DrawTicketID left join dbo.DrawTicketHuntChoice fdnm_dthc on fdnm_dt.DrawTicketID = fdnm_dthc.DrawTicketID and ( -- If the draw ticket is a winner, link to the hunt choice that won. (fdnm_dt.WasDrawn = 1 and fdnm_dthc.WasDrawn = 1) -- Else if the draw ticket was not a winner, link to the first hunt choice since -- Losing and Alternate notifications are not valid for multi-choice hunts or (fdnm_dt.WasDrawn = 0 and fdnm_dthc.OrderIndex = 1) ) left join dbo.TransactionDetail fdim_td on fdim.TransactionHeaderID = fdim_td.TransactionHeaderID left join dbo.LicenseAction fdim_la on fdim_td.TransactionDetailID = fdim_la.TransactionDetailID -- This might be silly since it should only be Issued for issuance... (currently it's sold in the stored proc that issues tags) and (fdim_la.LicenseActionTypeID = t.Sold or fdim_la.LicenseActionTypeID = t.Issued or fdim_la.LicenseActionTypeID = t.Duplicated) left join dbo.License fdim_l on fdim_la.LicenseID = fdim_l.LicenseID left join dbo.Hunt h on fdnm_dthc.HuntID = h.HuntID or fdim_l.HuntID = h.HuntID left join dbo.HuntTypeLicenseYear htly on h.HuntTypeLicenseYearID = htly.HuntTypeLicenseYearID left join dbo.MasterHuntType mht on htly.MasterHuntTypeID = mht.MasterHuntTypeID --set statistics io on --set statistics time on select t1.LicenseYear, t1.Name, t1.DisplayOrder, c.CustomerID, t1.FulfillmentMailingUnitID, t1.GOID, zc.ZipCode, t1.HuntFirstOpenDate, t1.HuntCode, t1.FulfillmentMailingUnitLockGroupID, t1.UserName, t1.LockedDate, t1.fdnm_l_CustomerID, t1.fdim_th_CustomerID, t1.TransactionHeaderID into #temp2 from #temp1 t1 -- Getting to Cusotmer from the joined transaction header or the license from the DrawTicketLicense left join dbo.Customer c on t1.fdnm_l_CustomerID = c.CustomerID or t1.th_CustomerID = c.CustomerID left join dbo.CustomerIndividual ci on c.CustomerID = ci.CustomerID left join dbo.AddressDetail ad on c.MailingAddressID = ad.AddressID and ad.IsActive = 1 left join dbo.ZipCode zc on ad.ZipCodeID = zc.ZipCodeID select t2.LicenseYear, t2.Name, t2.DisplayOrder, ci_orderby.LastName, ci_orderby.FirstName, ShippingName = ISNULL(fism_aot.ShippingName, dbo.udf_GetCustomerName(t2.CustomerID)), FulfillmentMailingUnitID = t2.FulfillmentMailingUnitID, GoID = goid.IdentityValue, MailingZip = ISNULL(fism_zc.ZipCode, t2.ZipCode), TransactionID = fism_th.TransactionID, TransactionHeaderID = fism_th.TransactionHeaderID, HuntDate = t2.HuntFirstOpenDate, HuntCode = t2.HuntCode, -- Header info BatchNumber = t2.FulfillmentMailingUnitLockGroupID, PrintedByUserName = t2.UserName, LockedDate = t2.LockedDate into #temp3 from #temp2 t2 left join dbo.CustomerIdentity goid on t2.CustomerID = goid.CustomerID and goid.IdentityTypeID = t2.GOID and goid.[Status] = 1 left join dbo.CustomerIndividual ci_orderby on t2.fdnm_l_CustomerID = ci_orderby.CustomerID or t2.fdim_th_CustomerID = ci_orderby.CustomerID left join dbo.TransactionHeader fism_th on t2.TransactionHeaderID = fism_th.TransactionHeaderID left join dbo.ActiveOutdoorsTransaction fism_aot on fism_aot.TransactionID = fism_th.TransactionID left join dbo.AddressDetail fism_ad on fism_aot.ShippingAddressID = fism_ad.AddressID and fism_ad.IsActive = 1 left join dbo.ZipCode fism_zc on fism_ad.ZipCodeID = fism_zc.ZipCodeID select RowNumber = ROW_NUMBER() OVER ( -- This ordering is from the various Fulfillment Map sort orders to match the fulfillment app's row order. ORDER BY t3.LicenseYear, t3.Name, t3.HuntDate, t3.DisplayOrder, t3.HuntCode, t3.LastName, t3.FirstName, t3.FulfillmentMailingUnitID ), ShippingName, FulfillmentMailingUnitID, GoID, MailingZip, TransactionID, TransactionHeaderID, HuntDate, HuntCode, -- Header info BatchNumber, PrintedByUserName, LockedDate from #temp3 t3 drop table #temp drop table #temp1 drop table #temp2 drop table #temp3
經過測試,執行時間由192秒降低到2秒。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31552801/viewspace-2659403/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 記一次分割槽表update調優過程
- 記一次SQL Server刪除SQL調優SQLServer
- SQL Server一次SQL調優案例SQLServer
- 記一次 500併發,平均響應時間慢-調優過程~~
- 一次 kafka 消費者的效能調優過程Kafka
- 記一次效能調優
- 記一次"截圖"功能的專案調研過程!
- go dns解析過程及調優GoDNS
- 記一次 Istio 衝刺調優
- Mysql之一次完成的sql執行過程MySql
- SQL Server儲存過程的優缺點SQLServer儲存過程
- 一次SQL調優 聊一聊 SQLSERVER 資料頁SQLServer
- 解Bug之路-記一次中介軟體導致的慢SQL排查過程SQL
- 記一次"記憶體洩露"排查過程記憶體洩露
- 記一次OOM問題排查過程OOM
- 記一次 Boomer 壓測 MQTT 過程OOMMQQT
- 記一次ElementUI原始碼修改過程UI原始碼
- 記一次系統演變過程
- MySQL 記一次 Bug發現過程MySql
- 記一次 GitLab 的遷移過程Gitlab
- 記一次我的 MySQL 調優經歷MySql
- JVM 效能調優實戰之:一次系統效能瓶頸的尋找過程JVM
- MySQL調優篇 | SQL調優實戰(5)MySql
- 記錄一次記憶體洩漏排查過程記憶體
- 記一次nodejs開發CLI的過程NodeJS
- 記一次前端面試的全過程前端面試
- 記一次安卓webview查錯過程安卓WebView
- 記錄一次Dataguard的修復過程
- 記一次使用 AetherUpload 影片上傳過程
- 記一次 Laravel-Admin 的 Debug 過程Laravel
- 記一次https通訊除錯過程HTTP除錯
- 記一次ceph pg unfound處理過程
- 記一次jvm調優及垃圾收集器JVM
- 分享工作中一次優化程式的過程優化
- 記一次堆外記憶體洩漏排查過程記憶體
- 神通資料庫測試環境調優過程資料庫
- 記一次全民K歌的crash定位過程
- 記一次 Composer 問題的解決過程!!