Massendaten täglich in eine DB speichern - Was macht Sinn?

[Piro]

Moin zusammen,

ich habe eine CSV Datei, die täglich erstellt wird und rund 100.000 Zeilen hat. Ich möchte die Daten in eine MS SQL DB schreiben, um Sie später besser zu verarbeiten.

Meine erste Idee war, immer die vorhandene Daten in der DB löschen und dann die neuen Daten in der DB schreiben. Das hat aber zur Folge, dass sich mein Primärschlüssel rapide erhöht und schnell an seine Grenzen stößt und überläuft.

Meine zweite Idee war, jeden Datensatz aus der Datei mit den Daten aus der DB zu vergleichen und ggf. ein Update oder Insert durchzuführen und das aktuelle Datum mit zu speichern. Nach dem Einlesen wird dann kontrolliert, welche Datensätze in der DB kein aktuelles Datum hat und wird gelöscht.

Ich tendiere zur 2. Idee. Leider sind die Kriterien für ein Update oder ein Insert nicht immer genau definiert. Es gibt keine eindeutige Identifikation.

Habt Ihr eventuell noch andere Idee, wie man am geschicktesten die Daten in die DB bekommt?

Vielen Dank im Voraus.

Sven

[fkerber]

Hi,

verstehe ich richtig, dass du die DB quasi jeden Tag komplett leerst und dann erst die Daten neu einfügst?


Edit: Was ist der primärschlüssel - einfach eine künstliche ID?

LG, Frederic

[Piro]

Das war meine erste Idee, die mir so spontan einfiel.

Ja, ich wollte erst alle Daten löschen und dann die neuen Daten speichern.

Der Primärschlüssel ist der eindeutige Index des Datensatzes, der hochgezählt wird. Gibt es in jeder DB in jeder Tabelle, um zwischen 2 Tabellen zu referenzieren.

Gruß

[fkerber]

Hi,

wenn ich dein Problem richtig verstehe (ID wird zu groß für den Wertebereich), dann könntest du nach dem Löschen doch einfach den Value wieder auf 1 setzen.

Für mySQL wäre das die Syntax:

SQL-Code:

ALTER TABLE 'tablename' AUTO_INCREMENT =1

LG, Frederic

[s.h.a.r.k]

Mal ganz blöd gefragt: Was für einen Typ hast du denn für den Primärschlüssel, dass der sehr schnell explodieren sollte? Nehme ich allein mal den Delphi Integer (und hier nicht mal unsigned) dann hast du circa 21474 Tag Zeit. Reicht dir das nicht? Dann nimm einfach unsigned int, dann hast immer das doppelte. Und in Datenbanken gibts mit Sicherheit auch ein Pendant zu Int64. Also Löschen brauchst du rein gar nichts.

Und DBMS sind zudem dafür ausgelegt eine derart große Anzahl an Daten zu halten, zumal du ja kein MySQL oder SQLite hast. MS SQL ist ein ausgewachsenes SQL-System.

[Assarbad]

Nimm doch SQLite. Dann haste sogar noch bessere Kontrolle und kannst die Dateien "wegschmeißen" (i.e. löschen) wenn nötig. Alternativ kann man sie auch sichern usw ...

Eine bessere Alternative zu CSV ist es allemal und damit haste deinen Code gleich zukunftssicher falls X-Plattform mal ein Problem werden sollte :zwinker:

Manko: auf eine SQLite-DB kann nur als Datei zugegriffen werden (nunja, zumindest ohne größere Änderungen), was auch bedeutet, daß es nur einen schreibenden Client geben kann.

[BUG]

Aber es ist schon etwas komisch das die ID überläuft, oder?
Selbst bei 100.000 neuen Einträgen pro Tag sollte es um die 100 Jahre dauern, bis ein 32bit-Ganzzahlwert überläuft.

[mkinzler]

Und wenn man einen BIGINT nimmt, hat man noch länger Ruhe.

[s.h.a.r.k]

mkinzler: Wie lange hast du denn vor zu leben!? :mrgreen:

[Piro]

Danke für die Erklärungen. War dann wohl etwas ängstlich mit meiner ID. Will nur sicherstellen, dass es auch noch nach mir läuft. :-)

Ich bin nur kein Freund von Daten rein -> raus -> rein -> und so weiter.

Ich glaube, ich werde meine 2 Idee umsetzen. Neue Daten aktualisieren bestehende Daten und nicht aktuelle Daten werden gelöscht. Das sollte das Beste sein, oder?

Mich würde mal interessieren, welche Logik ihr so verwendet für solche Aufgaben.

Vielen Dank im Voraus für die guten Antworten.

[fkerber]

Hi,

aber der Abgleich mit Daten, die eh entsorgt werden sollte doch das Ganze massiv langsamer machen, oder?


LG, Frederic

[s.h.a.r.k]

Es kommt halt drauf an, ob du deine alten Daten später noch evtl. brauchst -- für andere Abteilungen/(Jahres-)Statistiken etc. Wenn du die Daten wirklich nur einmal mit deinem Programm anfasst, dann brauchst nicht mal eine DB, außer du lässt komplexe Abfragen drauf laufen. Dann wird es wohl schneller sein die kurz in eine DB zu klopfen.

Und zum Theme löschen: Warum wirfst nicht die komplette Table weg und erzeugst danach eine neue? Geht doch schneller als löschen und PK neu setzen.

-- EDIT
@fkerber: nun ja, es sei denn man muss komplexe Suchalgos in sein Programm einbauen, die eine DB ja schon effizient erschlägt.

[alzaimar]

also ich würde folgende zwei Zeilen ausführen:

Code:

select * into Tabelle_<HeutigesDatum> from MasterTabelle where 1=0
Create Index IX_<HeutigesDatum> on bla bla feld xy usw.
bulk insert into Tabelle_<HeutigesDatum> from cSV_Datei

Die genaue Syntax ergibt sich aus Google. Ich habe genau das gemacht und das zieht mir eine 20MB CSV-Datei in ein paar Sekunden in eine MSSQL-DB.
Nun hast Du täglich eine eigene Tabelle mit Index und allem Drum und dran.
Ob Du die alten Daten löschst oder z.B .die letzten X Tage noch aufhebst, bleibt Dir überlassen und hat nix mit o.g. Import zu tun.

[Piro]

Es werden sehr komplexe Abfragen auf die Daten ausgeführt. Deshalb die DB.

Die Idee mit dem Löschen der Tabelle und einfach wieder neu anlegen, klingt interessant aber mir ist noch nicht ganz klar wie.

Ich arbeite mit SQLConnection und TDataSet. Muss ich mir ein SQL Script hinlegen, welches mir die Tabelle anlegt oder was macht Sinn?
Habe da gerade irgendwie eine Denkblockade.

[s.h.a.r.k]

Naja, du kannst doch via TQuery ein DROP TABLE und CREATE TABLE ausführen. Ich arbeite immer mit AnyDAC, daher kann ich dir nicht sagen, mit welcher Klasse/Komponente du SQL Queries auf der DB ausführen kannst.

Wobei die Idee von alzaimar nicht zu unterschätzen ist. Je weniger dein Programm machen muss, desto besser. Außer du brauchst spezielle Trigger oder dergleichen.

[Piro]

@alzaimar: könntest du mir eventuell einen beispielhafteren Code geben und kurz erklären was der macht. Der Ansatz ist echt gut.

[Bummi]

Wenn ich löschen im Zusammenhang mit Daten höre wird mir ganz schlecht.
Sammle doch einfach z.B:. mit Importdatum und wenn sich herausstellt die Datenbank unhandlich wird, exportiere alles was z.B. älter als 100 Tage ist in eine Archivdatenbank.

[s.h.a.r.k]

Daten, die nicht mehr gebraucht werden, werden gelöscht -- ob da jemandem schlecht wird bei oder nicht :mrgreen: Es muss aber von vorn herein geklärt werden, dass die Daten wirklich zu 100% nicht mehr gebraucht werdenm, das ist schon klar :)

[Hobby-Programmierer]

Zitat:

Zitat von Bummi (Beitrag 1064157)

Wenn ich löschen im Zusammenhang mit Daten höre wird mir ganz schlecht.
Sammle doch einfach z.B:. mit Importdatum und wenn sich herausstellt die Datenbank unhandlich wird, exportiere alles was z.B. älter als 100 Tage ist in eine Archivdatenbank.

:thumb: Google würde dich sofort einstellen :mrgreen:

[Bummi]

phhhh :P

[alzaimar]

Zitat:

Zitat von Piro (Beitrag 1064156)

@alzaimar: könntest du mir eventuell einen beispielhafteren Code geben und kurz erklären was der macht. Der Ansatz ist echt gut.

1. Zeile: Erzeugen einer Tagestabelle auf Grundlage einer Vorlage.
2. Zeile: Wir erzeugen einen Index
3. Zeile: Wir importieren eine CSV-Datei wuppdiwupp-und-eins-fix-drei 1:1 in die Tagestabelle

Die genaue Syntax kann ich morgen posten. Hier habe ich sie nicht. Du kannst sie aber selbst ergoogeln. "MSSQL BULK INSERT"

[Sir Rufo]

Code:

BULK INSERT

in eine frische Tabelle ist das richtige Stichwort.

Nehmen wir mal an deine Tabelle mit den Daten heißt

Code:

IMPORT_DATA

Dann würde ich wie folgt vorgehen:
1. CSV-Daten per BULK INSERT in die Tabelle

Code:

IMPORT_DATA_<YYYYMMDD>

(ersetze "<YYYYMMDD>" mit dem Tagesdatum) importieren.
2. Ist alles korrekt importiert worden dann innerhalb einer Transaktion

Code:

DROP TABLE IMPORT_DATA;
EXEC sp_rename '<Schema>.IMPORT_DATA_20101126', 'IMPORT_DATA';

Sollte der Import mal nicht funktionieren, dann sind die alten Daten noch da.

Wenn die Daten auch noch für einen Zeitraum x archiviert werden sollen, dann erzeuge eine Archiv-Tabelle mit folgenden Feldern:

Code:

Zeitpunkt [PRIMARY KEY]
ZeitID [PRIMARY KEY]  // der Index aus IMPORT_DATA
// jetzt die restlichen Felder aus IMPORT_DATA

Über den Zeitpunkt kannst du dann Reorganisieren.

Sollte es Performance-Probleme geben, ist es ratsam diese Archiv-Tabelle auf einer anderen Festplatte anzulegen.

[R2009]

Hi Piro,

nur ein kleiner Hinweis:
ich weiss nicht wie das bei MSSQL gehandhabt wird aber bei SQlite ist es so, dass gelöschte Daten nicht verschwinden, sondern nur als gelöscht markiert werden. Ein compress ist bei SQlite, zumindest von wrapper seite aus, nicht möglich (nur aus dem Kommandozeientool). Wir sind deshalb, bei SQlite, auch dazu übergegngen ganze Tabellen zu löschen,die Struktur neu zu erstellen und dann die Daten neu reinzuschreiben.Bei uns geschieht das im Monat einmal, ist also ein vergleichsweise geringer Aufwand.
Ein viel grösseres Problem als die überlaufende ID sollte die absolute Grösse deiner DB darstellen.

Grüsse
Rainer

[taveuni]

Ist ja alles ganz nett.
Aber wenn die Daten wirklich nicht mehr gebraucht werden
bitte den Tabelleninhalt mit TRUNCATE löschen.
Die Indizies bleiben erhalten, der Speicherplatz wird freigegeben
und die Aktion 100'000 Records zu löschen dauert ca 0.0023 Sekunden auf unserem Sql Server.

Also wo ist das Problem?

[alzaimar]

Zitat:

Zitat von taveuni (Beitrag 1064193)

Also wo ist das Problem?

An ganz anderer Stelle. Lies einfach den Thread durch.

[taveuni]

Zitat:

Zitat von alzaimar (Beitrag 1064195)

Lies einfach den Thread durch.

Hab ich nun noch mal gemacht.
Ich hatte das ursprüngliche Problem so interpretiert dass der Thread Ersteller
Daten in einer Tabelle hat die er nicht mehr braucht und mit neuen füllen will.
Bedenken lagen dann irgendwo bei einem Ueberlauf des Primärindexes.
Ausserdem waren dann noch Hinweise auf die Schnellste Methode.

Ich habe dann darauf hingewiesen das TRUNCATE diese beiden Probleme lösen würde.

Aber vielleicht sollte ich mich noch mehr zurückhalten und wieder arbeiten.

Sorry.

[pixfreak]

Moin zusammen,

wenn wirklich richtig löschen, dann Truncate, wie bereits gesagt....

Aber wie der Teufel es will, brauchst Du die Daten in zehn Jahren doch noch mal.
Also ein PK auf bigint Basis, bei (signed) kann er 9.223.372.036.854.775.808 Werte annehmen, bevor er überläuft (ins negative...)
Bei 100.000 Daten täglich sind das:

92.233.720.368.548 Tage

252.695.124.297 Jahre

252.695.124 Jahrtausende

...

Selbst wenn Dein Programm länger als Du lebst am Laufen bleiben muss, wird es bei selbst x mal sovielen Daten wie jetzt die jetzige und zukunftsnahe Computerarchitekturen locker überleben.:shock:

Ich würde daher auch noch das Datum speichern (vielleicht in einer zweiten Tabelle) und man kann auf die Daten dann doch noch später zugreifen oder ab einer bestimmten Zeit löschen.

Nebenbei: Maximales erreichbares Alter unserer Sonne: 11,112 Milliarden Jahre (lt. Wikipedia) Danach, bzw. "kurz" davor ist es eh egal... :wink:


VG Pixfreak

[Satty67]

[OT]

Zitat:

Zitat von pixfreak (Beitrag 1064222)

Nebenbei: Maximales erreichbares Alter unserer Sonne: 11,112 Milliarden Jahre (lt. Wikipedia) Danach, bzw. "kurz" davor ist es eh egal...

Weit davor haben wir hoffentlich andere Systeme besiedelt und die wichtigen Daten auf einem Holowürfel mitgenommen ;)
[/OT]

[shmia]

Wenn man anstelle eines AUTO-INC Feldes ein normales (Int-)Feld verwendet, dann stellt sich das Problem doch gar nicht, da man den Schlüsselwert selbst vergibt.
Beim Import zählt man einfach beginnend mit 1 nach oben.

Vorrausgesetzt man braucht die alten Daten nicht und löscht diese z.B. mit TRUNCATE TABLE.

[Piro]

Vielen vielen Dank an alle. Dank der guten Diskussion habe ich vieles gelernt.

Ich werde jetzt folgendes machen.

1. vorhandene Daten mit "TRUNCATE" löschen
2. Daten neu speichern
3. ID selber vergeben

Danke noch mal an alle. Tolles Forum.