performance von MS Access beim importieren von Textzeilen

[Gerhard Wascinski]

Hallo Leute,

Eine Routine in einem Delphieprogramm (XE6) erstellt eine Textdatei, wo Daten für eine Accesstabelle erzeugt werden.
z.B. Zeile: "Wert Feld1,Wert Feld2,...."
Zur Zeit schreibe ich mit Appendrecord die gelesenen Zeilen in die Tabelle der Accesstabelle.
Das funktioniert ganz gut.
Allerdings ist der Weg sehr langsam.
Daher habe ich versucht, das Schreiben in die Accesstabelle über den SQL Befehl "insert into Tabelle (Feld1,Feld2) values (Feld1,Feld2....) zu realisieren.
Das funktioniert auch gut, aber immer noch zu langsam.
Nun suche ich einen Weg, wie man aus einer Textdatei zeilen lesen kann und sehr schnell in eine Tabelle von Access schreiben kann.
Hat vielleicht jemand eine Idee wie man das perfomant hinkriegt?

Vielen Dank im vorraus...

[Wernfried]

Nimm eine beliebige "richtige" Datenbank anstatt MS Access....

Wie sieht denn dein Code aus mit dem du ins Access schreibst?

Gruss

[Gerhard Wascinski]

der codeschnipsel sieht so aus:

ADOCommand.CommandText:='insert into rtriev (Knt,Zeile) values ('+inttostr(VtRtriev.FieldByName('Knt').AsInteger) +','+chr(39)+Zeile+chr(39)+')';
ADOCommand.Execute;

Tabellenname ist "RTRIEV" Ein wert für die Tabelle ist KNT: kann auch aus einer Textzeile kommen und per TAB separiert werden.
Der zweite Wert kommt aus der Variablen "Zeile". Zeile aus der Textdatei.

Nun kann der 1. Wert auch mit aus der Textdatei kommen, was wohl nötig ist, wenn ich eine komplette Textdatei Zeilenweise einlesen möchte.

Gruss Gerhard

[Gerhard Wascinski]

Was aus der Textdatei kommt sieht so aus:

1|S92100:0,670;
1|S92100:1,3000;
1|S92100:2,810;
1|S92100:3,460;
1|S92100:4,6;
1|S92100:5,6;
1|S92100:6,4;
1|S92100:7,0;
1|S92100:8,0;
1|S92100:9,100;
1|S92100:10,9984;
1|S92100:11,255;
1|S92100:12,0;
1|S92100:13,512;
1|S92100:14,8;
1|S92100:15,400;
1|S92100:16,31;
1|S92100:17,1000;
1|S92100:18,50;
1|S92100:19,6;
1|S92100:20,999;
1|S92100:21,999;
1|S92100:22,512;
1|S92100:23,512;
1|S92100:24,512;
1|S92100:25,14;
1|S92100:26,14;
1|S92100:27,9984;
1|S92100:28,255;
1|S92100:29,255;
1|S92100:30,4496;
1|S92100:31,20;
1|S92100:32,255;
1|S92100:33,100;

es können so gesehen auch mehr als 200.000 Zeilen sein.... daher die Frage nach der Performance.

für die Accesstabelle "RTRIEV" gibt es 2 Spalten: "KNT" und "Zeile".

im Moment zerpflücke ich die Zeile aus der Textdatei und schreibe die beiden Werte (KNT,Zeile) per ADOCommand in die Tabelle.

Ich hoffe es wird verständlicher...

Gruss Gerhard

[defo]

Ich hoffe es wird verständlicher...

Kannst Du nicht die Textdatei in Access importieren? Das muss doch flott gehen.
Eine richtige DB wäre sicher auch toll, ändert aber wahrscheinlich nicht viel am Verfahren.

Schau mal hier z.B.
http://stackoverflow.com/questions/3...sv-file-in-vba

Ich würde allerdings versuchen, das Ganze mit einem 'Insert into T () Select * from <csvfile>' zu machen.

[Ralf Jansen]

Eine richtige DB wäre sicher auch toll, ändert aber wahrscheinlich nicht viel am Verfahren.

Nun ja es gibt Verfahren die bei einer Desktopdatenbank einfach nicht funktionieren können.

Wo liegt den die Accessdatenbank relativ zu deiner Anwendung? Etwa auf einem Netzlaufwerk? Dann kannst du optimieren wie du willst der Bottleneck ist woanders

[defo]

Nun ja es gibt Verfahren die bei einer Desktopdatenbank einfach nicht funktionieren können.

Wo liegt den die Accessdatenbank relativ zu deiner Anwendung? Etwa auf einem Netzlaufwerk? Dann kannst du optimieren wie du willst der Bottleneck ist woanders

Im Prinzip hast Du Recht, aber ein paar MB übers Netz wären jetzt noch keine Katastrophe. In meinen Augen ist das satzweise Gehampel dann aber sehr ineffizient. Bzw. Satzweiser Import ist sowieso ineffizient, plus Netzlaufwerk wäre es keine gute Kombination.

Und wie gesagt, nur die DB zu tauschen, macht ja den Code bzw. das Importverfahren nicht besser. Also kann man ganz beruhigt auch nach anderen Verfahren schauen.

[Wernfried]

Es gibt vermutlich mehrere Stellen welche für die schlechte Perfomance verantwortlich sind.

Als erstes würde ich es mal mit einem parametrisierten Statement versuchen. Es dürfte ungefähr so aussehen:

 
ADOCommand.CommandText := 'insert into rtriev (Knt,Zeile) values (:Knt, :Zeile)';
ADOCommand.Parameters.AddParameter;
ADOCommand.parameters[0].Name := 'Knt';
ADOCommand.Parameters.AddParameter;
ADOCommand.parameters[1].Name := 'Zeile';

repeat 
	"lese eine Zeile der Datei"
	ADOCommand.Parameters[0].Value := inttostr(VtRtriev.FieldByName('Knt').AsInteger);
	ADOCommand.Parameters[1].Value := Zeile;
	ADOCommand.Execute;
until ...;

Die Syntax stimmt vermutlich nicht ganz aber du solltest eine Idee davon haben was ich sagen will.

Gruss

[Gerhard Wascinski]

Hintergrund des Tools ist folgendes:
Wir machen Support für Kommunikationstechnik.
Um allen Technikern einen schnellen Überblick über die Konfigurationen der Komponenten zu ermöglichen schreibe ich ein kleines Tool, was die Konfiguration graphisch auf dem Laptop darstellen soll.
Dazu braucht der Techniker die Datensicherung der Komponente.
Diese Datensicherung erstellt die Komponente in die oben gezeigte Textdatei.
Diese wird dann in die Datenbank importiert und analysiert.
Daher scheidet ein komplexe Datenbank wie MySQL oder anderes aus, da eine mdb Datei einfach auf dem lokalen PC erstellt wird und ggf. weiter gegeben werden kann.
Nur wenn die Datensicherung 100.000 Zeilen oder mehr hat, dann nutzt niemand das Tool, weil es einfach zu lange dauert.
Mit MySQL habe ich es probiert und es geht rasend schnell. Nur kann ich nicht jedem Techniker erstmal eine SQL Datenbank installieren lassen.
Soviel zum Hintergrund.

Zu Defo:

Mit dem Kommando "insert into.... select * from...."
habe ich es versucht. Bei SQL funktioniert das, aber komischerweise nicht bei Access. wo der Fehler liegen soll ist mir völlig schleierhaft.
vielleicht hast Du einen Codeschnipsel für Access.. wäre nett..

Den Vorschlag von Wernfried probiere ich mal aus, mal sehen wie dann die Performance ist..


Danke für eure Tips...

Gruss Gerhard

[defo]

Nur wenn die Datensicherung 100.000 Zeilen oder mehr hat, dann nutzt niemand das Tool, weil es einfach zu lange dauert.
Mit MySQL habe ich es probiert und es geht rasend schnell. Nur kann ich nicht jedem Techniker erstmal eine SQL Datenbank installieren lassen.
Soviel zum Hintergrund.

MySQL würde ich spätestens seit der Übernahme durch Big O auch nicht empfehlen.
Es gibt sicher gute Alternativen, z.B. Firebird, gerade frisch in V 3.0. Das kann embedded betrieben werden. D.h. für Dich, Du lieferst mit Deinem Tool eine DLL und ggF. fertige oder template DB Dateien aus und kannst mit einem richtigen DB Server arbeiten (die lokale DLL). Das Kopieren und Weiterverwenden von Datenbanken (also Dateien) sollte damit auch kein Problem sein.
Solche Systeme verfügen idR auch über sehr performante Textimportfunktionen, falls überhaupt nötig. Weiß ich aber gerade bei FB nicht auswendig, wie die Syntax lautet.

[Wernfried]

Dann nimm SQLite: https://www.sqlite.org/
Dort bekommst du mit einem einfachen Laptop die 100'000 Zeilen in weniger als 1 Sekunde in die Datenbank geschrieben.

Hier gibt es ein gutes Beispiel, das Programm macht genau das was du auch machen möchtest: http://stackoverflow.com/questions/1...ance-of-sqlite


Gruss

[Gerhard Wascinski]

Hallo Wernfried,
das mit SQLite ist eine sehr gute Idee.
Es scheint wirklich sehr schnell zu sein. Ich habe eine Testroutine geschrieben, die die Textzeilen schnell in die Datenbank schreibt.
Da ich mich aber mit SQLite noch nicht intensiv beschäftigt habe kommt es nun zu folgendem Problem, was ich mir noch nicht erklären kann.
Der Programcode funktioniert richtig und schnell.
Allerdings liest der Code nur genau 915 Zeilen ein, dann bleibt das Programm stehen.
Gibt es in SQLite irgendwelche Einstellungen, wo das Einlesen von Datensätzen begrenzt wird ?
Die 915 Zeilen scheinen fast ohne Zeitverlust in die Tabelle geschrieben zu werden. Insofern richtig flott.
Nur dann gehts nicht mehr weiter und ich muss das Tool abbrechen via Taskmanager.
Klammere ich den Code fürs Schreiben in die Tabelle RTRIEV aus, dann läuft der Code durch, also muss irgendwas von der DB kommen, was das Einfügen von Datensätzen über 915 verhindert.
Hat jemand vielleicht eine Idee ?

Vielen Dank im vorraus.

Gruss Gerhard

[Christian Marquardt]

SQLITE arbeitet erstmal im Speicher und schreibt dann die Daten auf Platte.

https://www.sqlite.org/pragma.html#pragma_cache_size

Weiterhin
https://www.sqlite.org/limits.html

UNd es wäre zu prüfen, ob das alles in einer Transaction läuft oder ob je insert eine Transaction genacht wird

[mwelling]

Ich würde bei Access in solchen Fällen das DAO benutzen.
Den folgenden Code habe ich mit 2 Mio. Datensätzen getestet, da ich eine recht schnelle Festplatte nutze.
Bei mir läuft es in ca. 10 bis 12 Sekunden durch.

Public Function ImportRTRIEV() As Integer
Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim rst As DAO.Recordset
Set rst = dbs.OpenRecordset("RTRIEV", dbOpenDynaset)
Dim strLine As String
Open "A:\rtriev.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, strLine
Dim pos As Integer
pos = InStr(1, strLine, ":", vbTextCompare)
rst.AddNew
rst(0) = Left(strLine, pos - 1)
rst(1) = Right(strLine, Len(strLine) - pos)
rst.Update
Loop
Close 1
End Function

Gruß
Michael

[Gerhard Wascinski]

Hallo Christian,
der Tip war gut. Ich habe das mal eingestellt und funktioniert zufriedenstellend. Danke Dir.