环境: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秒。
