none
effizientes Umschaufeln großer gefilterter Datenmengen von einer Tabelle in eine andere

    Frage

  • Hallo,

    nachdem es mir gelungen ist, rund 828Mio Datensätze auf den SQL-Server zu bringen, stehe ich nun vor der nächsten Herausforderung. Die Daten müssen ausgewertet, bzw. teilwiese wieder aussortiert und geordnet werden. Leider ließ sich das beim Berechnen und beim Übertragen nicht schon bewerkstelligen, weil ich dazu den aller letzten Wert benötige und somit erst am Ende weiß, wie die Daten aufzubereiten sind.

    Es  liegt eine Tabelle (tblTmp) mit folgender Struktur vor, zwei Datenfelder (828Mio Einträge):

    Vorgaenger (bigint) und Nachfolger (bigint)

    Der Berechnungsalgorithmus hat insgesamt 5 Ergebnisse geliefert, die keinen Nachfolger haben und somit das Ende der Berechnungskette darstellen. Nur einer von den 5 ist das "echte" Ergebnis, die anderen 4 sind nun aus der Tabelle zu entfernen. Damit meine ich, dass alle Vorgaenger dieser 4 Nichtergebnisse und deren Vorgaenger bis zurück zum Anfang wieder aus der Tabelle entfernt werden müssen. Ich muss also die Tabelle von "hinten" durchlaufen.

    Die Idee ist nun, alle Vorgaenger des korrekten Ergebnissen zu suchen und in eine neue Tabelle zu schaufeln

    INSERT INTO tblErgebnis(Wert,Stufe) SELECT Wert,31 FROM tblTmp WHERE Nachfolger=@Endergebnis

    Das bringt 4 Treffer. Die Stufe sagt nur aus, dass die so eingetragenen Werte zur Berechnungsstufe 31 gehören.

    Im nächsten Schritt sind dann alle Vorgaenger der Stufe 31, also Stufe 30 zu suchen, das wäre dann sowas

    INSERT INTO tblErgebnis(Wert,Stufe) SELECT Wert,30 FROM tblTmp WHERE Nachfolger IN (SELECT Wert FROM tblErgebnis WHERE Stufe=31)

    Sinngemäß also, suche aus der Tabelle Ergebnis alle Felder der (vorherigen) Stufe 31, suche für diese aus der Tabelle Tmp alle die, diese als Nachfolger eingetragen haben und schaufele diese mit (nächster) Stufe 30 in die Tabelle Ergebnis. und so weiter und so fort.

    Ist die SQL-Syntax korrekt?

    Was passiert, wenn der letzte Teil, also nach IN, Millionen von Treffern generiert? Das wäre bei Stufe 24 so was erreicht. Da dürfte der Server doch in die Knie gehen, oder? Hat das überhaupt eine Chance so, oder ist das von Anfang an zum Scheitern verurteilt? Man bedenke die riesige Anzahl an Datensätzen. Die Suche nach Vorgängern erreicht im Höchstfalle ca. 135Mio (vielleicht auch nur 1/4 davon, aber kaum weniger) Treffer.

    Gruß

    H.

    Montag, 12. Februar 2018 10:28

Alle Antworten

  • Ich würde das ohne den Insert in eine temporäre Tabelle machen sondern das Konstrukt des "Rekursiven CTE's", also eines "Union Join" auf sich selber, der von Nachfolger zu Vorgänger verlinkt und den delete dazu auslöst.

    Die Rekursion dazu wird vom Select automatisch durchgeführt.

    Allerdings ist hier die richtige Wahl der Transaktion entscheidend, da duch das Delete ggf. der Select verfälscht wird.

    Du kannst aber eigentlich den umgekehrte Weg gehen. Nicht die Daten zu löschen, sondern nur die gültigen Daten in eine neue Tabelle zu übertragen.

    Auch dieses geht mit einem recursiven CTE (ggf. als View bereitgestellt) und dann einem einfachen

    Insert into NewTable
    select * from CTE_View

    Somit kopierst du ggf. nur 1/5 der Daten statt 4/5 zu löschen.

    https://technet.microsoft.com/de-de/library/ms186243%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
    • Bearbeitet bfuerchau Montag, 12. Februar 2018 10:42
    Montag, 12. Februar 2018 10:37
  • Du kannst aber eigentlich den umgekehrte Weg gehen. Nicht die Daten zu löschen, sondern nur die gültigen Daten in eine neue Tabelle zu übertragen.


    das ist doch genau das was ich machen möchte. Ich schaufele in eine neue nicht temporäre Tabelle tblErgebnis. Die Links von Vorgänger zu Nachfolger bleiben in der ursprünglichen Tabelle tblTmp voll erhalten. Vielleicht war das missverständlich ausgedrückt. In den Codebeispielen allerdings gibt es keine DELETE.

    Es bleibt aber auch bei der Rekursion die Frage, ob das alles noch mit Millionen von Datensätzen funktioniert.

    Montag, 12. Februar 2018 10:54
  • Ja natürlich, der Rekursive CTE geht genau die Sätze durch, die durch den Bezug definiert sind.
    Voraussetzung ist natürlich auf jeden Fall ein Index über den Suchschlüssel;-).

    Vorteil: es ist genau auch nur 1 Select und keine Prozedur mit u.U. millionen von Selects erforderlich.

    Schau dir den Link zur CTE mal an.

    Montag, 12. Februar 2018 10:59
  • Interessant wäre es sicherlich das ganze dann noch mit einem Columnstore Index zu kombinieren.

    Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

    Mittwoch, 14. Februar 2018 09:12
    Beantworter
  • Moin,

    ich habe die Daten inzwischen aufbereitet. Was mich positiv überrascht hat ist, dass das dieses mal überhaupt kein Performanceproblem bedeutete.

    Ich musste die 828Mio Einträge insgesamt 31 mal durchsuchen, passende Einträge aussortieren und in einen andere Tabelle speichern. Ob nu rekursiv oder per Hand angeschmissen, pro Runde waren es immer so 5:30 Minuten. 

    Somit reduzierte sich das ursprüngliche Performancegedöns einzig und alleine auf die Kommunikation zwischen Client und Server. Nur das bremste mich aus.

    Gruß

    H.

    Mittwoch, 14. Februar 2018 09:31
  • Der Overhead betrifft nicht nur die Kommunikation sondern eben auch den SQL-Part bis zur Ausführungsschicht.
    Sobald dies einmal erledigt ist, kann der SQL-Server (und andere DB's ebenso) mit nativen Methoden auf seine DB zugreifen was eben erheblich schneller ist weil viel gespart wird.

    Ein "Insert into ... select ... from ..." ist natürlich schneller, als eine Schleife mit "while Select .... => insert into", was ja wieder Satzweise geht.

    Natürlich darf beim "Insert ... select" die Quelle keine Prozedur sein oder Views, die Prozeduren enthalten oder auf Verbindungsserver gehen. Da ist dann wieder Overhead im Spiel.
    • Bearbeitet bfuerchau Mittwoch, 14. Februar 2018 10:38
    Mittwoch, 14. Februar 2018 10:36