Optimieren von ROW_NUMBER() in SQL Server

Question

Wir haben eine Reihe von Rechnern, die in sporadischen Abständen Daten in einer Datenbank aufzeichnen. Für jeden Datensatz möchte ich die Zeitspanne zwischen こん Aufnahme und die vorherige Aufnahme.

Ich kann dies mit ROW_NUMBER wie folgt tun:

WITH TempTable AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Machine_ID ORDER BY Date_Time) AS Ordering
    FROM dbo.DataTable
)

SELECT [Current].*, Previous.Date_Time AS PreviousDateTime
FROM TempTable AS [Current]
INNER JOIN TempTable AS Previous 
    ON [Current].Machine_ID = Previous.Machine_ID
    AND Previous.Ordering = [Current].Ordering + 1

Das Problem ist, dass es vraiment langsam (mehrere Minuten bei einer Tabelle mit etwa 10k Einträgen) - ich habe versucht, separate Indizes für Machine_ID und Date_Time sowie einen einzelnen Join-Index zu erstellen, aber nichts hilft.

Gibt es eine Möglichkeit, diese Abfrage umzuschreiben, um schneller zu werden?

Answer 1

Die angegebene ROW_NUMBER()-Partition und die Reihenfolge erfordern einen Index auf (Machine_ID, Date_Time) in einem Durchgang zu erfüllen:

CREATE INDEX idxMachineIDDateTime ON DataTable (Machine_ID, Date_Time);

Getrennte Indizes für "Machine_ID" und "Date_Time" sind wenig hilfreich, wenn überhaupt.

Answer 2

Wie sieht es im Vergleich zu dieser Version aus?

SELECT x.*
    ,(SELECT MAX(Date_Time)
      FROM dbo.DataTable
      WHERE Machine_ID = x.Machine_ID
          AND Date_Time < x.Date_Time
    ) AS PreviousDateTime
FROM dbo.DataTable AS x

Oder diese Version?

SELECT x.*
    ,triang_join.PreviousDateTime
FROM dbo.DataTable AS x
INNER JOIN (
    SELECT l.Machine_ID, l.Date_Time, MAX(r.Date_Time) AS PreviousDateTime
    FROM dbo.DataTable AS l
    LEFT JOIN dbo.DataTable AS r
    ON l.Machine_ID = r.Machine_ID
        AND l.Date_Time > r.Date_Time
    GROUP BY l.Machine_ID, l.Date_Time
) AS triang_join
ON triang_join.Machine_ID = x.Machine_ID
    AND triang_join.Date_Time = x.Date_Time

Beide würden am besten mit einem Index auf Machine_ID, Date_Time und für korrekte Ergebnisse durchführen, ich nehme an, dass dies eindeutig ist.

Sie haben nicht erwähnt, was in * versteckt ist, und das kann manchmal eine Menge bedeuten, da ein Machine_ID, Date_Time-Index im Allgemeinen nicht abgedeckt wird, und wenn Sie eine Menge Spalten dort haben oder sie haben eine Menge Daten, ...

Answer 3

Wenn die Anzahl der Zeilen in dbo.DataTable groß ist, ist es wahrscheinlich, dass das Problem durch die CTE-Selbstverknüpfung auf sich selbst verursacht wird. In einem Blogbeitrag wird das Problem ausführlich erläutert aquí

Gelegentlich habe ich in solchen Fällen darauf zurückgegriffen, eine temporäre Tabelle zu erstellen, in die ich das Ergebnis der CTE-Abfrage einfüge und dann die Joins gegen diese temporäre Tabelle durchführe (obwohl dies in der Regel für Fälle gilt, in denen eine große Anzahl von Joins gegen die temporäre Tabelle erforderlich ist - im Falle eines einzelnen Joins ist der Leistungsunterschied weniger bemerkenswert)

Answer 4

Ich hatte einige seltsame Leistungsprobleme bei der Verwendung von CTEs in SQL Server 2005. In vielen Fällen löste das Ersetzen der CTE durch eine echte temporäre Tabelle das Problem.

Ich würde dies ausprobieren, bevor ich mit der Verwendung eines CTE weitermache.

Ich habe nie eine Erklärung für die Leistungsprobleme gefunden, die ich gesehen habe, und hatte auch keine Zeit, den Ursachen auf den Grund zu gehen. Ich habe jedoch immer vermutet, dass die Engine die CTE nicht auf die gleiche Weise optimieren kann wie eine temporäre Tabelle (die indiziert werden kann, wenn eine weitere Optimierung erforderlich ist).

Update

Nach Ihrer Bemerkung, dass es sich um eine Ansicht handelt, würde ich die Abfrage zunächst mit einer temporären Tabelle testen, um zu sehen, ob diese besser funktioniert.

Wenn dies der Fall ist und die Verwendung einer gespeicherten Prozedur keine Option ist, können Sie erwägen, die aktuelle CTE in eine indizierte/materialisierte Ansicht zu verwandeln. Bevor Sie diesen Weg einschlagen, sollten Sie sich über das Thema informieren, denn ob dies eine gute Idee ist, hängt von vielen Faktoren ab, nicht zuletzt davon, wie oft die Daten aktualisiert werden.

Answer 5

Wie wäre es, wenn Sie einen Trigger verwenden, um den letzten Zeitstempel zu speichern und jedes Mal zu subtrahieren, um die Differenz zu erhalten?