存储过程非常慢

Question

我在查询优化方面遇到了困难，目前我非常接近数据库重新设计的点。 stackoverflow 是我最后的希望。我认为仅仅向您显示查询就足够了，因此我不仅链接了数据库脚本，还链接了数据库备份，以防您不想手动生成数据

在这里您可以找到脚本和备份

当您尝试执行以下操作时，问题就会开始...

exec LockBranches @count=64,@lockedBy='034C0396-5C34-4DDA-8AD5-7E43B373AE5A',@lockedOn='2011-07-01 01:29:43.863',@unlockOn='2011-07-01 01:32:43.863'

主要问题出现在这部分：

UPDATE B
SET B.LockedBy = @lockedBy,
    B.LockedOn = @lockedOn,
    B.UnlockOn = @unlockOn,
    B.Complete = 1
FROM
(
    SELECT TOP (@count) B.LockedBy, B.LockedOn, B.UnlockOn, B.Complete
    FROM Objectives AS O
    INNER JOIN Generations AS G ON G.ObjectiveID = O.ID
    INNER JOIN Branches AS B ON B.GenerationID = G.ID
    INNER JOIN
    (
        SELECT SB.BranchID AS BranchID, SUM(X.SuitableProbes) AS SuitableProbes
        FROM SpicieBranches AS SB
        INNER JOIN Probes AS P ON P.SpicieID = SB.SpicieID
        INNER JOIN
        (
            SELECT P.ID, 1 AS SuitableProbes
            FROM Probes AS P
/* ----> */ INNER JOIN Results AS R ON P.ID = R.ProbeID /* SSMS Estimated execution plan says this operation is the roughest */
            GROUP BY P.ID
            HAVING COUNT(R.ID) > 0
        ) AS X ON P.ID = X.ID
        GROUP BY SB.BranchID
    ) AS X ON X.BranchID = B.ID
    WHERE
            (O.Active = 1)
        AND (B.Sealed = 0)
        AND (B.GenerationNo < O.BranchGenerations)
        AND (B.LockedBy IS NULL OR DATEDIFF(SECOND, B.UnlockOn, GETDATE()) > 0)
        AND (B.Complete = 1 OR X.SuitableProbes = O.BranchSize * O.EstimateCount * O.ProbeCount)        
) AS B

编辑：以下是中的行数每张桌子：

Spicies         71536
Results         10240
Probes          10240
SpicieBranches  4096
Branches        256
Estimates       5
Generations     1
Versions        1
Objectives      1

Answer 1

其他人可能比我能更好地解释为什么这要快得多。经验告诉我，当您有一堆查询在一起运行速度很慢但在各个部分应该很快时，那么值得尝试使用临时表。

这要快得多

ALTER PROCEDURE LockBranches
-- Add the parameters for the stored procedure here  
@count INT,   
@lockedOn DATETIME,  
@unlockOn DATETIME,  
@lockedBy UNIQUEIDENTIFIER 

AS  
BEGIN  
 -- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.  
 SET NOCOUNT ON  

--Create Temp Table
SELECT SpicieBranches.BranchID AS BranchID, SUM(X.SuitableProbes) AS SuitableProbes 
INTO #BranchSuitableProbeCount
FROM SpicieBranches 
INNER JOIN Probes AS P ON P.SpicieID = SpicieBranches.SpicieID  
INNER JOIN  
(  
     SELECT P.ID, 1 AS SuitableProbes  
     FROM Probes AS P  
     INNER JOIN Results AS R ON P.ID = R.ProbeID  
     GROUP BY P.ID  
     HAVING COUNT(R.ID) > 0  
) AS X ON P.ID = X.ID  
GROUP BY SpicieBranches.BranchID


UPDATE B SET 
B.LockedBy = @lockedBy,    
B.LockedOn = @lockedOn,    
B.UnlockOn = @unlockOn,    
B.Complete = 1
FROM
(
  SELECT TOP (@count) Branches.LockedBy, Branches.LockedOn, Branches.UnlockOn, Branches.Complete  
  FROM Objectives  
  INNER JOIN Generations ON Generations.ObjectiveID = Objectives.ID  
  INNER JOIN Branches ON Branches.GenerationID = Generations.ID  
  INNER JOIN #BranchSuitableProbeCount ON Branches.ID = #BranchSuitableProbeCount.BranchID  
  WHERE  
    (Objectives.Active = 1)  
   AND (Branches.Sealed = 0)  
   AND (Branches.GenerationNo < Objectives.BranchGenerations)  
   AND (Branches.LockedBy IS NULL OR DATEDIFF(SECOND, Branches.UnlockOn, GETDATE()) > 0)  
   AND (Branches.Complete = 1 OR #BranchSuitableProbeCount.SuitableProbes = Objectives.BranchSize * Objectives.EstimateCount * Objectives.ProbeCount)
) AS B

END

平均执行时间为 54 毫秒，而原始执行时间为 6 秒，速度要快得多。

编辑

看看并将我的想法与 RBarryYoung 解决方案中的想法结合起来。如果您使用以下命令创建临时表

SELECT SB.BranchID AS BranchID, COUNT(*) AS SuitableProbes
INTO #BranchSuitableProbeCount  
FROM SpicieBranches AS SB
INNER JOIN Probes AS P ON P.SpicieID = SB.SpicieID
WHERE EXISTS(SELECT * FROM Results AS R WHERE R.ProbeID = P.ID)
GROUP BY SB.BranchID

，那么您可以将其缩短至 15 毫秒，这比我们开始时的时间快了 400 倍。查看执行计划表明临时表上发生了表扫描。通常，您会尽可能避免表扫描，但对于 128 行（在本例中），它比以前执行的任何操作都要快。

Answer 2

这基本上是一个完整的猜测，但在过去，我发现加入子查询的结果可能会慢得可怕。也就是说，子查询在实际上不需要的时候被评估了太多次。
解决这个问题的方法是将子查询移动到 CTE 中并加入到这些子查询中。祝你好运！

Answer 3

看来两个 uniqueidentifier 列上的连接是问题的根源。一种是聚集索引，另一种是非聚集索引（FK表）。很好的是它们有索引。不幸的是，在连接大量行时，向导的性能非常差。

作为故障排除步骤：

• 索引处于什么状态？上次更新统计数据是什么时候？
• 当临时执行时，该子查询本身的性能如何？即，当您单独运行该语句时，结果集返回的速度有多快？可以接受吗？
• 重建两个索引并更新统计数据后，是否有任何可测量的差异？

SELECT P.ID, 1 AS SuitableProbes FROM Probes AS P
INNER JOIN Results AS R ON P.ID = R.ProbeID
GROUP BY P.ID  HAVING COUNT(R.ID) > 0

Answer 4

以下命令在我的系统上运行速度大约是原来的 15 倍：

UPDATE B
SET B.LockedBy = @lockedBy,
    B.LockedOn = @lockedOn,
    B.UnlockOn = @unlockOn,
    B.Complete = 1
FROM
(
    SELECT TOP (@count) B.LockedBy, B.LockedOn, B.UnlockOn, B.Complete
    FROM Objectives AS O
    INNER JOIN Generations AS G ON G.ObjectiveID = O.ID
    INNER JOIN Branches AS B ON B.GenerationID = G.ID
    INNER JOIN 
    (
        SELECT SB.BranchID AS BranchID, COUNT(*) AS SuitableProbes
        FROM SpicieBranches AS SB
        INNER JOIN Probes AS P ON P.SpicieID = SB.SpicieID
        WHERE EXISTS(SELECT * FROM Results AS R WHERE R.ProbeID = P.ID)
        GROUP BY SB.BranchID
    ) AS X ON X.BranchID = B.ID
    WHERE
            (O.Active = 1)
        AND (B.Sealed = 0)
        AND (B.GenerationNo < O.BranchGenerations)
        AND (B.LockedBy IS NULL OR DATEDIFF(SECOND, B.UnlockOn, GETDATE()) > 0)
        AND (B.Complete = 1 OR X.SuitableProbes = O.BranchSize * O.EstimateCount * O.ProbeCount)        
) AS B

Answer 5

将子查询插入本地临时表

SELECT SB.BranchID AS BranchID, SUM(X.SuitableProbes) AS SuitableProbes
into #temp FROM SpicieBranches AS SB
INNER JOIN Probes AS P ON P.SpicieID = SB.SpicieID
INNER JOIN
(
    SELECT P.ID, 1 AS SuitableProbes
    FROM Probes AS P
/* ----> */ INNER JOIN Results AS R ON P.ID = R.ProbeID /* SSMS Estimated execution plan says this operation is the roughest */
    GROUP BY P.ID
    HAVING COUNT(R.ID) > 0
) AS X ON P.ID = X.ID
GROUP BY SB.BranchID

---

下面的查询显示与相应表的部分连接而不是完整连接！

UPDATE B
SET B.LockedBy = @lockedBy,
    B.LockedOn = @lockedOn,
    B.UnlockOn = @unlockOn,
    B.Complete = 1
FROM
(
    SELECT TOP (@count) B.LockedBy, B.LockedOn, B.UnlockOn, B.Complete
    From
    (
        SELECT ID, BranchGenerations, (BranchSize * EstimateCount * ProbeCount) as MultipliedFactor
        FROM Objectives AS O WHERE (O.Active = 1)
    )O
    INNER JOIN Generations AS G ON G.ObjectiveID = O.ID
    Inner Join
    (
        Select Sealed, GenerationNo, LockedBy, UnlockOn, ID, Complete
        From Branches 
        Where B.Sealed = 0 AND (B.LockedBy IS NULL OR DATEDIFF(SECOND, B.UnlockOn, GETDATE()) > 0)
    )B ON B.GenerationID = G.ID
    INNER JOIN
    (
        Select * from #temp
    ) AS X ON X.BranchID = B.ID
    WHERE
        AND (B.GenerationNo < O.BranchGenerations)
        AND (B.Complete = 1 OR X.SuitableProbes = O.MultipliedFactor)        
) AS B