存储过程非常慢(Very slow stored procedure)

我在查询优化方面遇到了困难，目前我非常接近数据库重新设计的重点。 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

I have a hard time with query optimization, currently I'm very close to the point of database redesign. And the stackoverflow is my last hope. I don't think that just showing you the query is enough so I've linked not only database script but also attached database backup in case you don't want to generate the data by hand

Here you can find both the script and the backup

The problems start when you try to do the following...

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'

The main problems occur in this part:

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

EDIT: Here are the amounts of rows in each table:

Spicies 71536 Results 10240 Probes 10240 SpicieBranches 4096 Branches 256 Estimates 5 Generations 1 Versions 1 Objectives 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

这比平均执行时间54ms快得多，而原始执行时间为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行（在这种情况下），它比以前做的更快。

Somebody else might be able to explain better than I can why this is much quicker. Experience tells me when you have a bunch of queries that collectively run slow together but should be quick in their individual parts then its worth trying a temporary table.

This is much quicker

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

This is much quicker with an average execution time of 54ms compared to 6 seconds with the original one.

EDIT

Had a look and combined my ideas with those from RBarryYoung's solution. If you use the following to create the temporary table

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

then you can get this down to 15ms which is 400x better than we started with. Looking at the execution plan shows that there is a table scan happening on the temp table. Normally you avoid table scans as best you can but for 128 rows (in this case) it is quicker than whatever it was doing before.

存储过程非常慢(Very slow stored procedure)

我在查询优化方面遇到了困难，目前我非常接近数据库重新设计的重点。 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

I have a hard time with query optimization, currently I'm very close to the point of database redesign. And the stackoverflow is my last hope. I don't think that just showing you the query is enough so I've linked not only database script but also attached database backup in case you don't want to generate the data by hand

Here you can find both the script and the backup

The problems start when you try to do the following...

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'

The main problems occur in this part:

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

EDIT: Here are the amounts of rows in each table:

Spicies 71536 Results 10240 Probes 10240 SpicieBranches 4096 Branches 256 Estimates 5 Generations 1 Versions 1 Objectives 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

这比平均执行时间54ms快得多，而原始执行时间为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行（在这种情况下），它比以前做的更快。

Somebody else might be able to explain better than I can why this is much quicker. Experience tells me when you have a bunch of queries that collectively run slow together but should be quick in their individual parts then its worth trying a temporary table.

This is much quicker

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

This is much quicker with an average execution time of 54ms compared to 6 seconds with the original one.

EDIT

Had a look and combined my ideas with those from RBarryYoung's solution. If you use the following to create the temporary table

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

then you can get this down to 15ms which is 400x better than we started with. Looking at the execution plan shows that there is a table scan happening on the temp table. Normally you avoid table scans as best you can but for 128 rows (in this case) it is quicker than whatever it was doing before.