Laden...

Performanceproblem bei großer CSV auslesen und in DB speichern

Erstellt von chavez vor 11 Jahren Letzter Beitrag vor 11 Jahren 4.306 Views
C
chavez Themenstarter:in
252 Beiträge seit 2007
vor 11 Jahren
Performanceproblem bei großer CSV auslesen und in DB speichern

Leider hat sich mittlerweile beim Auslesen einer riseigen csv Datei und anschließendem speichern in die DB ein Performanceproblem ergeben.
Die csv ist derzeit ca 1GB groß mit ca 15.000.000 Zeilen und wird vermutlich auch in nächster Zeit noch wachsen.
Ein wenig zum Hintergrund wie und was derzeit passiert.
In diesem CSV stehen pro Monat die Verkäufe von Alben bzw Tracks nach Länder aufgeschlüsselt.
1.)Die Csv wird derzeit mit folgendem Reader zeilenweise ausgelesen.
2.)Dann wird eine Anfrage an DB geschickt und das Album bzw der Track geladen, der diesem Verkauf entspricht. Dies erfolgt via nHibernate und es wird immer das gesamte Album inkl Tracks geladen, da die später für Berechnungen gebraucht werden.
3.) das Ergebnis der DB Abfrage wird in einem Dictionary gespeichert. Somit wird für alle weiteren Zeilen der CSV geprüft ob das Album/Track schon mal geladen wurde und falls ja eben keine Anfrage an die DB geschickt.
4.) Nun werden eben Berechungen(nur ein paar Subtraktionen und Multiplikationen) mit den Verkäufen und den Album/Tracks gemacht und danach eine Zeile in eine DataTable geschrieben.
5.) Diese Datatable wird dann mittels SqlBulkCopy in eine Tabelle geschrieben. Das Ganze muss innerhalb einer Transaction laufen und deswegen verwende ich eben UseInternalTransaction und BatchSize ist 0.

Bis das Ganze durchgelaufen ist vergehen ca 7 - 9 Stunden. Wobei die Schritte 1-4 ca zwei Drittel der Zeit in Anspruch nehmen.
Jetzt kam die Anforderung, dass dies(csv auslesen und in db speichern) schneller gehen muss. Das muss in 20 Minuten erledigt sein. Persönlich halte ich das für mehr als utopisch aber evtl lässt sich ja doch was machen.

Was mir noch aufgefallen ist, dass das abschließende Commit so lange dauert wie das schreiben in die DB. Ich lasse mich mittels NotifyAfter alle 10% vom Fortsschritt informieren. Dieser steht nach 1 - 1,5 Stunden bei der Anzahl der Rows und das abschließende Commit verbraucht wieder so ca 1 Stunde.

So und nun zu meinen Fragen:
Wo bzw wie könnte man noch optimieren?
Ich habe bisher noch nie mit so riesigen Datenmengen gearbeitet. Evtl ist die Dauer ja normal?
Ich könnte vorher bereits alle Alben/Tracks aus der DB laden. Somit enfällt dann der DB-Zugriff während des csv-auslesens. Das würde evtl etwas bringen nur werden die Alben/Tracks täglich mehr und irgendwann entsteht vermutlich hier der Bottleneck.
Die Csv in kleinere Portionen aufteilen und jeweils in die DB schreiben. Nur muss ich mich dann ja selbst darum kümmern falls zb beim letzten Batch was schief läuft, dass alle vorherigen auch gelöscht werden.

16.807 Beiträge seit 2008
vor 11 Jahren

Naja.. das dauert definitiv viel zu lange.

Die Aussage, dass die Schritte 1-4 aber 6 Stunden brauchen ist viel zu ungenau.
WAS braucht denn WIE lange? Dann kann man spezifische Optimierungen angehen. Lädst Du unnötige Daten aus der DB? Was sagt der SQL Profiler? Benutzt Du Scopes, damit der GC möglichst effizient Speicher wieder freigeben kann?

Wenn es um Vergleiche zu bereits existierende Dateneinträge geht, so lade ich die komplette Tabelle in den Cache (wenn möglich + Sperrung), da so die ständige wirklich simple Abfrage zur DB wegfällt und alles im Speicher stattfindet. Das KANN optimierend wirken - MUSS aber nicht. Du solltest natürlich nur die Dinge aus der Tabelle laden, die hier relevant sind (bei mir sind es einmalige Nummern).
Dein Vorhaben mit dem Dictionary-Cache ist eine gute Idee, aber ich denke, dass hier eben trotzdem viel Performance verloren geht. Eine Abfrage über ALLES geht halt deutlich schneller als 15 Millionen Anfragen.

Eine Forderung nach einer Zeitdauer zu erheben, ohne, dass dafür auch die Ressourcen in Betracht gezogen werden - weiß nicht, was das soll.

Ich jag ~2 Millionen Einträge in die Datenbank bzw. gleiche diese ab und das braucht ca. 4-5 Minuten.
Dabei nutze ich eben das Laden von Vergleichsdaten in den lokalen Cache, um die Existenz der Einträge zu prüfen, und ich benutz TransactionScopes zusammen mit dem Entity Framework.
Als Anhaltspunkt: ich brauch für das Lesen einer 2 GB XML und Übertragen der relevanten Daten in eine optimierte Objekt-Struktur keine 4 Sekunden.

Aber wie gesagt: wenn Du herausgefunden hast, WAS wirklich WIE lange dauert - DANN kann man daran arbeiten. Selbst der CSV-Reader, der hier "A fast CSV Reader" heißt, könnte das Problem sein. Oder eben die Ständigen Abfragen, die von 6 Stunden 5,5 Stunden benötigen.....

T
2.219 Beiträge seit 2008
vor 11 Jahren

Schon einmal versucht die Programmierung auf Multithreading umzustellen?
Die CSV sollte demnächst mal auf eine Sqlite Datenbank umgestellt werden bzw. eine eigene Datenbank für sowas eingerichtet werden.

Das zeilenweise auslesen und parsen von 1 GB großen Dateien mit rund 15 Mio Zeilen ist schon keine schnelle Sache.
Wenn dabei auch noch pro Zeile Datenbankabfragen gemacht werden, was dann bei Singlethread Verarbeitung ebenfalls bremst, ist klar warum es nur solangsam ist.

Anbei macht es doch Sinn anstelle des CsvReaders direkt einen StreamReader zu nutzen.
Zwar kennst du dann die Anzahl der Zeilen nicht sofort, hier musst du erst alle Zeilen eingelesen haben, gleiches wird der CsvReader aber auch machen.

Und einen Grund zum wechsel vom StreamReader zu irgendwelchen Custom Reader habe ich bisher nicht gehabt und es wird diese auch nicht geben.

Nachtrag:
Die Caching Ideen von Abt halte ich auch für eine gute Idee.
Ich denke aber, dass du dann genügend RAM haben solltest.

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

W
872 Beiträge seit 2005
vor 11 Jahren

Multithreading wuerde ich erstmal aussen vor lassen.
Wuerde an Deiner Stelle erst alle Zeilen einlesen und dann mit nur einer Abfrage alle relevanten Objekte in einer Liste mit NHibernate rausholen - wahrscheinlich hat jede NHibernate Anfrage einen Overhead.
Den Vorschlag von T-Virus mehr in der Datenbank zu arbeiten, halte ich auch fuer gut.
Welche Datenbank benutzt Du?

106 Beiträge seit 2011
vor 11 Jahren

Hallo chavez,

Als erstes solltest du klären welcher Schritt wie lange braucht.
Ich kenne deinen CSV-Reader nicht, aber ich habe sehr gute Erfahrung mit FileHelpers gemacht, sollte dein Reader probleme bereiten, schau dir einfach mal den anderen an.

Nun solltest du dir eine Strategie zum laden/speichern der Entitäten machen.
Wenn ich das richtig verstanden habe, so lädst du zu jeden Datensatz in der CSV-Datei auch die zugehörige Entität aus der DB, das macht locker 15Mio abfagen.
Es ist nicht genau zu entnehmen, welche Loading Strategie du verfolgst(Eager oder Lazy), aber da du die einzelnen Titel jedes Albums auch noch dazu holst, hast du wohl ca. 150Mio Entitäten die du aus der DB lädst(ich gehe davon aus das ein Album 10 Titel hat). Sollten die Titel des Albums als Lazy sein, so wird zu der Abfage die das Album holt, auch noch eine weitere Abfrage gestartet. Dies kannst du relativ einfach mit Eager umgehen.

Wichtiger Hinweis
Lade dir den NHibernate Profiler runter (gibts als 30 Tage Trial.) und schau dir genau an, was NHibernate pro abgearbeiter CSV-Zeile macht, dies kannst du mit 15Mio multiplizieren und erhälst die Anzahl der Abfragen die an die DB gestellt werden.
Hierbei wirst du festellen, das selbst bei nur 2 Anfagen pro CSV-Zeile immerhin noch 30Mio Abfragen bestehen, die eindeutig zu viel sind, gerade in Anbetracht des Zeitlimits den du hast.

Überlege dir wie du die Anfagen batchen bzw. Pagen kannst.
Als Beispiel: mache nicht zur jeder CSV-Zeile eine Abfrage an die DB sondern nur alle 20 CSV-Zeilen und sammle die Informationen die du dafür brauchst (z.B. die Ids(das setzt natürlich vorraus das in der CSV-Datei die PKs oder irgendwelche anderen eindeutigen Henkel zu den Entitäten stehen) der Entitäten). Nun erstelle dir ein Query in dem du dir die Entitäten von den Ids ausgeben lässt die du zuvor gesammelt hast, und achte darauf das die Musiktitel im Album Eager sind, damit du nicht in das N+1 Dilemma reinschlitterst.
Anschliessend machst du deine Berechnungen auf den Entitäten und schickst ein einzelnes Speichern für die 20 Entitäten ab.

Somit hättest du aus 40 DB-Abfragen(Laden und Speichern), kurzerhand 2 gemacht. Das sollte deine Performance schonmal ordentlich steigern.

NHibernate und Caching

das Ergebnis der DB Abfrage wird in einem Dictionary gespeichert. Somit wird für alle weiteren Zeilen der CSV geprüft ob das Album/Track schon mal geladen wurde und falls ja eben keine Anfrage an die DB geschickt.

Darauf kannst du verzichten. NHibernate bringt bereits von Hause aus Caching mit, das sich eigentlich um alle belange kümmert. Nur muss man dabei auch einige Eigenheiten von NHibernate in kauf nehmen.
Wenn du sachen aus der DB lädst, werden Sie automatisch in der betreffenden Session gecached. Die kann bei vielen Subentitäten, Listen und anderen abhängigkeiten schnell in die 1000er oder noch höher gehen. Und je voller die Session ist, desto langsamer ist sie auch, da jede Abfrage die du an die DB stellst, zu erst im internen Cache überprüft wird, ob diese Entität neu gelanden werden muss oder nicht.
Hier gibt es aber schönerweise "Session.Clear()", der dafür sorgt das die Session wieder gelerrt wird und performant arbeitet. Musst mal ein bisserl rumtesten, je nachdem wie viele Entitäten er pro Anfrage reinlädt, aber schätze du könntest alle 100 CSV-Zeilen einen Clear() aufrufen.

Ich habe in der Vergangenheit auch versucht mit lokalen Chaches, ähnlich wie du mit dem Dictionary, zu arbeiten. Aber erfahrungsgemäß ist es bedeutend performanter das von NHibernate machen zu lassen und nur in regelmäßigen Abständen ein Clear() aufzurufen.

Cascade
Überprüfe dein Mapping der Entitäten.
Dies kann auch eine sehr große Performancebremse sein. Z.b. wenn die Entitäten der Musiktitel im Album auf "cascade=all" (oder save-update) gesetzt sind. Dies kann beim falschen Umgang unnötig viele Updates auf den Entitäten hervorrufen(selbst bei Selects), obwohl keine Änderungen vollzogen wurden.
Erfahrungsgemäß sollte man beim mappen von Collections nur "delete-orphan" benutzen, ein "all" oder "all-delete-orphan" ersparrt dir zwar beim erstellen/speichern der Entitäten ein kleines bisserl schreibarbeit, aber wenn man korrekte Zeitstempel für Änderungen auf einer Entität speichern soll(Auditing), ist dieses Autosave mehr als hinderlich.

Warum arbeitest du eigentlich mit einem DataTable und SqlBulkCopy?

OffTopic:
Ich beschäftige mich derzeit sehr intensiv mit der Optimierung von NHibernate und hatte ins Auge gefasst, mal einen größeren Artikel über die grundsätzliche herangehensweise, Best Practices und Optimierungen von NHibernate zu verfassen. Besteht da grundsätzlich Interesse oder kann ich mir das sparen?

MfG
Rabban

Ps: Wenn du mehr hilfestellungen brauchst, so solltest du jeden deiner Schritte ausführlicher beschreiben und auch die genauen Problemzonen benennen. Sonst können wir leider nur raten.

Pps: vom Multithreading(Nebenläufigkeit) würde ich dir auch abraten.

C
chavez Themenstarter:in
252 Beiträge seit 2007
vor 11 Jahren

Super, vielen Dank.
Das bringt mich schon ein ganzes Stück weiter.

Noch ein wenig zum Hintergrund.
Wie gesagt handelt es sich hierbei um Verkaufszahlen. Wir bekommen derzeit ca 50 verschiedene csv/txt Dateien in unterschiedlichen Formaten(andere Header, Trennzeichen, etc) und einigen Eigenheiten. Nicht alle sind immer well-formed. Darum auch der Reader und nicht nur ein einfacher StreamReader.
Der Reader liest jetzt eben abhängig vom File die jeweiligen Werte, die benötigt werden, raus(Dazu unten mehr). Als Identifier dient hier der EAN/ISRC(falls sich jemand ein wenig im Msuic-Business auskennt) und anhand dessen Stelle ich die Abfrage via nHibernate an die DB um das Album zu erhalten, denn anhand der Trackanzahl und Spieldauer der jeweiligen Tracks werden dann eben die Berechnungen durchgeführt.
Sprich hier brauch ich sowieso immer gleich die Tracks dazu, also eager loading. 15mio Anfragen kommen hier nicht zustande, da viele Zeilen im CSV ident sind.
Also in der csv steht in Zeile 1: Album A hat sich in Deutschland 100 mal verkauft => Anfrage via nHibernate an die DB um welches Album es sich handelt
Zeile 2: Album A hat sich in Österreich 50 mal verkauft => keine Anfrage an DB, da ich das Album bereits in meinem Dictionary hab.
Zeile 3: Album B ... => Anfrage an DB

Unsere Tabelle mit den Alben/Tracks umfasst ca 500.000/5.000.000. Somit können derzeit nur max 500.000 Anfrage während des Auslens der CSV an die DB gestellt werden. Wie gesagt wächst die natürlich auch kontinuierlich. Darum will/wollte ich die nicht einfach vorab laden sondern eben nur pro Zeile bzw die Alben/ Tracks die auch verkauft wurden. Sonst hätte ich 5,5 Mio Objekte im Speicher und verkauft wurden dann aber nur 1000 unterschiedliche.
Hier werde ich mich mal mit dem nhibernate Profile einklinken und gucken was da wirklich so abgeht.

Caching
Das mit dem Clear passiert noch nicht. Das werde ich auch mal testen und etwas rumspielen.

Cascade
Mapping werde ich auch nochmals prüfen. Mit nHibernate lade ich eben nur die Informationen zu den jeweiligen Tracks/Alben. Ich führe nur Selects keine Insert/Update/Delete aus.
Wie oben erwähnt brauch ich aber sowieso alle Tracks des Albums.

Die Daten werden in eine andere Tabelle geschrieben.
Mein Datatable hat eigentlich nur folgende Spalten:
EAN: der wird aus der csv
ISRC: ebenfalls csv
Stückzahl: ebenfalls csv
AlbumTitel: kommt eben via nhibernate-Abfrage
TrackTitel: ebenfalls nhibernate
Label: ebenfalls nhibernate
und dann eben noch 3 double-Spalten: das sind die Werte die ich berechne

Deswegen verwende ich eben ein Datatable das ich zur Hälfte mit Daten aus der CSV und zur anderen Hälfte mit den Album- und Track-Informationen und meinen Berechnungen befülle.
Das schien mir in meinem Fall am performantesten. Spricht etwas gegen dieses Vorgehen?

Ich werde auch mal nen Vergleich zwischen meinem CSV-Reader und dem FileHelpers machen.
Werde in den nächsten Tage etliches rumtesten und versuchen nen genaueren Überblick zu geben bzw Ergebnisse zu posten.

Also ich wäre definitiv an einem Artikel zu Optimierungen von nHibernate.

PS: Heute hab ich mal nur getestet wie schnelle es der Import-Assistent aus dem SQl Management Studio schafft die 900MB und 15Mio Zeilen in die Tabelle zu packen. Das war in ca 3-4 Minuten erledigt. Wie gesagt nur die reinen Daten aus dem CSV. Keine zusätzlichen Infos und Berechnungen.

T
2.219 Beiträge seit 2008
vor 11 Jahren

@chavez
Der Import an sich ist auch nicht das Problem.
Da du ja bereits mit SqlBulkCopy arbeitest hast du auch schon den schnellsten Weg für den Datenimport.

Das Problem muss bei deiner Verarbeitung liegen.
Ich glaube das Problem ist, dass du bei jeder Zeile ggf. mit Abfragen zur DB oder dem Dictionary arbeiten musst.

Da die Berechnungen ja eigentlich nur einfaches Zeug ist, sollte es hier auch keine großen Bremsen geben.
Deshalb bleibt meine Vermutung, dass das Problem eher beim auslesen und dann gleich beim prüfen der Daten gegen die Datenbank ist.
Hier hilft es nur zu Debuggen oder alternativ mal zu schauen wie lange die Verarbeitung vom auslesen bis zum fertig speichern der Zeilen dauert.
Zusätzlich noch die Info wie lange die Datenbank abfragen dauern.
Dann weißt du eigentlich wo die Zeiten verloren gehen.

Dem CsvReader würde ich aber mal genauer Prüfen.
Da er letzendlich auch nur einen StreamReader verwendet ist es auch keine Optimale Lösung eine zwischen Lösung zu nutzen.

Hier könntest du auch schauen ob ein einfacher StreamReader schneller ist und auch speichereffizienter oder ob der CsvReader eben schneller wäre.
Letzteres bezweifle ich aber schon.

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

C
chavez Themenstarter:in
252 Beiträge seit 2007
vor 11 Jahren

So ich habe mich jetzt mal eingehend mit dem Problem beschäftigt und etliche Tests durchgeführt.

Der csv Reader ist nicht das Problem. Das Einlesen dauert ca. 3-4 Min.
Danach habe ich versucht eben nicht nach jeder gelesenen Zeile eine Anfrage an die DB zu senden sondern eben alles einzulesen und dann nur 1 Anfrage zu stellen. In der csv stehen ca 40000 verschiedene Tracks. Dies brachte auch nicht so wirklich einen Zeitsprung. Das wurde dadurch nur 30 Minuten schneller.
Als letzte Lösung habe ich dann versucht mal nHibernate ganz wegzulassen und direkt ein DataTable zu verwenden. Bis das DataTable mit den 40000 Zeilen/Tracks geladen ist dauert es ca 30 sec.

Heureka ich dachte jetzt hab ichs. Auslesen der csv in ein DataTable 3 Min + laden der 40000 Tracks in eine DataTable weitere 30 sec. Einfach nur die fehlenden Infos aus der Datatable holen und in der anderen ergänzen. Mittels Linq ja auch einfach und schnell erledigt. Das sollte nicht lange dauern und definitiv etwas bringen.

Dann auch gleich getestet und es kam die böse Überraschung. Das ganze dauert immer noch 6 Stunden. Somit hab ich fast nix gewonnen.
Nur wie kann das sein? Ich hab beide DataTable im Speicher und durchlaufe die mit 2 verschachtelte foreach Schleifen.
Hier der Code: Bis der durchgelaufen ist dauert es eben 6 Stunden.


      foreach (DataRow row in dtTracks.Rows)
      {
        DataRow row1 = row;
        var rows = dtSales.AsEnumerable().Where(r => r["isrc"].Equals(row1["isrc"]));
        foreach (var row2 in rows)
        {
          row2["artist"] = row["artist"];
          row2["title"] = row["title"];
          row2["labelcode"] = row["labelcode"];
          row2["cline"] = row["cline"];
          row2["duration"] = row["duration"];
        }
      }

dtTracks ist mein DataTable mit den 40000 Tracks/Zeilen.
dtSales ist das riesige Datatable mit den den Werten aus der csv.

Was kann ich noch machen? Ich bin grad mit meinem Latein am Ende und für weitere Ideen mehr als dankbar.
Parallel.Foreach würde mir noch einfallen. Nur kann ich darauf nicht zugreifen, da ich aufs 3.5er FW zugreifen muss. Außerdem kann ich mir net vorstellen dass ich damit von 6 Stunden auf 1 runter komme.

771 Beiträge seit 2009
vor 11 Jahren

Hi,

ich bin mir nicht sicher, wie aufwendig es ist "dtSales.AsEnumerable()" auszuführen.
Zumindestens steht folgendes in der Hilfe:

Das von der AsEnumerable-Methode zurückgegebene aufzählbare Objekt wird permanent an die DataTable gebunden, von der es erstellt wurde. Mehrere Aufrufe der AsEnumerable-Methode geben mehrere, unabhängige abfragbare Objekte zurück, die an die Quell-DataTable gebunden sind.

Ich würde das mal testweise vor die Schleife packen und in einer Variablen speichern.

F
10.010 Beiträge seit 2004
vor 11 Jahren

So etwas steht und fällt mit der Verwendeten Auflistung.

In deinem Fall gehst du 40.000 mal die gesamte riesige DataTable komplett durch. Das das nicht performant ist, ist klar.

Erstelle dir 2 Klassen Track und SalesRec.

Lies die grosse CSV gleich in ein Dictionary<string, List<SalesRec>> ein.
Als Key benutzt du "isrc".
Jetzt hast du auch gleich alle "isrc" einmalig vorhanden mit denne du die Tracks dann per SqlCommand und ExecuteReader lesen kannst.

Da du die Daten ja nicht wirklich aufbewahren musst, kannst du hier gleich in der Read Schleife per Dictionary["isrc"].ForEach auf die SalesRecs zugreifen.

Auch das schreiben in die DB ist dann schnell gemacht( ( Transaction nicht vergessen ).