File System sync zur Datenbank

[Der_Knob]

Hallo miteinander,

ich suche nach einer Möglichkeit das updaten meiner Datenbank zu beschleunigen.

Ausgangssituation:
Ich habe habe eine Datenbank (SQLite) mit einer "media" table in der alle filenames von audio files/videos eingefügt werden.
Für die mp3's werden die id3 tags ausgelesen um später schneller darüber suchen zu können und auch noch mit eingefügt.

Aktuelle Lösung:
Um die Datenbank zu aktualisieren (oder beim ersten create) gehe ich über die media folders (die der User konfiguriert hat) und suche alle audio files/videos (FindFirstFile() / FindNextFile()).
Ich checke ob das "modified Date" neuer als der letzte Datenbank update Lauf ist.
Falls ja, dann checke ich ob die Datei schon in der Datenbank ist - Ja => Update / Nein => Insert

Soweit so gut...

Um mit zu bekommen ob Files gelöscht wurden setzte ich zuerst bei allen Datensätzen die Spalte "delete" auf true.
Für jedes File was ich finde, mache ich ein update um das "delete" flag wieder auf false zu setzen. Falls ich ein Update oder Insert mache weil das "modified Date" neuer ist, setzte ich das flag gleich richtig mit.
Am Ende lösche ich alle records welche noch das "delete" flag gesetzt haben.
=> Ich glaube das setzten des "delete" flags schluckt die meiste Performance...

Im Grunde läuft es gut und zuverlässig... aber bei 60 GB Musik braucht der Process schon ein paar Stunden (abhängig vom PC).
Meine Frage ist nun, fällt jemanden was dazu ein um das ganze zu beschleunigen?

[SideWinder]

Falls du tatsächlich die MP3-Dateien selbst (und nicht nur die Meta-Informationen) in die Datenbank schiebst (warum auch immer das gut sein soll...), darf dich das nicht wundern.

Das kann dann ja kaum schneller sein als die Daten von einem Ordner der Festplatte zu einem anderen zu kopieren, das dauert bei 60GB auf einer gewöhnlichen HDD nunmal so lange...

Mein Vorschlag: Nur die Meta-Informationen (ID3-Tags) in der DB speichern.

MfG SideWinder

[Der_Knob]

äh... hab mich vielleicht etwas undeutlich ausgedrückt

Natürlich schreib ich nur den Filename + Meta Infos in die Datenbank.

[Der_Knob]

Das ganze Update um das delete flag auf false zu setzten kostet Zeit...
Meistens müssen die Files ja nicht gelöscht werden, ist ja eher die Ausnahme.

Mh, was mir gerade eingefallen ist, alles updaten (ohne das delete flag) und am Schluß alle Files aus der db holen und checken ob sie noch existieren und dann nur die löschen, die nicht existieren... das KÖNNTE schneller sein

[SideWinder]

Nein, das wird auch nicht schneller sein. Für viele Dateien dauert das halt eine Weile. Falls dein Programm nicht ununterbrochen läuft helfen dir auch Hooks nichts.

Besser wird es sein diesen Task (a) im Hintergrund laufen zu lassen (schau dir bspw. mal das Vorgehen bei Windows Media Player, iTunes, etc. an). Für bessere Usability kannst du dann im ersten Anlauf nur die Dateien in dein Programm laden (der User bekommt sie angezeigt und kann sie abspielen) und erst dann in einem weiteren Schritt Nach und Nach alle ID3-Tags auslesen (was wahrscheinlich länger dauert).

MfG SideWinder

[Denker]

Tolle Antworten aber allesamt eine halbgare und vor allem peinliche Mischung von Unwissen. Aber Hauptsache mal was gepostet.

Zuerst wäre es mal interessant zu wissen, wobei du die meiste Zeit verplemperst. Eventuell bei Inserts an bestimmte Positionen?

Dann liegt da dein Problem: das Element der Tabelle, welches eine Position identifiziert (was ist das, dein Dateiname?) sollte irgend wie Unique oder noch besser der Primary Key sein. Hast du das nicht, vervielfacht sich die Zugriffszeit, weil tatsächlich (fast) alle Datensätze durchsucht werden müssen - ansonsten kommt ein clevererer Zugriffsalgorithmus zum Zuge.

[Der_Knob]

@SideWinder:
Der Datenbank update Prozess läuft schon als Thread im Hintergrund
Wir automatisch alle 5 Tage angestoßen und falls er beim letzten mal nicht fertig war, wird er kurz nach dem App Start gleich wieder gestartet.
Mh ja, ich glaub auch fast das ich das nicht mehr schneller hin bekomme.
Das mit den zwei Schritten überleg ich mir mal das könnte sinnvoll sein.

Das Problem is halt hauptsächlich bei ganz alten PC ... 1 GB Ram und Intel Atom N270 CPU. Da frist der Prozess schon einiges an Zeit
Aber viele Daten brauchen halt um verarbeitet zu werden...

@Denker:
Ich bin mal davon ausgegangen das es klar ist, das ich mich etwas mit DB's auskenne... aber trotzdem danke für den Ratschlag trotz deines Halbwissen um die Situation
Primary Key ist vorhanden. Du gehst richtig in der Annahme das der Filename der PK ist und damit natürlich auch Unique.

[SideWinder]

Tolle Antworten aber allesamt eine halbgare und vor allem peinliche Mischung von Unwissen. Aber Hauptsache mal was gepostet.

Welche Meinungen meinst du denn da nun genau? Ich habe selbst gerade eine Applikation geschrieben die Ähnliches für Bilder macht und glaube da die Performance-Schwachpunkte schon ziemlich gut gefunden zu haben.

@Der_Knob: Schau mal in der SQLite-Doku nach ob nicht evtl. ein Integer als PK schneller ist. Der String muss zumindest noch gehashed werden.

MfG SideWinder

[hustbaer]

SQLite wird unglaublich langsam, wenn man viele einzelne INSERT/UPDATE/DELETE macht. (OK, fast alle Datenbanken werden dann langsam, aber bei SQLite hatte ich den Eindruck dass es viel schlimmer ist als bei z.B. MSSQL)

Lösung:

Mach weniger INSERT/UPDATE/DELETE.
Die Frage ist jetzt wie, aber die Antwort ist glücklicherweise relativ einfach: mehrere Statements zu einem grösseren zusammenfassen.

Das setzen des "delete" Flags sollte bereits ein Batch-Statement über alle Datensätze auf einmal sein. Bei SQLite kann man da vermutlich nicht mehr viel rausholen. Bei anderen Datenbanken die "schlauer" joinen können, könnte man statt dem "delete" Flag eine eigene Tabelle mit nur einer Spalte verwenden, wo man z.B. den Primary-Key von Datensätzen reinschreibt die nicht gelöscht werden sollen. (Oder den Filenamen von Dateien die noch vorhanden sind)
Zum Löschen würde man dann einen "anti-join" mit dieser Tabelle machen, also ein "DELETE ... WHERE bla NOT IN (SELECT bla FROM die_einspaltinge_tabelle)".

Was den Rest angeht: statt dass du jede gefundene Datei sofort in die Datenbank einfügst, sammelst du gefundene Dateien, bis du z.B. 1000 oder 10000 beinander hast. Diese fügst du dann mit einem einzigen Statement in eine Hilfstabelle ein (Filename + Last-Modified Datum).
Das machst du so lange bis du alle Dateien da drinnen hast.
Dann kannst du mit einem einzigen SELECT (JOIN der Hilfstabelle und der MP3-File Tabelle) alle Files raussuchen deren Last-Modified Datum nicht mit dem in der Hilfstabelle übereinstimmt.

Dann liest du die MP3 Tags neu aus, und sammelst wieder die Tag-Informationen aus den Dateien, bis du eine bestimmte Menge zusammen hast. Diese fügst du dann wieder mit einem grossen INSERT in die Datenbank ein (in eine neue Hilfstabelle). Bis du alle Tags beinander hast. Und zum Schluss machst du ein grosses UPDATE mit dem du alle neuen Tags in die Haupttabelle rüberschiebst.

----

Nochmal zum Thema Geschwindigkeit von Operationen bei Datenbanken allgemein:
Langsam:

execute("INSERT [eine zeile]")
execute("INSERT [eine zeile]")
execute("INSERT [eine zeile]")

Schneller:

execute("INSERT [eine zeile]
         INSERT [eine zeile]
         INSERT [eine zeile]")

Noch schneller:

execute("INSERT [viele zeilen auf einmal]") -- Syntax varriert je nach DB, Standard SQL kann das nicht

Langsam:

execute("UPDATE [eine zeile]")
execute("UPDATE [eine zeile]")
execute("UPDATE [eine zeile]")

Schneller:

execute("UPDATE [eine zeile]
         UPDATE [eine zeile]
         UPDATE [eine zeile]")

Noch schneller:

execute("CREATE TABLE [hilfstabelle]
         INSERT [viele zeilen auf einmal] -- Daten für UPDATE in Hilfstabelle hochladen
         UPDATE [viele zeilen auf einmal] -- Über Hilfstabelle
         DROP TABLE [hilfstabelle]")

Alternativ kann man die Hilfstabelle auch stehen lassen, und vor & nach der Operation mit TRUNCATE ausleeren.

----

Wie gross der Unterschied zwischen den drei Variante ist, ist je nach DBMS unterschiedlich. Bei SQL Server ist z.B. das Einfügen mit Variante 3 (viele INSERTS auf einmal) sehr sehr viel schneller als mit Variante 2. Variante 1 ist dagegen nicht sehr viel langsamer als Variante 2.

Bei SQLite ist der grosse Unterschied glaube ich eher zwischen Variante 1 und 2, Variante 3 wird u.U. nicht mehr viel bringen (bzw. könnte die Sache u.U. sogar lagsamer machen).

[SideWinder]

Glaubt ihr wirklich, dass das Einfügen in eine In-Memory-Datenbank der Flaschenhals ist wenn er jedes File findet, dann die ID3-Tags (I/O von HDD!) ausliest und dann erst alles in die DB einfügt? Denkt ihr nicht ein erster Schritt wäre hier das ID3-Tag-Auslesen auf später zu verschieben?

Bzw. anders gesagt @hustbaer: Glaubst du nicht das deine Informationen vor allem für Non-In-Memory-Datenbanken zutreffen? Falls die Statements alle sehr ähnlich sind sollte der Optimizer der DB auch kaum Overhead liefern wenn man jedes INSERT einzeln abschickt, oder?

MfG SideWinder

[hustbaer]

Denker schrieb:

Tolle Antworten aber allesamt eine halbgare und vor allem peinliche Mischung von Unwissen.

Die Aussage trifft auch ganz gut auf deinen Beitrag zu.

das Element der Tabelle, welches eine Position identifiziert (was ist das, dein Dateiname?) sollte irgend wie Unique oder noch besser der Primary Key sein.

Es muss weder unique sein, noch der Primary Key. Es reicht wenn ein Index auf die Spalte existiert.

[hustbaer]

SideWinder schrieb:

Glaubt ihr wirklich, dass das Einfügen in eine In-Memory-Datenbank der Flaschenhals ist (...)

In-Memory-Datenbank?
Ich hab den Beitrag jetzt nur überflogen, aber wo steht da was von In-Memory?

Da steht SQLite. SQLite hat zwar einen In-Memory Modus, nur davon ist hier nicht die Rede. Es sei denn ich hätte es überlesen.

Bzw. anders gesagt @hustbaer: Glaubst du nicht das deine Informationen vor allem für Non-In-Memory-Datenbanken zutreffen?

Doch, glaube ich schon. Siehe oben

Falls die Statements alle sehr ähnlich sind sollte der Optimizer der DB auch kaum Overhead liefern wenn man jedes INSERT einzeln abschickt, oder?

SQLite ist was das angeht ziemlich doof

Und auch bei anderen DBMS macht es einen grossen Unterschied.

[Der_Knob]

@hustbaer: hast recht, es ist keine In-Memory-Datenbank

Werd am Wochenende mal deinen Tipp mit dem mehreren Inserts in einem execute ausprobieren.
Ich geb dann bescheid ob es was gebracht hat

@SideWinder: Das mit Integer als PK werd ich auch mal prüfen... vielleicht bringt das ja auch was - könnte ich mir schon gut vorstellen.

[Der_Knob]

Hab jetzt mal den Insert umgebaut.
Von ca. 7 min braucht er nun noch 5 min (kleine Datenmenge).

Werde jetzt mal alles umbauen noch etwas testen. Sieht aber gut aus
Danke für den Tipp!!!

[hustbaer]

Das klingt immer noch nach viel zu viel.
Oder macht der Teil den du umgebaut hast nur einen kleinen Teil der 7 Minuten aus?

[Der_Knob]

naja, es geht den mp3 Ordner durch und ließt alle id3 tags aus...
Also es macht schon einiges. Ist somit ok

[Denker]

hustbaer schrieb:

das Element der Tabelle, welches eine Position identifiziert (was ist das, dein Dateiname?) sollte irgend wie Unique oder noch besser der Primary Key sein.

Es muss weder unique sein, noch der Primary Key. Es reicht wenn ein Index auf die Spalte existiert.

Danke das du dich selbst so öffentlich bloßstellst.

Natürlich reicht ein Index auf die Spalte für die reine Funktionalität, aber eben nicht, wenn es auf Tempo ankommt. Aber ich bin mir sicher, auch darauf hast du wieder eine völlig ahnungslose Gegenantwort.

Was bist du eigentlich hauptberuflich - Müllfahrer?

[hustbaer]

Denker schrieb:

hustbaer schrieb:

das Element der Tabelle, welches eine Position identifiziert (was ist das, dein Dateiname?) sollte irgend wie Unique oder noch besser der Primary Key sein.

Es muss weder unique sein, noch der Primary Key. Es reicht wenn ein Index auf die Spalte existiert.

Danke das du dich selbst so öffentlich bloßstellst.

Natürlich reicht ein Index auf die Spalte für die reine Funktionalität, aber eben nicht, wenn es auf Tempo ankommt. Aber ich bin mir sicher, auch darauf hast du wieder eine völlig ahnungslose Gegenantwort.

Du hast noch nie mit Datenbanken gearbeitet, oder?

Was bist du eigentlich hauptberuflich - Müllfahrer?

Nein, ich nicht. Du vielleicht?

[Der_Knob]

Denker schrieb:

hustbaer schrieb:

das Element der Tabelle, welches eine Position identifiziert (was ist das, dein Dateiname?) sollte irgend wie Unique oder noch besser der Primary Key sein.

Es muss weder unique sein, noch der Primary Key. Es reicht wenn ein Index auf die Spalte existiert.

Danke das du dich selbst so öffentlich bloßstellst.

Natürlich reicht ein Index auf die Spalte für die reine Funktionalität, aber eben nicht, wenn es auf Tempo ankommt. Aber ich bin mir sicher, auch darauf hast du wieder eine völlig ahnungslose Gegenantwort.

Klar reicht ein Index um Geschwindigkeit zu steigern!
Kann ich aus Erfahrung bestätigen... dadurch haben wir in der Arbeit so manches Statement von mehreren Minuten auf Millisekunden runder gebracht!