Hallo,
ich habe wieder einmal ein kniffliges Problem. Ich habe eine CSV-Liste mit Produkten (ca. 1/2 Mio.!!), die ich importieren muss. Ich habe den Access CSV-Import verwendet um das File als Tabelle zu verknüpfen (das funktioniert übrigens hervorragend).
Die importierte Tabelle hat folgende Felder: produktbezeichnung, herstellerbezeichnung, herstellermodellnummer, ...
Meine internen Tabellen (in die die Produkte übertragen werden müssen) haben folgenden Aufbau:
produkt: id, bezeichnung, herstellerid, herstellermodellnummer, ... (unique key: herstellerid+herstellermodellnummer)
(hersteller: id, herstellerbezeichnung, ...) im Prinzip für diese Frage irrelevant
herstellerzuordnung: id, herstellerid, herstellerbezeichnung, ...
Die Tabelle "herstellerzuordnung" wird benötigt, da in den zu importierenden Listen die Herstellerbezeichnungen teilweise voneinander abweichen. Die tatsächlichen Hersteller werden in der Tabelle "hersteller" geführt und über die "herstellerzuordnung" wird jeder Möglichkeit für "herstellerbezeichnung" aus der Importtabelle ein eindeutiger Hersteller in Form der "herstellerid" zugeordnet.
Meine Problematik:
Ich muss die Importdaten in die "produkte"-Tabelle importieren. Der Import beinhaltet aber auch Produkte, welche bereits als Produkt angelegt sind.
Ich habe versucht, für alle Datensätze aus des Imports jeweils ein INSERT auf produkte durchzuführen mittels Docmd.RunSQL, jedoch benötigt das in meinem Fall über 6 Stunden, bis so alle Daten angelegt sind, welche noch nicht vorhanden waren (auf Grund des unique keys in produkte schlägt das Hinzufügen eines bereits bestehenden Produktes automatisch fehl, allerdings benötigt diese Methode abartig viel Zeit).
Mein aktueller Ansatz ist folgendermaßen:
1) Datensätze öffnen mit SQL-Abfrage:
SELECT * FROM (importtabelle LEFT JOIN herstellerzuordnung ON importtabelle.Herstellerbezeichnung = herstellerzuordnung.herstellerbezeichnung) LEFT JOIN produkt ON importtabelle.herstellermodellnummer = produkt.herstellermodellnummer;
2) Testen ob die produkt.herstellermodellnummer NULL ist <ODER> "die herstellermodellnummer existiert, jedoch nicht vom gleichen hersteller" (über eine DCount<1 Abfrage)
3) Wenn beides zutrifft -> INSERT
Damit konnte ich die Laufzeit auf unter 4 Stunden drücken (was immer noch sehr viel Zeit kostet, sind die DCount-Abfragen).
Hat evtl. jemand eine Idee, wie man das noch weiter optimieren kann? Vorschläge für einen alternativen (schnelleren) Algorithmus?
(möglicherweise kann man über ein "Count" in der SQL-Abfrage schon Datensätze ausschließen, jedoch habe ich keine passende funktionierende Syntax dafür unter Access-SQL gefunden)
Was nicht funktioniert hat, ist so etwas wie: "INSERT INTO produkte SELECT ... FROM importtabelle", da das nach einer gewissen Anzahl von Datensätzen einfach abbricht (habs getestet). Dies liegt IMO vermutlich an der großen Menge an Datensätzen in importtabelle.
P.S. ich arbeite mit Access als Front End und mySQL als Backend.
Hallo,
ich mache Imports von größeren Datenmengen immer in eine externe temporär erstellte Datenbank. Dabei die Daten so wie sie sind importieren, dann in der temp. Datenbank bearbeiten und die bereinigte Daten dann in das Arbeitsbackend übernehmen.
Ich habe das mal in meinem Blog etwas beschrieben:
http://www.accessblog.de/archives/196 (http://www.accessblog.de/archives/196)
und
http://www.accessblog.de/archives/592 (http://www.accessblog.de/archives/592)
Ob csv, Excel oder AccessDB ist egal, das Prinzip ist das selbe.
Gruß Andreas
Hallo,
danke schonmal, dass du dir die Mühe gemacht hast, mein Thema anzusehen.
Mir geht es jedoch mehr um die Laufzeit als um die technische Umsetzung.
Das Prinzip einer separaten Importdatenbank habe ich bereits selbst mehrfach verwirklicht.
Ich habe die Daten ja bereits in Tabellen einer externen Import-Datenbank, muss jetzt aber: alle fehlenden Produkte hinzufügen und alle bestehenden Produkte (vorwiegend den Preis) aktualisieren und dann alle Produkte die sich nicht mehr in den Importtabellen befinden deaktivieren (das mache ich dann anhand des timestamps, dachte ich).
Meine Herausforderung ist die Übertragung der Daten ins Backend. Ich teste gerade eine Routine auf ihre Geschwindigkeit, in der ich einfach die Importtabelle (sortiert), sowie die Backend-Tabelle (identisch sortiert) öffne und dann alle Datensätze in beiden Tabellen gleichzeitig durchlaufe und auf Unterschiede prüfe. Schätze mal das dürfte die schnellste Methode sein, da dann keine Querys zu jedem einzelnen Produkt nötig sind.
Ich habe darüber hinaus festgestellt, dass der RunSQL-Befehl sehr viel Zeit benötigt (ich würde daher gerne mehrere SQL-Befehle zusammenfassen und mit RunSQL "auf ein mal" an den Server senden, jedoch funktioniert das leider auch nicht so, wie ich mir das vorstelle -> siehe auch meinen Post zu RunSQL).
Hallo,
bei der Datenübertragung ins Backend musst du zuerst 2 Fälle unterscheiden. Neuanlage und Änderung.
Ich habe das so gemacht dass ich 2 Recordsets offen habe, eines mit dem Arbeitsbackend, eines mit der temporären Tabelle wo die Daten sitzen.
In einer Do Until rsTempTabelle.EOF Schleife wird mittels FindFirst im Arbeitsbackend der ID gesucht.
If rs.NoMatch wird der DS neu angelegt, ansonsten aktualisiert.
Andreas
Ich denke die Sortierung (wird in diesem Falle ja vom SQL-Server gemacht) und das parallele Durchlaufen beider Tabellen ist schneller bei derart vielen Datensätzen (mir fällt zumindest aktuell nichts schnelleres mehr ein). Wenn du in 1/2 Mio. Datensätze zu jedem Recordset einen seek machen möchtest... das ist ja dann fast so ähnlich wie ein DLookup für jeden Datensatz.
Im übrigen verzichte ich ja schon auf eine detaillierte grafische Ausgabe, da alleine diese schon zu langsam ist.
Etwas off-topic, aber möglicherweise weiß trotzdem jemand etwas darüber:
Hat einer Erfahrung mit Visual Basic Studio?
Ist das mit .Net (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx)) deutlich schneller als mit Access?
...sonst bleibt mir dann wohl nur noch die PHP-Lösung, falls alle Stricke reißen.
Zitatfestgestellt, dass der RunSQL-Befehl sehr viel Zeit benötigt
Es ist eher so, dass die angestoßenen Abfragen viel Zeit benötigen.
DCount, FindNext und andere Einzelprüfungen sind nun auch nicht gerade Performance-Renner.
Zitatmuss jetzt aber: alle fehlenden Produkte hinzufügen und alle bestehenden Produkte (vorwiegend den Preis) aktualisieren und dann alle Produkte die sich nicht mehr in den Importtabellen befinden deaktivieren
Das sollte man doch (sinnvollerweise in umgekehrter Reihenfolge) über gute SQL-Anweisungen lösen können:
- Aktualisierungsabfrage zum Deaktivieren veralteter Datensätze,
- Aktualisierungsabfrage zum Aktualisieren vorhandener Datensätze,
- Anfügeabfrage mit eingebauter Inkonsistenzprüfung zum Anfügen von nur neuen Datensätzen
Wenn man über Performance in Datenbanken spricht, sollte man
- SQL-Anweisungen verwenden,
- Indizes verwenden (durchaus auch in der Importtabelle),
- nur benötigte Daten bewegen,
- Netzwerktraffic niedrig halten.
MfGA
ebs
Danke an all die Ideenbringer, ich habe eine Lösung gefunden.
Für alle die meine Lösung interessiert, ich habe sehr lange rumgetestet und profiliert:
Man benutzt einfach eine bei Access hinterlegte Query als Source für die SQL-Abfrage (das ist dann so ähnlich wie eine geschachtelte SELECT Anfrage aus dem Lehrbuch). Funktioniert einwandfrei und relativ schnell.