这是针对SQL Server 2012的。我被要求优化存储过程,该存储过程用于计算各种发票的账单金额。 这是一个巨大的存储过程,具有多个插入,更新和删除查询,以及对多个表的联接。 存储过程在此特定更新查询上停留了8-9个小时。查询正在更新约400000条记录。
在执行存储过程时,除运行存储过程的连接外,与数据库没有其他连接。
The query is
UPDATE D
SET Amount=CASE WHEN D.Container='PCS' AND DP.AmountPerStop =0 THEN DP1.Amount*IsnULL(IsnULL(M2.MPDFactor,M.MPDFactor),1) ELSE DP1.Amount+DP.Amount*(DeliveryQty-1) END,InvoiceID=I.InvoiceID,MPDFactor=CASE WHEN D.Container='PCS' AND DP.AmountPerStop =0 THEN IsnULL(M2.MPDFactor,M.MPDFactor) ELSE NULL END,PickupAmountCap = DP.PickupAmountCap
FROM dbo.tDeliveries D
JOIN tDeliveryPrices DP ON DP.ProductGroupCode=D.ProductGroupCode AND DP.Container=D.Container AND DP.Zone=D.Zone
JOIN tVendorAgreements A ON A.DIP=D.DIP AND DP.VendorAgreementID=A.VendorAgreementID
JOIN tDeliveryPrices DP1 ON DP1.ProductGroupCode=D.ProductGroupCode AND DP1.Container=D.Container AND DP1.Zone=D.Zone AND DP1.VendorAgreementID=A.VendorAgreementID
JOIN tDIP DIP ON D.Dip=DIP.Dip
JOIN tInvoices I ON A.VendorAgreementID=I.VendorAgreementID
JOIN @tDailyInvoicePeriodForDIP IP ON IP.DIP = DIP.DIP
LEFT JOIN tMPDFactors M ON M.DIPArea=DIP.DipArea AND M.ProductGroupCode=D.ProductGroupCode AND D.DeliveryQty BETWEEN M.StartQty AND M.EndQty
LEFT JOIN tMPDFactors M2 ON M2.DIP=DIP.Dip AND M2.ProductGroupCode=D.ProductGroupCode AND D.DeliveryQty BETWEEN M2.StartQty AND M2.EndQty
WHERE D.InvoiceID IS NULL AND
I.InvoicePeriod= @Period AND
I.InvoiceLockedDate IS NULL AND
DP1.StartQty=1 AND
(DeliveryQty BETWEEN DP.StartQty AND DP.EndQty OR D.Container='PCS') AND
D.Event_Type = 'I' AND
@Period BETWEEN A.ValidFromDate AND A.ValidUntilDate
就最终结果而言,查询工作正常。这只花了很多时间。任何帮助将不胜感激。