sql-server - 存储过程中的更新查询需要大量时间
问题描述
这适用于 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
就最终结果而言,查询工作正常。只是要花很多时间。任何帮助将不胜感激。
解决方案
您是否尝试过对存储过程使用重新编译选项?如果您的存储过程使用非常小的数据集生成查询计划,则您的计划可能会强制该过程遍历非常大的表,因为原始查询计划确定遍历表是收集或更新数据的最有效方法。这可能无法完全解决您的问题,但它是另一种选择。此外,该建议是开始确定低效步骤的好地方。
推荐阅读
- javascript - 对于选择器中的循环数据 - React Native
- c# - 如果数据在调用后到达,为什么 .NET SerialPort ReadByte() 会阻塞完全超时
- c - 单个 printf() 函数是否可以在循环中从 getchar() 获取输入时执行多次?
- r - 如果语句不适用于矩阵条件
- javascript - 使用 Swapi 获取数组对象的数据值
- arrays - How to count items in arrays of values in an array of hashes
- shell - Append from nth to nth index of a file with another file
- gnuplot - gnuplot: make only one border thicker in a surface plot
- javascript - React Native 中的 Fetch() api 失败 - “未处理的承诺拒绝”
- c++ - 如何在 Visual Studio 2017 中启动 cocos2d-x-3.17 的新 android 游戏项目?