Geek Answers Handbook

Very slow stored procedure

It’s hard for me to optimize the queries, at the moment I'm very close to redesigning the database. And stackoverflow is my last hope. I don’t think that just showing you the query is enough, so I connected not only the database script, but also connected the database backup if you do not want to generate data manually

Here you can find both a script and a backup

Problems begin 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 arise 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: The following are the number of rows in each table:

 Spicies 71536 Results 10240 Probes 10240 SpicieBranches 4096 Branches 256 Estimates 5 Generations 1 Versions 1 Objectives 1
+4
source share
5 answers

Someone else can explain how I can explain why it is so much faster. Experience tells me when you have a bunch of queries that collectively work slowly together, but need to be fast in their separate parts, then it's worth trying a temporary table.

It's much faster

 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 faster with an average run time of 54 ms compared to 6 seconds with the original.

EDIT

Looked and combined his ideas with RBarryYoung solutions. If used to create a 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 it up to 15 ms, which is 400 times better than we started. Examination of the execution plan shows that the table is being scanned in the temp table. Usually you avoid scanning tables as much as you can, but for 128 rows (in this case) this is faster than before.

+2
source

This is basically a complete hunch here, but in past times I found that joining subquery results can be terribly slow. That is, the subquery has been evaluated too many times when it really is not needed.
The way around this was to move the subqueries to the CTE and join them instead. Good luck

+2
source

The two-column join of the uniqueidentifier seems to be the source of the problem. One is a clustered index, the other is nonclustered (table FK). It’s good that they have indexes. Unfortunately, messages tend to work poorly when merged with a large number of lines.

How to troubleshoot steps:

  • What is the state of the indices? When was the last time statistics were updated?
  • How far is this subquery performed when adhoc is executed? those. when you run this expression yourself, how quickly does the result return? acceptable?
  • after recovering two indices and updating statistics, is there any measurable difference?
 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
+1
source

It runs about 15 times faster on my system:

 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
+1
source

Insert an auxiliary query into a local temporary table

 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

The query below shows partial joins with the corresponding table, not a full one !!

 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
+1
source

More articles:


All Articles