ADO braucht ewig bei Schreiben in Access Datenbank

[Helge Vitense]

Hallo...

Ich bin im Thema Datenbank recht frisch,aber ich komme um dieses Thema notgedrungen nicht herum

Ich bin gerade dabei einen Parser zu schreiben,der Daten aus einer ASCII Datei vom "Menschen lesbaren" Format in einen "Maschinen lesbaren" Format extrahieren soll.

Das klappt mitlerweile auch recht gut.
Die geparsten Daten werden im moment in einem Stringgrid
zwischengelagert. Wenn der Parse Vorgang abgeschlossen ist,so
sollen verschiedene Daten in verschiedene Tabellen einer Accessdatenbank geschrieben werden.
Hierbei muß überprüft werden,ob die Daten schon vorhanden sind,weil sich dort Daten ändern könnten.
Ich muß also per Locate auf vorhanden sein prüfen und dann zwischen APPEND und EDIT entscheiden.

Ich habe vorher mit TDatasource gearbeitet,da hat das eintragen
von ca. 35000 Datensätzen aber 7 (!!!) Minuten gedauert.
Und das war erst eine Tabelle von vielen.

Nachdem ich mich im Internet etwas durchgewühlt habe,bin ich dann auf UPDATEBATCH gestoßen..

Der folgende Code braucht zum schreiben/ändern der rund 35000 Datensätze aber auch noch 6-7 Minuten.

Das dauert einfach zu lange,weil noch mehrere Tabellen mit
Daten gefüllt werden müßen (wenn auch nicht ganz so umfangreich).

Das ganze läuft auf W2K,Athlon64 3500+ unter Delphi 7

Hier der Code:

adodataset1.Close;
adodataset1.DisableControls;
adodataset1.LockType:=ltBatchOptimistic;
adodataset1.CommandText:='SELECT * FROM pl_zo';
adodataset1.Open;

for x:=1 to stringgrid2.RowCount-2 do
with adodataset1 do
try
if Locate('pl_nr;pl_zo_nr',VarArrayOf([strtoint(stringgrid2.Cells[0,x]),strtoint(stringgrid2.Cells[2,x]) ]),[])=false then Append else Edit;
if stringgrid2.Cells[0,x]<>'' then findfield('pl_nr').AsInteger:=strtoint(stringgrid2 .Cells[0,x]) else findfield('pl_nr').AsInteger:=-1; //Nummer Planet
if stringgrid2.Cells[1,x]<>'' then findfield('pl_dimension').Text:=Stringgrid2.Cells[1,x] else findfield('pl_dimension').text:=' ';
if stringgrid2.Cells[2,x]<>'' then findfield('pl_zo_nr').asinteger:=strtoint(Stringgr id2.Cells[2,x]) else findfield('pl_zo_nr').asinteger:=-1;
if stringgrid2.Cells[3,x]<>'' then findfield('pl_zo_namen').text:=stringgrid2.Cells[3,x] else findfield('pl_zo_namen').text:=' ';
if stringgrid2.Cells[4,x]<>'' then findfield('pl_zo_besitzer').text:=stringgrid2.Cell s[4,x] else findfield('pl_zo_besitzer').text:=' ';
if stringgrid2.Cells[5,x]<>'' then findfield('pl_zo_fus').asinteger:=strtoint(Stringg rid2.Cells[5,x]) else findfield('pl_zo_fus').asinteger:=-1;
if stringgrid2.Cells[6,x]<>'' then findfield('pl_zo_beschreibung').asstring:=stringgr id2.Cells[6,x] else findfield('pl_zo_beschreibung').asstring:='';
if stringgrid2.Cells[7,x]<>'' then findfield('pl_zo_temp').asstring:=stringgrid2.cell s[7,x] else findfield('pl_zo_temp').asstring:='';
if stringgrid2.Cells[8,x]<>'' then findfield('pl_zo_nied').asstring:=Stringgrid2.Cell s[8,x] else findfield('pl_zo_nied').asstring:='';
if stringgrid2.Cells[9,x]<>'' then findfield('pl_zo_ivo').asstring:=stringgrid2.cells[9,x] else findfield('pl_zo_ivo').asstring:='';
if stringgrid2.Cells[10,x]<>'' then findfield('pl_zeit').asinteger:=strtoint(Stringgri d2.Cells[10,x]) else findfield('pl_zeit').asinteger:=-1;
//if stringgrid2.Cells[11,x]<>'' then findfield('pl_techlevel').asinteger:=strtoint(stri nggrid2.cells[11,x]) else findfield('pl_techlevel').asinteger:=-1;

post;
except
showmessage('Problem bei schreiben der pl_zonen');
end;

adodataset1.UpdateBatch(arall);

Ich habe auch mal das gleiche versucht,indem ich per "Select * FROM pl_zo Where 1=2" die daten direkt per APPEND und OHNE LOCATE in das AdoDataset geschrieben habe.
Das hat etwas über 2 Minuten gedauert. Auch kein sehr schnelles
Resultat,allerdings besser als 6,7 oder 8 Minuten...
Allerdings wurden die Daten bei UPDATEBATCH an die schon vorhandenen Daten angehängt,was ich nicht gebrauchen kann.
Jeder Eintrag darf nur einmal vorhanden sein...

Kann mir jemand vielleicht irgendwelche Tipps geben,die auch
für Datenbank Laien verständlich sind und die die Performance erhöhen und die extremen Speicherzeiten auf ein erträgliches Maß
reduzieren ???

Danke schonmal im vorraus...

Cya de Helge

[Jochen Reichenberg]

Hm, mal ganz langsam...
.
Warum werden die Daten eingefügt, obwohl sie schon vorhanden sind? Eigentlich sollte in diesem Fall eine Exception geworfen werden, zumindest so lange Du den Primärschlüssel richtig gesetzt hast. (Oder zumindest einen entsprechenden Unique Key gesetzt hast.)
.
Grundsätzlich verbessern Primärschlüssel die Performance bei den meisten Datenbankaktionen. Also solltest Du da mal anfangen.
Bei Access sollte grundsätzlich die CursorLocation auf clUseServer stehen. Auch das steigert die Performance.
.
Da ich ein fauler Mensch bin, prüfe ich nicht, ob ein Datensatz bereits vorhanden ist, sondern füge ihn einfach ein und fange eine möglicherweise geworfene Exception ab. Sollte die Exception ein Indexfehler sein, also der Primärschlüssel schon existieren, schiebe ich einfach ein Update für den Datenssatz hinterher.
.
Sollte das alles immer noch zu langsam sein. kannst Du versuchen, statt der TADOxxxx-Komponenten, die nativen Delphi-Interfaces für ADO zu verwenden (zumindest in meinem Borland C++ Builder bringt das noch Geschwindigkeit).
.
Beispiele kann ich Dir leider nur in C++ geben. Wenn Du damit was anfangen kannst, poste ich gerne was.
.
Grüße Joche

[Karsten Gehm]

Also Locate statt Exception abfangen halte ich schon für eine gute (und schnellere) Idee. Ein Index sollte aber tatsächlich über genau die Felder auf die Du einen Locate machst vorhanden sein, das bringt bei der Datenmenge sehr viel. Auf jeden fall solltest du weiter die Felder der Tabellenkomponente hinzufügen um direkt auf die Felder zugreifen zu können, sonst wird bei 350.000 datensätzen 350.000 * jedes einzelne Feld per findfield gesucht. Zuletzt würde ich das stringgrid weglassen, der Speicherverbrauch und die performance sollten dadurch auch deutlich steigen.

Gruß
Karste

[Helge Vitense]

Hallo Jochen...

Wie gesagt,ich bin unbeleckt in Sachen Datenbanken...

Ich habe es mal mit der CurcsorLocation auf clUseServer probiert,
allerdings verträgt sich dieses nicht mit BatchOptimistic -> weil ich
einfach zu viele Datensätze habe (~35.000) und somit das Limit
für die "pending" Records überschreite...

Also ich habe deswegen mal auf OPTIMISTIC gesetzt und somit arbeitet der die CursorLocation auch Problemlos...

Die Zeit für das Schreiben in die Datenbank ist dadurch aber nicht
kürzer,sondern länger geworden... Er schreibt jetzt schon über 27 Minuten -> und ist immer noch nicht fertig...

Insofern ist dies für meine Zwecke wohl der falsche weg...

Jetzt zu den Primärschlüssel:
Ich habe mit sowas noch nicht (bewust ?) gearbeitet...
Solltest du als Primärschlüssel sowas verstehen,das im Feld
"pl_nr" die Zahl 1234 nur einmal vorkommen darf,dann nützt mir
das auch nichts...
Weil ein Planet (pl_nr -> Planet Nr) kann durchaus mehrere Zonen besitzen. (pl_zo_nr)
Es gibt dann also einen Eintrag für Planet 1234 Zone 1 und auch
einen Eintrag für Planet 1234 Zone 2 ... und Zone 6.... und Zone 12 usw. usw.

Aus diesem Grund checke ich mit LOCATE ja auch die beiden Werte,ob es diese schon gibt -> dann muß es editiert werden.
Sie müssen auch editiert werden,denn jede (Spiel) Runde
können sich die Daten der Zone ändern.

Aber vielleicht verstehe ich Dich mit dem Primärschlüssel auch falsch,wie gesagt ich hab davon keine Ahnung...

Das Problem ist,ich muß Access nutzen,und eine Vorgegebene
Datenbank beschreiben. Diese gehört zu einem Tool,welches seit
Jahren nicht mehr weiterentwickelt wird. Dadurch,das sich das
"Menschenlesbare" Format aber geändert hat,kann dieses Tool die
Daten nicht mehr selber in die Datenbank parsen.
Da dieses Tool erhaltungswürdig ist,habe ich eben die Parsing Routine neu geschrieben und versuche die Daten jetzt in dem Format in die Access Datenbank zu schreiben,das das andere Tool
damit ganz normal arbeiten kann,als wäre nichts gewesen...

Das Parsen funktioniert auch - nur mit der Datenbank habe ich
jetzt so meine Probleme

Mit C++ kann ich leider nichts anfangen
Ich bin mit Delphi schon nicht so sehr gut...

Für weitere (verständliche) Tipps dannkbar....

Cya de Helg

[Helge Vitense]

Hallo Karsten

Wie gesagt: Primärschlüssel sind für mich im moment Böhmische
Wälder,weil ich mir nicht sicher bin,wie ihr das meint...

DAs Stringgrid ist nicht das Problem....
Das Parsen einer 11 MB Datei in eben dieses Stringgrid dauert
knappe 8 Sekunden....

Das Schreiben der Daten in die Datenbank dauert nur ewig,was
wohl nicht auf das Stringgrid zurückzuführen ist

Das mit den "Festen Feldern" könnte ich in der Tat mal probieren.
Glaube aber nicht,das es einen "großteil" der Performanceschwäche
ausmacht...

Cya de Helg

[Heiko Luettge]

Hallo Helge,<p>
Lade alle Such-Daten der Tabelle (also pl_nr, pl_zo_nr) in eine Liste und benutze die Liste zum Suchen. etwa so <p>

type
TSearchData = class
pl_nr,
pl_zo_nr: String;
end;

type
TSearchDataList = class(TList)
function GetSearchData(const pl_nr, pl_zo_nr: String): TSearchData;
end;

<p>
Das Füllen erfolgt über select pl_nr, pl_zo_nr from tableXXX
<br>
das suchen in der Liste könntest du im einfachsten Fall über eine Schleife machen.
<p>
Das klappt natürlich nur, wenn zum Zeitpunkt des Updates keiner auf die Tabelle zugreifen kann (exlusives Öffnen)
<p>
Heiko
PS: Vor dem Meckern ausprobieren;

[Helge Vitense]

Moin Heiko...

Werde heute und morgen wohl nicht mehr zum probieren kommen,
aber ich werde es bei nächstern Gelegenheit einmal testen,vielleicht hilft es ja ein
wenig...

Cya de Helg

[Heiko Luettge]

Hallo Helge,<p>
das sollte es schon. Du ersetzt 35000 Lesezugriffe über das Netz durch einen, zugegeben länger dauerndend Ladevorgang (alle pl_nr, pl_zo_nr) und lokale Lesezugriffe, die aber auf dem Athlon "fast" zu sehen sind.
<p>
Heik

[Karsten Gehm]

Öffne die Tabelle in der Entwurfsansicht, klicke den Schalter vor dem ersten Feld auf das Du den Locate machst mit der Rechten Maustatse an und klicke auf primary, gehe auf den Münpunkt Ansicht > Indizies und füge die anderen Felder der Reihe nach hinzu. Danach sollte sich die Geschwindigkeit deutlich erhöhen. Die Eindeutigkeit die vorliegen muss geht dann auch über alle hinzugefügten Felder, statt locate kannst du dann also auch findkey nehmen und muss die Felder in der gleichen reihenfolge übergeben.

[Heiko Luettge]

Hallo Karsten,<p>
ist das das Vorgehen in Access oder in Delphi ?
<p>
Heik

[Karsten Gehm]

Das musst du in Access machen, damit Access diesen Index anlegt und damit arbeiten kann..

[Helge Vitense]

Hallo Heiko,Hallo Karsten...

Danke für eure Tipps und sorry,
das ich mich so lange nicht mehr gemeldet habe,aber
aus Zeit- und Berufsgründen mußte ich das Projekt ersteinmal
pausieren

@Heiko: Ich hatte mal testweise eine leere Datenbank mit den Daten
befüllt. Da die Datenbank leer war,konnte ich auf APPEND gehen und
das Locate weglassen...
Das sollte ja schneller gehen,als wenn ich die Daten in eine "Variable" lade um im Speicher zu checken,ob die Daten schon
in der Datenbank stehen oder nicht.
Leider muß ich sagen,das der schreibvorgang in die (leere) Datenbank fast genauso lange gedauert hat,als wenn ich mit LOCATE arbeite. Es waren ein paar Sekunden weniger,was aber bei der hohen Minutenzahl leider kein effektiver Zeitgewinn ist

@Karsten:
Das mit den Primärschlüssel hört sich interessant an,allerdings gibt
es da ein Problem:
Die Datenbank gehört zu einer anderen Anwendung,welche nicht von
mir stammt. Ergo nützt es mir nichts,wenn die Datenbank bei
anderen Usern im ihren Ursprungszustand ist - da ich da dort ja
schlecht sagen kann: Ihr müßt mit Access die Datenbank umbauen
Ich weiß auch nicht,ob die "original" Anwendung,welche auf eben
diese Datenbank zugreift -> eventuell auf seine eigenen Primärschlüssel angewiesen ist. Es könnte also sein,das wenn die
Primärschlüssel modifiziert werden,die eigentlich Anwendung ,welche die Daten nutzt,nicht mehr richtig funktioniert.

Da ich selber mit Access keine Erfahrung habe,ist mir das
Risiko zu hoch da rumzuexperimentieren.
Es mag dann ja bei mir funktionieren,allerdings beim nächsten User nicht,weil der das "Datenbank Original" hat...

Ich müßte also einen weg finden,das ich den BATCH Modus besser
ausgenutzt bekomme,vielleicht mach ich da ja noch was falsch ???

Und noch eine Frage: Könnte mir der Befehl UPDATERECORD(.....)
vielleicht noch etwas bringen ???
Hab mich noch nicht näher damit beschäftigt,müßte mich dann auch belesen,wie der genau funktioniert...

Danke schonmal im vorraus

Cya de Helg

[Jochen Reichenberg]

Hallo nochmal,

besteht die Möglichkeit, die MDB und die TXT zum Download bereit zu stellen? Meine Erfahrungen mit Access halten sich in Grenzen und ich würde gern ein paar Dinga ausprobieren...

Grüße Joche

[Helge Vitense]

Moin Jochen...

Die MDB und die TXT Datei so ins Netz zu stellen,wird nicht
viel bringen. Da du die TXT ja parsen müßtest.

Ich könnte folgendes machen:

Ich parse die TXT fertig,schreibe den inhalt des Stringgrids in
eine Datei und schreibe eine Anwendung,welche den Stringgridinhalt (also die Daten aus der Datei) dann in die DB schreibt.

Somit wäre sichergestellt,das jeder normal das Schreiben in
die Datenbank testen kann,und in dem Sourcecode der Anwendung
schreiben kann,ohne umständlich die anderen Daten usw. parsen
zu müssen.

Sprich:
Es gibt die MDB,die vorgeparste Datendatei und das dafür erstellte
Projekt um die Textdaten in die MDB zu schreiben in einem Paket.
Somit sollte jeder der Interesse hat,loslegen können

Wenn das so genehm ist,dann mache ich das gerne,würde aber
bis zum Wochenende dauern,weil ich derzeit nicht die benötigte Freizeit dazu hab.

Cya de Helg

[Jochen Reichenberg]

Hi Helge,

prinzipiell ok, nur leider habe ich kein Delphi, sondern den BCB-Builder... Ich hab aber reichlich Erfahrung mit dem Parsen von Textdateien. An dem Parser solls also nicht scheitern.
Für mich wären also die Textdatei und die MDB besser.

Wann Du das machst, bleibt Dir überlassen.

Grüße Joche