时间戳

首页 > 解决方案 > SQL Server：相同的存储过程在 1 个 DB 上运行良好，但在第 2 个 DB 上运行缓慢

performance - SQL Server：相同的存储过程在 1 个 DB 上运行良好，但在第 2 个 DB 上运行缓慢

问题描述

我在 2 个数据库中有相同的存储过程。它在第一个数据库中运行良好，但在另一个数据库中需要超过 10 秒。我提取它来查询并有类似的东西。这是我的查询。专家的任何帮助将不胜感激。

SELECT     
    dbo.Installment.Id, dbo.Installment.InstallmentNo, 
    dbo.Installment.InstallmentOrder, dbo.Installment.Amount, 
    dbo.Installment.DueDate, dbo.Installment.AmountPaid, 
    dbo.Installment.PaidOn, dbo.Installment.PlotId, 
    dbo.Installment.SurchargePaid, dbo.Installment.surchargePaidOn, 
    dbo.Installment.PartialInstallmentId, dbo.Installment.Is_Lumpsum, 
    dbo.Plot.PlotNo, dbo.Plot.PhaseId, dbo.Plot.InstallmentPlanId, 
    dbo.InstallmentPlan.StartDate, dbo.InstallmentPlan.InstSurchargeDueMonth, 
    dbo.GetInstPlanDueDate(dbo.InstallmentPlan.StartDate, 
                           ISNULL(dbo.Installment.DueDate, GETDATE()), 
                           ISNULL(dbo.Installment.PaidOn, GETDATE()), 
                           dbo.InstallmentPlan.InstSurchargeDueMonth, 
                           dbo.Installment.InstallmentOrder, 
                           ISNULL(dbo.Installment.Is_Lumpsum, 0), 
                           InstallmentStartDate.DueDate, dbo.Installment.PlotId) AS Sutcharge_Start_From, 
    dbo.CalculateSurchargableDays(dbo.Installment.DueDate, 
                                  ISNULL(dbo.Installment.PaidOn, GETDATE()), 
                                  dbo.GetInstPlanDueDate(dbo.InstallmentPlan.StartDate, 
                                      ISNULL(dbo.Installment.DueDate, GETDATE()), 
                                      ISNULL(dbo.Installment.PaidOn, GETDATE()), 
                                      dbo.InstallmentPlan.InstSurchargeDueMonth, 
                                      dbo.Installment.InstallmentOrder, 
                                      ISNULL(dbo.Installment.Is_Lumpsum, 0), 
                                      InstallmentStartDate.DueDate, 
                                      dbo.Installment.PlotId), 
                        dbo.Installment.InstallmentOrder) AS days, 
                      case isnull(dbo.Installment.SurchargePaid,0) when 0 then
                  dbo.CalculateSurchargableDays(dbo.Installment.DueDate, ISNULL(dbo.Installment.PaidOn, GETDATE()), 
                  dbo.GetInstPlanDueDate(dbo.InstallmentPlan.StartDate, ISNULL(dbo.Installment.DueDate, GETDATE()), isnull(dbo.Installment.PaidOn,getdate()), dbo.InstallmentPlan.InstSurchargeDueMonth, dbo.Installment.InstallmentOrder, 
                  ISNULL(dbo.Installment.Is_Lumpsum, 0), InstallmentStartDate.DueDate, dbo.Installment.PlotId),dbo.Installment.InstallmentOrder) * (ISNULL(dbo.Installment.Amount, 0) * (dbo.InstallmentPlan.InstSurchargePercentage / 365 / 100))
                  else
                    isnull(dbo.Installment.Surcharge,0)
                  end
                  AS surcharge_calculated, case isnull(dbo.Installment.AmountPaid,0) when 0 then 0 else 0 end as Payment_Status,dbo.Installment.InstallmentOrder%6 as t


FROM           
    dbo.Installment 
INNER JOIN
    dbo.Plot ON dbo.Installment.PlotId = dbo.Plot.Id 
INNER JOIN
    dbo.InstallmentPlan ON dbo.Plot.InstallmentPlanId = dbo.InstallmentPlan.Id 
INNER JOIN
    (SELECT PlotId, MIN(DueDate) AS DueDate
     FROM dbo.Installment
     GROUP BY InstallmentOrder, PlotId
     HAVING (InstallmentOrder = 0)) AS InstallmentStartDate ON InstallmentStartDate.plotid = dbo.installment.plotid
WHERE     
    ((dbo.Plot.InstallmentPlanId > 0))

STATISTICS IO 用于正常工作的那个

(5089 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Installment'. Scan count 2, logical reads 109228, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Plot'. Scan count 1, logical reads 218, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'InstallmentPlan'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

和慢的：

(64577 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Installment'. Scan count 2, logical reads 2842959, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Plot'. Scan count 1, logical reads 272, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'InstallmentPlan'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

我的实际执行计划在这里

快速查询

慢查询

标签： performancestored-proceduressql-server-2012

解决方案

这里的问题是您的两个数据库之间的数据量不同，虽然查询在较小的数据库上可以接受，但在较大的数据库上却很慢。

查看这两个执行计划，我们可以看到优化器必须执行完整的聚集索引扫描来执行连接，因为没有合适的索引。对我来说，主要问题似乎是对Installment执行 2316 次的表进行 CI 扫描，导致读取 5,583,876 行（针对较大的数据库）相同的操作在较小数据库的查询中读取 395,505 行。您的STATISTICS IO输出告诉我们这是 2,733,731 次读取差异。这些是 8KB 页面，因此这是 2.6GB - 相当重要，可能是执行时间变化的罪魁祸首。

您可以通过在Installment表上创建非聚集索引来消除此扫描：

CREATE INDEX IX_Installment_InstallmentOrder ON Installment
(
    InstallmentOrder
)
INCLUDES
(
    DueDate,
    PlotId
)

这应该会减少读取次数并加快查询速度。

即使这可行，我怀疑如果您的数据集由于SELECT语句中的两个标量函数而继续增长 -GetInstPlanDueDate和CalculateSurchargableDays.

标量函数是逐行处理的，不能很好地扩展到更大的数据集，因此您将来可能需要删除它们并用基于集合的方法（表值函数、JOIN、视图、派生表等）替换

话虽如此，如果函数是可内联的，标量函数的性能在 SQL Server 2019 中得到了改进

推荐阅读