SQL Statement auf MS Access DB

Anno74 · 10. November 2019 um 09:03

Hallo,

ich greife unter VB via ADO auf eine Access Datenbank zu. Welches Format(JET Engine) vorliegt kann der Anwender bestimmen, beim erstellen der DB, welche auch unter VB erstellt wird.

Der Aufbau ist rel. simple gehalten.

'Tabelle FileData
'Felder
ID - Typ adInteger (Autoinkrement)
Rechner - Typ adVarWChar
File - Typ adVarWChar
Path - Typ adVarWChar
Size - Typ adInteger
Create - Typ adDate
Change - Typ adDate

Auf dem Rechner, läuft das Program nun und aller X Stunden durchsucht er definierte Ordner nach ihren Dateien. Ich bekomme sie jeweils in den Variablen

sRechner, sFile, sPath, sSize, sCreate und sChange zurueckgeliefert.

Nun sollen diese Dateien in die DB eingetragen werden! Aber nur wenn diese Daten noch nicht in der DB vorliegen oder wenn sich irgendein Wert geaendert hat. Hat sich ein Wert geaendert. Zum Bsp. sChange oder sSize so soll der alte Datensatz überschrieben werden!

Mein bisherige Idee ist folgende, aber sehr zeitaufwendig und sicher net elegant

Select \* From FileData Where Rechner Like '" & sRechner & "' and \_
File Like '" & sFile & ' and Path Like '" & sPath & "';"

Wenn ich mind. 1 Recordset bekomme, würde ich nun alle Daten vergleichen und wenn sich was geaendert hat, dann den Datensatz löschen und ihn wieder eintragen. Bekomme ich kein Recordset zurueck so gibt es den noch nicht und ich würde ihn eintragen. Aber bei ca. 50000 Files, die er finden wird, wird das ne ganze Menge Zeit in Anspruch nehmen! Von daher frage ich mal, ob es nicht eine elegantere Lösung gibt?

MfG Alex

Georg_V · 10. November 2019 um 09:03

gelöscht :

Select * From FileData Where Rechner Like ‚" & sRechner & "‘
and _
File Like '" & sFile & ’ and Path Like ‚" & sPath & "‘;"

gelöscht :

Hallo Alex,

eigentlich gibt es ein Access-Forum direkt darüber …

Aus Performancegründen würde ich immer erst ein

> Select count(\*) From FileData Where Rechner Like '" & sRechner & "'  
> and \_  
> File Like '" & sFile & ' and Path Like '" & sPath & "';"

machen und dann ein direktes Update der Daten.

Ansonsten könnte man (je nach Änderungsrate der Informationen und Lage von Erfassungsprogramm und Datenbank) erst alle Datensätze in eine zweite Tabelle
inserten und dann lokal in der Datenbank ein Masseninsert bzw. Update starten.

MfG Georg V.

drambeldier · 10. November 2019 um 09:04

Moin, Alex,

geh gezielt vor: Suche mit den Variablen den Satz, bei dem die Werte Rechner, File und Path den neuen Werten entsprechen. Ist der Satz da, wird er geändert, ansonsten eingefügt.

Für den Insert lässt sich keine Bedingung angeben; das Ändern ginge noch per Sql:

Update ... where id not in (select id from table where [Felder vergleichen])

Eine zweite Möglichkeit wäre, den gefundenen Satz immer zu löschen und dann neu einzufügen, damit wäre die Änderung auch gleich erledigt.

Was stellst Du denn mit dem Like an? Da komme ich nicht ganz mit.

Gruß Ralf

Anno74 · 10. November 2019 um 09:05

Hallo Ralf,

geh gezielt vor: Suche mit den Variablen den Satz, bei dem die
Werte Rechner, File und Path den neuen Werten entsprechen. Ist
der Satz da, wird er geändert, ansonsten eingefügt.

Naja den Ansatz hatte ich auch schon

Aber da es schnell mal eben 50.000 Files sein koennen, koennte das einige Zeit in Snspruch nehmen. Also erst den Satz in der DB suchen, dann vergleichen und dann updaten oder wenn er nicht vorhanden ist, eben gleich eintragen:s
Ich habe eigentlich gehofft das es mit einem schlichten einzeler geht, was imenz schneller waere :s

Für den Insert lässt sich keine Bedingung angeben; das Ändern
ginge noch per Sql:

Update … where id not in (select id from table where [Felder
vergleichen])

Ja aber suchen ob er vorhanden ist, muss ich ja trotzdem
Von daher kann ich ihn dann auch gleich löschen und ihn neu eintragen.

Eine zweite Möglichkeit wäre, den gefundenen Satz immer zu
löschen und dann neu einzufügen, damit wäre die Änderung auch
gleich erledigt.

In diese Richtung würd es wohl hinauslaufen!

Was stellst Du denn mit dem Like an? Da komme ich nicht ganz
mit.

Damit kannst du Strings vergleichen. Geht glaube auch mit dem =, aber LIKE finde ich schöner *ggg*

Gruß Ralf

MfG Alex

Anno74 · 10. November 2019 um 09:05

Hallo Georg,

eigentlich gibt es ein Access-Forum direkt darüber …

Das habe ich gesehen. Da ich aber als Arbeitsumgebung VB habe und kein Access ( ist net mal auf dem Rechner hier installiert) und es sich lediglich um ein SQL Statement handelt, so habe ich gewusst hier gepostet

Aus Performancegründen würde ich immer erst ein

Select count(*) From FileData Where Rechner Like ‚" & sRechner & "‘
and _
File Like '" & sFile & ’ and Path Like ‚" & sPath & "‘;"

Hm, also es kann immer nur 1 Datensatz pro File vorliegen. Niemals mehr!
Gehen wir mal von aus.

In der DB steht das File C:\Autoexec.bat, mit der Info wann es erstellt wurde und wann die letzte Aenderung war und ich nun das File bekomme um in die DB zu schreiben. So soll nur wenn sich das File geaendert hat (Eigentlich kann es ja nur die letzte Aenderung sein) dann der Datensatz geupdatet werden. Ansonsten nicht!
Somit kann jedes File nur einmal in der DB stehen!
Kommt nun Bsp. ein File c:\xyz.dat hinzu, welches in DB noch nicht existiert, so soll es hinzugefügt werden

machen und dann ein direktes Update der Daten.

Ansonsten könnte man (je nach Änderungsrate der Informationen
und Lage von Erfassungsprogramm und Datenbank) erst alle
Datensätze in eine zweite Tabelle
inserten und dann lokal in der Datenbank ein Masseninsert bzw.
Update starten.

Ja aber dazu muesste ich wissen, welche von diesen Datensaetzen dann schon in der DB in einer anderen Version vorliegen

MfG Alex

WediBerni_4489e0 · 10. November 2019 um 09:06

Moin,

wenn ich das so lese … warum löscht du die DB nicht einfach und baust komplett neu auf?

Gruß

Bernd

TheBeast · 10. November 2019 um 09:06

Hi Alex,

also wie das mit der Performance in einer Access-Umgebung (Datenbank wäre wohl stark übertrieben) aussieht kann ich Dir nicht sagen, aber wenn das ein Thema ist, dann würde ich nach Möglichkeit auf eine „echte“ DB umsteigen.

Unter Oracle würde ich es im Prinzip so machen, dass die DB selbst die Arbeit erledigt:

INSERT des neuen Satzes (fliegt mit einer Primary Key-Verletzung, wenn schon vorhanden)
Wenn 1) wegen PK schief gegangen ist, dann UPDATE

Die Reihenfolge kann man natürlich umdrehen:

UPDATE
INSERT, wenn das vorherige Statement 0 Sätze verändert hat

Nach Möglichkeit wählt man die Reihenfolge so, dass immer nur ein Statement ausgeführt wird (also erste Möglichkeit, wenn eher neue Files dazukommen, die zweite, wenn eher Files geändert werden).

Eine kleine Verständnisfrage noch: Dir ist schon klar, dass Du so ein Verzeichnis aufbaust, das alle Dateien enthält, die irgendwann einmal auf dem Rechner waren, zuminest sehe ich nichts, wo gelöscht wird. Wenn das nicht gewünscht wird, dann ist das schnellste wohl, das ganze Verzeichnis parallel neu aufzubauen und dann - falls warum auch immer nötig - mit den ursprünglichen Daten abzugleichen.

Gruß
Martin

WediBerni_4489e0 · 10. November 2019 um 09:06

Moin,

wenn schon dann kannst du das in einem SQL abhandeln - da ich nu seit 2 Jahren beim SQL-Server gelandet bin weiss ich die Syntax nicht genau, aber das kann man ja nachgoogeln ;o)

Jedenfalls kann man bei Oracle bei fehlgeschlagenem insert direkt ein update durchführen in einem SQL.

Gruß
Bernd
P.S.: Du dürftest eh bei Access an die Grenze kommen mit der Anzahl der Datensätze wo Access nu gar nicht mehr geht…

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Anonym_5da391d1a0c0 · 10. November 2019 um 09:07

Offtopic: MERGE
Tach,

Jedenfalls kann man bei Oracle bei fehlgeschlagenem insert
direkt ein update durchführen in einem SQL.

och Leute, wie seid ihr denn drauf. Schaut euch mal die Syntax des MERGE-Befehls an (gibts seit Oracle 9 oder 10), dann klappts auch wunderbar und perfomant mit einem einzigen Statement.

also wie das mit der Performance in einer Access-Umgebung
(Datenbank wäre wohl stark übertrieben) aussieht kann ich Dir
nicht sagen, aber wenn das ein Thema ist, dann würde ich nach
Möglichkeit auf eine „echte“ DB umsteigen.

Recht so, zeig ihm, was ne richtige Männerdatenbank ist! SCNR.

gruss
bernhard

TheBeast · 10. November 2019 um 09:07

Hi,

och Leute, wie seid ihr denn drauf. Schaut euch mal die Syntax
des MERGE-Befehls an (gibts seit Oracle 9 oder 10), dann
klappts auch wunderbar und perfomant mit einem einzigen
Statement.

Der dürfte ihm bei einer Access-Anwendung nicht viel helfen. Ausserdem, da war doch noch was… ach ja:
Use the USING clause to specify the source of the data to be updated or inserted. The source can be a table, view, or the result of a subquery. Das ginge also nur in etwa so:

MERGE INTO myTable USING (SELECT var1, var2, ..., varn FROM DUAL) ...

Wem’s gefällt (und ob’s schneller ist würde ich auch erst nach einem Test glauben, faktisch kann die DB ja auch nix anderes machen als einen PK Scan und dann halt INSERT/UPDATE). So richtig nett wird mergen nur, wenn man über mehrere Tabellen geht.

also wie das mit der Performance in einer Access-Umgebung
(Datenbank wäre wohl stark übertrieben) aussieht kann ich Dir
nicht sagen, aber wenn das ein Thema ist, dann würde ich nach
Möglichkeit auf eine „echte“ DB umsteigen.

Recht so, zeig ihm, was ne richtige
Männerdatenbank ist! SCNR.

Also ganz ohne Ironie würde ich dem Herrn OP mal ans Herz legen
sich mySql anzusehen, muss ja nicht immer gleich Oracle oder
DB2 sein - und er hat die Performance ins Spiel gebracht, nicht ich.

Gruß
Martin

Anonym_5da391d1a0c0 · 10. November 2019 um 09:09

Hi TheBeast,

Der dürfte ihm bei einer Access-Anwendung nicht viel helfen.

Stimmt, aber in den Antworten ging es ja schon um Oracle. Ich wollte halt nur mal Reklame für den relativ neuen MERGE-Befehl machen, der nach meiner Erfahrung von der Performance her ganz gut ist.

Wem’s gefällt (und ob’s schneller ist würde ich auch erst nach
einem Test glauben, faktisch kann die DB ja auch nix anderes
machen als einen PK Scan und dann halt INSERT/UPDATE). So
richtig nett wird mergen nur, wenn man über mehrere Tabellen
geht.

PK-Scan muss nicht sein, du kannst ja über WHERE-Klauseln dein Update-Statement auch wie gewohnt einschränken.

Also ganz ohne Ironie würde ich dem Herrn OP mal ans Herz
legen

Wer ist denn Herr OP?

sich mySql anzusehen, muss ja nicht immer gleich Oracle oder
DB2 sein - und er hat die Performance ins
Spiel gebracht, nicht ich.

Stimmt. mySQL läuft auch recht rund.

Gruss und schönen Tag der Arbeit
bernhard