Sehr langsames gespeichertes Verfahren

Question

Ich habe Probleme mit der Abfrageoptimierung, derzeit bin ich sehr nahe am Punkt der Neugestaltung der Datenbank. Und stackoverflow ist meine letzte Hoffnung. Ich glaube nicht, dass es ausreicht, Ihnen nur die Abfrage zu zeigen, daher habe ich nicht nur das Datenbankskript verlinkt, sondern auch das Datenbank-Backup angehängt, falls Sie die Daten nicht manuell generieren möchten

Hier finden Sie sowohl das Skript als auch das Backup

Die Probleme beginnen, wenn Sie versuchen, Folgendes zu tun...

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'

Die Hauptprobleme treten in diesem Abschnitt auf:

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: Hier sind die Mengen an Zeilen in jeder Tabelle:

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

Answer 1

Das ist im Grunde genommen nur eine vage Vermutung, aber in der Vergangenheit habe ich festgestellt, dass das Anhängen an die Ergebnisse einer Unterabfrage furchtbar langsam sein kann. Das heißt, die Unterabfrage wurde viel zu oft ausgewertet, wenn es wirklich nicht nötig war.
Der Ausweg bestand darin, die Unterabfragen in CTEs zu verschieben und stattdessen daran anzuhängen. Viel Glück!

Answer 2

Jemand anderes könnte besser erklären können als ich, warum das viel schneller ist. Meine Erfahrung sagt mir, dass, wenn Sie eine Menge Abfragen haben, die zusammen langsam sind, aber in ihren einzelnen Teilen schnell sein sollten, es sich lohnt, eine temporäre Tabelle auszuprobieren.

Dies ist viel schneller

ALTER PROCEDURE LockBranches
-- Fügen Sie die Parameter für die gespeicherte Prozedur hier ein
@count INT,
@lockedOn DATETIME,
@unlockOn DATETIME,
@lockedBy UNIQUEIDENTIFIER

AS
BEGIN
-- SET NOCOUNT ON hinzugefügt, um zusätzliche Ergebnismengen zu verhindern
-- die SELECT-Anweisungen stören.
SET NOCOUNT ON

--Temporäre Tabelle erstellen
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

Dies ist viel schneller mit einer durchschnittlichen Ausführungszeit von 54 ms im Vergleich zu 6 Sekunden mit dem Original.

EDIT

Habe mir das angesehen und meine Ideen mit denen aus der Lösung von RBarryYoung kombiniert. Wenn Sie das folgende verwenden, um die temporäre Tabelle zu erstellen

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

dann können Sie dies auf 15 ms reduzieren, was 400-mal besser ist als zu Beginn. Ein Blick auf den Ausführungsplan zeigt, dass eine Tabelle gescannt wird in der temporären Tabelle. Normalerweise vermeidet man Table Scans so gut wie möglich, aber für 128 Zeilen (in diesem Fall) ist es schneller als das, was es zuvor getan hat.

Answer 3

Es scheint, dass der Join auf den beiden uniqueidentifier-Spalten die Ursache für das Problem ist. Einer ist ein gruppiertes Index, der andere ein nicht gruppiertes auf der (FK-Tabelle). Gut, dass es Indizes auf ihnen gibt. Leider sind GUIDs berüchtigt leistungsschwach, wenn sie mit einer großen Anzahl von Zeilen verbunden werden.

Als Troubleshooting-Schritte:

• In welchem Zustand befinden sich die Indizes? Wann wurden die Statistiken zuletzt aktualisiert?
• Wie leistungsfähig ist die Subabfrage an sich, wenn sie adhoc ausgeführt wird? D.h. wenn Sie diese Anweisung für sich alleine ausführen, wie schnell gibt das Resultset zurück? Akzeptabel?

• Nach dem Neuaufbau der 2 Indizes und der Aktualisierung der Statistiken, gibt es einen messbaren Unterschied?

  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

Die folgende Anweisung läuft auf meinem System etwa 15-mal schneller:

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

Einfügung einer Unterabfrage in eine lokale temporäre Tabelle

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 
    GROUP BY P.ID
    HAVING COUNT(R.ID) > 0
) AS X ON P.ID = X.ID
GROUP BY SB.BranchID

---

Die unten stehende Abfrage zeigt die partiellen Verknüpfungen mit der entsprechenden Tabelle anstelle von Komplett!!

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