TSQL - Rekursiven CTE ineffizient Brauchen eine alternative

Hier ist eine Tabelle mit Beispieldaten:

DECLARE @TestTable TABLE (
    ItemID INT,
    A INT,
    B INT,
    Month INT)

INSERT INTO @TestTable VALUES (1234, 5, 9, 1)
INSERT INTO @TestTable VALUES (1234, 6, 9, 2)
INSERT INTO @TestTable VALUES (4321, 5, 11, 1)
INSERT INTO @TestTable VALUES (4321, 12, 11, 2)
INSERT INTO @TestTable VALUES (1324, 14, 6, 1)
INSERT INTO @TestTable VALUES (1324, 5, 6, 2)
INSERT INTO @TestTable VALUES (1234, 1, 9, 3)
INSERT INTO @TestTable VALUES (1324, 9, 6, 3)

Etwas zu beachten ist, dass die B-Säule ist immer die gleiche, da es nur einmal benutzt in dieser Berechnung, aber gebraucht wird für die erste Berechnung.

Ich bin versucht zu subtrahieren Sie B von A auf die erste Zeile, dann auf den anschließenden Zeilen subtrahieren Sie die vorherigen Zeilen Unterschied von A. Effektiv, B - A = C auf den ersten, dann C - A auf alle nachfolgenden Zeilen FÜR DIE ZUGEHÖRIGE ItemID.

Hier sind die Ergebnisse, die ich erwarte:

ItemID  A   B   C   Month   RowNumber
1234    5   9   4   1       1
1234    6   9   -2  2       2
1234    1   9   -3  3       3
1324    14  6   -8  1       1
1324    5   6   -13 2       2
1324    9   6   -22 3       3
4321    5   11  6   1       1
4321    12  11  -6  2       2

Hier ist, wie ich bin, dies zu erreichen.

;WITH CTE_TestValue AS (
    SELECT 
        Main.ItemID,
        Main.A,
        Main.B,
        Main.Month,
        ROW_NUMBER() OVER (Partition BY Main.ItemID ORDER BY Main.Month) AS RowNumber
    FROM @TestTable AS Main
),
CTE_TestColumnC AS (
    SELECT 
        MainA.ItemID,
        MainA.A,
        MainA.B,
        (MainA.B - MainA.A) AS C,
        MainA.Month,
        MainA.RowNumber
    FROM CTE_TestValue AS MainA
        WHERE MainA.Rownumber = 1

    UNION ALL

    SELECT 
        MainB.ItemID,
        MainB.A,
        MainB.B,
        (Sub.C - MainB.A) AS C,
        MainB.Month,
        MainB.RowNumber
    FROM CTE_TestValue AS MainB
        INNER JOIN CTE_TestColumnC AS Sub
            ON MainB.RowNumber - 1 = Sub.RowNumber
            AND MainB.ItemID = Sub.ItemID
--      CROSS JOIN CTE_TestColumnC AS Sub
--          WHERE Sub.RowNumber + 1 = MainB.RowNumber
--          AND MainB.ItemID = Sub.ItemID 
)
SELECT 
    Main.ItemID,
    Main.A,
    Main.B,
    Main.C,
    Main.Month,
    Main.RowNumber
FROM CTE_TestColumnC AS Main
ORDER BY ItemID, Month, RowNumber

Dies funktioniert problemlos auf einem small-data-Beispiel, aber ich bin den Umgang mit etwa 20.000 ItemId jeder Wiederholung 10 mal. Abschluss aller der ersten Zeile Berechnungen sofort, wie erwartet, und dann die Berechnung geh mal bis DRASTISCH.

Wie Sie sehen können ich habe versucht, sowohl eine INNER JOIN und ein CROSS JOIN. Ich glaube, Sie haben den gleichen Ausführungsplan mit den Parametern, die ich gegeben habe, die CROSS JOIN.

Gibt es einen effektiveren/effizienteren Weg, dies zu erreichen?

Darf ich diese zum ausführen für 5 Stunden gestern zu sehen, ob es überhaupt am Ende.. es kam nicht.

Noch ein Hinweis: Wenn ich mit diesem auf der test-Daten, die ich SELECT OHNE Verwendung ORDER hoffentlich helfen, die Geschwindigkeit Dinge entlang. Die ORDER ist nur für meine Bequemlichkeit, wenn ich tatsächlich prüfen.

Ziemlich sicher, dass dies nicht deterministisch VON Main.ItemID ORDER BY-Main.ItemID als ItemID wiederholt.
Das ist wirklich eine abgestumpft-down-Beispiel für ein viel größeres problem. Es ist tatsächlich eine andere Spalte in der Daten verwende ich das richtig, um es, wie ich es brauche. Ich wollte nur nicht zu matschig das Beispiel hat mir keine Antworten in der Vergangenheit
Auf der Grundlage der up-votes für meine Beispiel-Abfrage, die ich gehe davon aus, dass dies der beste Weg ist. Ich denke, ich muss einen Weg finden, um index die Daten-sample, um hoffentlich zu Geschwindigkeit Dinge entlang. Kann mir jemand erklären, warum diese Berechnung dauert da so lange? Ich nehme an, es ist in Bezug auf die rekursive "loop", auf dem neu-WÄHLEN Sie den vorherigen Datensatz 10 mal pro ItemID.
Nein, ich glaube nicht, dass ist die beste Weise, es zu tun. CTE ist nur syntax und neu bewertet wird. Ich habe das problem gefunden-Anweisung fehlerhaft und u entlassen, mein Kommentar. Subtrahieren der letzten Zeile ist nicht C - A es ist A - Cprior.
Ich habe nicht die Absicht, zu entlassen, ist Ihr Kommentar. Ich fühlte nur, es war irrelevant für die Frage auf der hand. Aber ich sehe jetzt, dass es zu Problemen führen könnte, in jeder Antwort, die ich erhalten. Ich änderte den ursprünglichen post, um genauer zu reflektieren, was ich bin den Umgang mit.
"Ich habe versucht, einen inner join und cross join" aber das sind zwei völlig unterschiedliche Konzepte, und sollte unterschiedliche Ergebnisse erzeugen (es sei denn, eine der Tabellen hat nur eine Zeile).

InformationsquelleAutor jayEss | 2012-10-10

Dein problem ist, dass Sie mit einem CTE, die als Quelle einer rekursiven CTE. Ihre erste CTE ausgeführt werden, einmal für jede iteration Ihres rekursiven Allgemeinen Tabellenausdruck. Mit Ihrem test-Daten, dass bedeutet, dass CTE_TestValue erstellt wird 8 mal.

Das Ergebnis der CTE_TestValue in eine temp-Tabelle, die einen gruppierten Primärschlüssel auf (RowNumber, ItemID) und verwenden, die temporäre Tabelle als Datenquelle für den rekursiven Allgemeinen Tabellenausdruck CTE_TestColumnC.

Ändern sich auch die join-Bedingung in der rekursiven Teil zu ON MainB.RowNumber = Sub.RowNumber + 1. Zu machen, dass die Abfrage in der Lage, verwenden Sie den index auf die temporäre Tabelle.

DECLARE @TestTable TABLE (
    ItemID INT,
    A INT,
    B INT,
    Month INT)

INSERT INTO @TestTable VALUES (1234, 5, 9, 1)
INSERT INTO @TestTable VALUES (1234, 6, 9, 2)
INSERT INTO @TestTable VALUES (4321, 5, 11, 1)
INSERT INTO @TestTable VALUES (4321, 12, 11, 2)
INSERT INTO @TestTable VALUES (1324, 14, 6, 1)
INSERT INTO @TestTable VALUES (1324, 5, 6, 2)
INSERT INTO @TestTable VALUES (1234, 1, 9, 3)
INSERT INTO @TestTable VALUES (1324, 9, 6, 3)

CREATE TABLE #TestValue
(
  ItemID INT,
  A INT,
  B INT,
  Month INT,
  RowNumber INT,
  primary key(RowNumber, ItemID)
)

INSERT INTO #TestValue
SELECT 
    Main.ItemID,
    Main.A,
    Main.B,
    Main.Month,
    ROW_NUMBER() OVER (Partition BY Main.ItemID ORDER BY Main.Month) AS RowNumber
FROM @TestTable AS Main


;WITH CTE_TestColumnC AS (
    SELECT 
        MainA.ItemID,
        MainA.A,
        MainA.B,
        (MainA.B - MainA.A) AS C,
        MainA.Month,
        MainA.RowNumber
    FROM #TestValue AS MainA
        WHERE MainA.Rownumber = 1

    UNION ALL

    SELECT 
        MainB.ItemID,
        MainB.A,
        MainB.B,
        (Sub.C - MainB.A) AS C,
        MainB.Month,
        MainB.RowNumber
    FROM #TestValue AS MainB
        INNER JOIN CTE_TestColumnC AS Sub
            ON MainB.RowNumber = Sub.RowNumber + 1
            AND MainB.ItemID = Sub.ItemID
)
SELECT 
    Main.ItemID,
    Main.A,
    Main.B,
    Main.C,
    Main.Month,
    Main.RowNumber
FROM CTE_TestColumnC AS Main
ORDER BY ItemID, Month, RowNumber

DROP TABLE #TestValue

In der Abfrageplan für die Abfrage das problem ist in der Tabelle gezeigten scan in der unteren rechten Ecke. mit diesen Testdaten ausgeführt wird 8-mal mit insgesamt 64 Zeilen zurückgegeben:

TSQL - Rekursiven CTE ineffizient Brauchen eine alternative

Den Abfrage-Pläne für die Abfrage eine temporäre Tabelle:
TSQL - Rekursiven CTE ineffizient Brauchen eine alternative

Dank einem Haufen! Funktioniert wie ein Charme!
Welches tool verwendet, um generieren, die schöne Diagramme?
href="https://www.sentryone.com/plan-explorer" >sentryone.com/plan-explorer

InformationsquelleAutor Mikael Eriksson

0

Ich hoffe, dass ich das richtig verstanden habe, was Sie zu tun versuchen.

Hier ist meine Lösung:
```
WITH DATA AS (
SELECT *, row_number() over (ORDER BY itemid) RN
FROM TestTable),
RECURSIVE AS (
   SELECT itemID, B-A AS C, RN
  FROM DATA
  WHERE RN = 1
  UNION ALL
  SELECT T1.itemID, t2.C - t1.A, t1.RN
  FROM DATA AS T1
  INNER JOIN
  RECURSIVE AS T2
  ON t1.RN = T2.Rn+1)
SELECT ItemID, C
FROM RECURSIVE
```
Finden Sie das vollständige Beispiel (mit deinen Daten) hier
- Das ist im Grunde die gleiche Abfrage, die ich habe, aber Ihr ist nicht die PARTITION, die die row_number es ist also nicht berücksichtigt, dass verschiedene zahlen in Beziehung zu den verschiedenen Elementen. Ich sollte angeben, dass in meiner Frage.
InformationsquelleAutor Gidil

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.