記一次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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 一次效能優化調整過程.優化
- 記一次SQL Server刪除SQL調優SQLServer
- SQL Server一次SQL調優案例SQLServer
- 一次 kafka 消費者的效能調優過程Kafka
- 偶發的儲存過程呼叫效能問題的一次調優過程儲存過程
- 記一次記憶體佔用問題的調查過程記憶體
- 記一次"截圖"功能的專案調研過程!
- 記一次sql優化SQL優化
- go dns解析過程及調優GoDNS
- 一次 JVM 調優的筆記JVM筆記
- 一次sql優化小記SQL優化
- 一次SQL Performance Analyzer的使用過程SQLORM
- 一條sql的優化過程SQL優化
- 一次捕獲SQL調優資訊的指令碼 sql9.sqlSQL指令碼
- 一次捕獲SQL調優資訊的指令碼 sql10.sqlSQL指令碼
- OCP課程56:管理II之SQL調優SQL
- SQL Server效能調優札記 [zt]SQLServer
- 通過ADDM進行SQL調優SQL
- 透過ADDM進行SQL調優SQL
- SQL調優SQL
- 用dbms_profiler調優儲存過程儲存過程
- ORACLE sql 語句的執行過程(SQL效能調整)OracleSQL
- 記一次我的 MySQL 調優經歷MySql
- python效能調優的一次記錄Python
- 記一次"記憶體洩露"排查過程記憶體洩露
- 記一次系統演變過程
- 記一次OOM問題排查過程OOM
- 記一次ElementUI原始碼修改過程UI原始碼
- MySQL 記一次 Bug發現過程MySql
- 記一次安卓webview查錯過程安卓WebView
- JVM 效能調優實戰之:一次系統效能瓶頸的尋找過程JVM
- 一次SQL調優 聊一聊 SQLSERVER 資料頁SQLServer
- 透過使用hint unnest調優sql語句SQL
- 通過使用hint unnest調優sql語句SQL
- sql調優學習之cpu消耗過多...SQL
- 隱式型別轉換(SYS_OP_C2C)-記一次SQL調優型別SQL
- oracle筆記整理13——效能調優之SQL優化Oracle筆記SQL優化
- 解Bug之路-記一次中介軟體導致的慢SQL排查過程SQL