ukulele
Datenbank-Guru
- Beiträge
- 5.250
Ich archiviere derzeit monatlich einen Datenbestand aus einer Verwaltungs-Software. Die Software liefert mir mehrere Tabellen in einer Access DB, ca. 700 MB, 8 Tabellen und vieleicht 250 Spalten pro Archivierung. Um mit den Daten Auswertungen zu fahren importiere ich sie in meinen SQL Server (in diesem Fall MSSQL).
Bei den Daten handelt es sich um Auftrags- und Zeitdaten. Die Datensätze ändern sich selten, werden aber bei jedem Export komplett übergeben. Die Veränderungen betreffen eigentlich immer nur die laufenden Aufträge, sind dann irgendwann abgeschlossen und werden wie bereits mehrere zurückliegende Jahre einfach nur noch in der aktuellen Version aus der Software exportiert.
Ausgewertet wird nach Zeiträumen, pro Zeitraum mache ich einen Export und Import. Wenn ich jetzt jedesmal alle Daten mit ihrem aktuellen Datensatz in die DB schmeisse bläht sich das natürlich ganz ordentlich auf. Auch Datensätze die nicht von Veränderungen betroffen wären würden erneut gespeichert. Wenn ich es komplett zerlege so und nur die Atribute die sich ändern in Logtabellen schreibe habe ich die perfekt realtionale und speicherschonende Datenhaltung aber nen Riesenaufwand bei jeder Auswertung.
Als naheliegend empfand ich daher die Datensätze in layout-identische Tabellen zu überführen und mit Zeitstempel (import_datum) zu versehen. Ein neuer Datensatz wird nur dann generiert wenn sich etwas geändert hat. (Einmal erstellte Aufräge werden übrigens nie gelöscht.) Jetzt hat meine Auftragstabelle bei 3 Importen 19.989 verschiedene Aufträge und 21.595 Versionen (also Datensätze absolut).
Zum weiterarbeiten habe ich mir zwei Sichten erstellt. Einmal für den jeweils aktuellesten Datensatz zu jedem Auftrag und einmal für jeden Zeitstempel alle zugehörigen Datensätze, unabhängig davon ob sie sich geändert haben oder nicht. Heißt: Wenn ich einen Datensatz mit Zeitstempel 1 eingetragen habe und der beim nächsten Import festgestellt habe das er sich nicht verändert hat, gibt es ihn nur mit Zeitstempel 1. Meine sicht stellt ihn aber auch für Zeitstempel 2 mit den gleichen Daten wie in Datensatz 1 dar. Das kommt dann in Summe auf 57.593 Einträge, was exakt der Anzahl an Einträgen entsprechen müsste die ich erhalten hätte, wenn ich mit jedem Import alle Daten in die Tabelle geschrieben hätte.
Beispiel Auftragstabelle:
import_datum, pk, Auftragsnummer, Name
01.12.2013, 1, 201301, alter Auftrag
01.12.2013, 2, 201312, nicht ganz so alter Auftrag
01.01.2014, 2, 201312, nicht ganz so alter Auftrag geändert
01.01.2014, 3, 201401, neuer Auftrag
Anzeige aktueller Versionen (Sicht 1):
import_datum, pk, Auftragsnummer, Name
01.12.2013, 1, 201301, alter Auftrag
01.01.2014, 2, 201312, nicht ganz so alter Auftrag geändert
01.01.2014, 3, 201401, neuer Auftrag
Anzeige Versionsverlauf (Sicht 2):
01.12.2013, 1, 201301, alter Auftrag
01.01.2014, 1, 201301, alter Auftrag
01.12.2013, 2, 201312, nicht ganz so alter Auftrag
01.01.2014, 2, 201312, nicht ganz so alter Auftrag geändert
01.01.2014, 3, 201401, neuer Auftrag
Nun laufen meine Auswertungen eigentlich alle über die Sichten, je nachdem was ich grade brauche. Sicht 2 ist dabei nicht ganz einfach und ich wollte hier mal fragen ob jemand Optimierungspotenzial sieht.
Bei den Daten handelt es sich um Auftrags- und Zeitdaten. Die Datensätze ändern sich selten, werden aber bei jedem Export komplett übergeben. Die Veränderungen betreffen eigentlich immer nur die laufenden Aufträge, sind dann irgendwann abgeschlossen und werden wie bereits mehrere zurückliegende Jahre einfach nur noch in der aktuellen Version aus der Software exportiert.
Ausgewertet wird nach Zeiträumen, pro Zeitraum mache ich einen Export und Import. Wenn ich jetzt jedesmal alle Daten mit ihrem aktuellen Datensatz in die DB schmeisse bläht sich das natürlich ganz ordentlich auf. Auch Datensätze die nicht von Veränderungen betroffen wären würden erneut gespeichert. Wenn ich es komplett zerlege so und nur die Atribute die sich ändern in Logtabellen schreibe habe ich die perfekt realtionale und speicherschonende Datenhaltung aber nen Riesenaufwand bei jeder Auswertung.
Als naheliegend empfand ich daher die Datensätze in layout-identische Tabellen zu überführen und mit Zeitstempel (import_datum) zu versehen. Ein neuer Datensatz wird nur dann generiert wenn sich etwas geändert hat. (Einmal erstellte Aufräge werden übrigens nie gelöscht.) Jetzt hat meine Auftragstabelle bei 3 Importen 19.989 verschiedene Aufträge und 21.595 Versionen (also Datensätze absolut).
Zum weiterarbeiten habe ich mir zwei Sichten erstellt. Einmal für den jeweils aktuellesten Datensatz zu jedem Auftrag und einmal für jeden Zeitstempel alle zugehörigen Datensätze, unabhängig davon ob sie sich geändert haben oder nicht. Heißt: Wenn ich einen Datensatz mit Zeitstempel 1 eingetragen habe und der beim nächsten Import festgestellt habe das er sich nicht verändert hat, gibt es ihn nur mit Zeitstempel 1. Meine sicht stellt ihn aber auch für Zeitstempel 2 mit den gleichen Daten wie in Datensatz 1 dar. Das kommt dann in Summe auf 57.593 Einträge, was exakt der Anzahl an Einträgen entsprechen müsste die ich erhalten hätte, wenn ich mit jedem Import alle Daten in die Tabelle geschrieben hätte.
Beispiel Auftragstabelle:
import_datum, pk, Auftragsnummer, Name
01.12.2013, 1, 201301, alter Auftrag
01.12.2013, 2, 201312, nicht ganz so alter Auftrag
01.01.2014, 2, 201312, nicht ganz so alter Auftrag geändert
01.01.2014, 3, 201401, neuer Auftrag
Anzeige aktueller Versionen (Sicht 1):
import_datum, pk, Auftragsnummer, Name
01.12.2013, 1, 201301, alter Auftrag
01.01.2014, 2, 201312, nicht ganz so alter Auftrag geändert
01.01.2014, 3, 201401, neuer Auftrag
Anzeige Versionsverlauf (Sicht 2):
01.12.2013, 1, 201301, alter Auftrag
01.01.2014, 1, 201301, alter Auftrag
01.12.2013, 2, 201312, nicht ganz so alter Auftrag
01.01.2014, 2, 201312, nicht ganz so alter Auftrag geändert
01.01.2014, 3, 201401, neuer Auftrag
Nun laufen meine Auswertungen eigentlich alle über die Sichten, je nachdem was ich grade brauche. Sicht 2 ist dabei nicht ganz einfach und ich wollte hier mal fragen ob jemand Optimierungspotenzial sieht.
Code:
SELECT tdatum.import_datum,
tdaten.*
FROM ( SELECT DISTINCT
import_datum
FROM [dbo].[datev_dbauftraege] ) tdatum
LEFT JOIN [dbo].[datev_dbauftraege] tdaten
ON tdatum.import_datum = tdaten.import_datum
OR tdatum.import_datum > tdaten.import_datum
AND NOT EXISTS ( SELECT 1
FROM [dbo].[datev_dbauftraege]
WHERE ANLAGEJAHR = tdaten.ANLAGEJAHR
AND AUFTRAGSNUMMER = tdaten.AUFTRAGSNUMMER
AND import_datum = tdatum.import_datum )