Very slow stored procedure

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 开发者_C百科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

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.

This is basically a complete guess here, but in times past I've found that joining onto the results of a sub-query can be horrifically slow. That is, the subquery was being evaluated way too many times when it really didn't need to.
The way around this was to move the subqueries into CTEs and to join onto those instead. Good luck!

It appears the join on the two uniqueidentifier columns are the source of the problem. One is a clustered index, the other non-clustered on the (FK table). Good that there are indexes on them. Unfortunately guids are notoriously poor performing when joining with large numbers of rows.

As troubleshooting steps:

• what state are the indexes in? When was the last time the statistics were updated?
• how performant is that subquery onto itself, when executed adhoc? i.e. when you run this statement by itself, how fast does the resultset return? acceptable?
• after rebuilding the 2 indexes, 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

The following runs about 15x 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

Insertion of sub query into 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 below query shows the partial joins with the corresponding table instead of complete!!

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