UPDATE einer ACCESS-Tabelle zu langsam

Question

Hallo,

ich muss eine Tabelle mit ca. 560.000 Datensätzen durchlesen und durch mehrere Kriterien entscheiden, ob ein Datensatz ein Kennzeichen bekommt oder eben nicht. Der Code sieht so aus:

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim dtROW As DataRow

        Label1.Text = Now().ToString
        Label1.Refresh()

        strCONLOC = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\TEST\TEST.MDB;Jet OLEDB:Database Password=TEST;Jet OLEDB:System Database=C:\TEST\TESTSYS.MDW;User ID=TEST;Password=TEST"

        conLOC = New OleDbConnection(strCONLOC)
        conLOC.Open()

        With cmd
            .Connection = conLOC
            .CommandText = "tblTEST"
            .CommandType = CommandType.TableDirect
        End With

        With dadp
            .SelectCommand = cmd
        End With
        dadp.MissingSchemaAction = MissingSchemaAction.AddWithKey
        dadp.Fill(dtbl)

        For Each dtROW In dtbl.Rows
            dtROW!EXPORTIERT = True
        Next

        Label2.Text = Now().ToString
        Label2.Refresh()

        cmb = New OleDbCommandBuilder(dadp)
        dadp.Update(dtbl)

        cmd.Dispose()
        dadp.Dispose()
        dtbl.Clear()
        dtbl.Dispose()

        conLOC.Close()
        conLOC.Dispose()

        Label3.Text = Now().ToString
        Label3.Refresh()

    End Sub

Bei diesem Beispiel dauert der UPDATE ca. 30 Minuten (neuer PC, Windows 8.1, 4GB RAM, VB 2010, Service Pack 1). Was mache ich falsch, bzw. wie kann man den UPDATE beschleunigen?

Danke für jeden Tipp!Gruß Schorsch

Answer 1

Hi,

Access ist für solche Datenmengen nicht unbedingt die beste Wahl. Ggfs. solltest Du über einen Wechsel zu einem richtigen Datenbanksystem nachdenken.

Generell ist es sinnvoller und erheblich schneller, die Aktualisierung per UPDATE Tabelle SET ... WHERE ... über SQL zu machen, für deinen Code also in etwa:

UPDATE tblTEST
SET    EXPORTIERT = 1
WHERE  ...

Dein Code durchläuft 560.000 mal eine Schleife und aktualisiert ein Feld (ohne weitere Prüfung, das wäre dann gleichzusetzen mit einer nicht vorhandenen WHERE Klausel). Danach durchläuft .NET selbst auch 560.000 mal eine Schleife, erstellt SQL Statements und führt die gegen die Datenbank aus (bei Access wohl alle einzeln). Dass das nicht performant sein kann, dürfte einleuchten.

Es wäre daher hilfreich, wenn Du uns die genauen Anforderungen/Kriterien nennen könntest, anhand derer Du entscheiden musst, ob und was aktualisiert wird und was nicht.

Poste bitte auch die Tabellenstruktur als CREATE TABLE Statement sowie einige Beispieldaten und dazu das gewünschte Ergebnis (also welche Datensätze wie aktualisiert werden sollen)

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 2

Hallo Stefan,

danke für die Tipps und Denkanstöße. Leider kann ich nicht so ohne Weiteres das DB-System ändern, weil die Software natürlich schon beim Kunden läuft.

Die Kriterien für das Ändern des Datensatzes stehen in mehreren Tabellen in anderen Datenbanken; ich kann also nicht so einfach einen UPDATE-Befehl ausführen.

Ich habe mir aber nun - aufgrund Deiner Tipps - wie folgt geholfen: Der Inhalt des Primärschlüssels der zu aktualisierenden Tabelle wird in eine temporäre Tabelle gechrieben wenn die Kriterien zutreffen; danach kann ich dann den SQL-Befehl ausführen, indem ich diese beiden Tabellen verknüpfe:

UPDATE tblTEST 
INNER JOIN tblTEST_AKT ON tblTEST.LFDNR = tblTEST_AKT.LFDAKT 
SET tblTEST.EXPORTIERT = True

Die Folge ist eine Verkürzung der Laufzeit von 30 auf 9 Minuten. Das ist akzeptabel.

Das Problem der "Trägheit" tritt ab ca. 16.000 zu aktualisierenden Datensätzen auf. Die Tabelle hat 30 Spalten/Felder mit jeweils ca. 10 Stellen Text. Alle Felder sind gefüllt.

Mit Verlaub halte ich dieses Verhalten von ADO.NET für einen Fehler, denn selbst eine solch relativ geringe Menge an Datensätzen müsste schneller aktualisiert werden können. Unter dieser Anzahl von ca. 16.000 ist das Verhalten einwandfrei und sehr schnell!

Gruß Schorsch

Answer 3

Hi,

wenn es bis 16.000 Datensätze sehr schnell (wie schnell ist "sehr schnell"?) geht und die Performance bspw. bei 17.000 bereits einbricht, spricht einiges dafür, dass es da eine "magische" Grenze gibt. Das kann das verfügbare RAM sein, die Art und Weise, wie bestimmte Sachen von der Anwendung oder auch der Jet Engine zwischengelagert werden, ... Hier müsste man prüfen, was genau dann länger braucht. Die erste Schleife, das Update der Datenbank, ...

Wie gesagt, Access gehört nicht zu den DBMS, die wirklich eine super Performance bei großen Datenmengen haben. Es kann gut sein, dass die Jet Engine selbst das Problem darstellt. Ohne ausführliche Tests wird man das aber wohl eher nicht herausfinden.

Was genau dauert bei der neuen Variante eigentlich so lange? Die Ermittlung der ID Werte für die Aktualisierung oder das UPDATE ... selbst (das Statement, welches Du oben gepostet hast)?

Je nach Anzahl der ermittelten ID Werte wäre es ggfs. sinnvoller, keine temporären Werte in die Datenbank zu schreiben, sondern die Werte in eine IN ( ... ) Klausel zu packen und dann mit diesem Kriterium das UPDATE durchzuführen. Aber auch hier gibt es Grenzen.

Zur Not musst Du halt blockweise arbeiten. Also nicht alle Datensätze auf einmal, sondern bspw. 10.000 Datensätze pro Durchlauf. Da Du ja sagst, bis 16.000 läuft es sehr schnell, wäre das evtl. eine Möglichkeit, die "magischen" Grenzen zu umgehen.

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 4

Hallo Stefan,

danke für Deine Mühe und Überlegungen!

Sehr schnell heißt für mich 1-2 Sekunden. Das blockweise Speichern, also etwa alle 10.000 Datensätze, habe ich dann natürlich auch ausprobiert. Die Zeit für das Speichern steigt bei mir schlagartig an, wenn in diesem Fall insgesamt über 30.000 Datensätze gespeichert wurden. Also, Block 1 mit 10.000 Datensätzen dauert 1-2 Sekunden, Block 2 auch, Block 3 auch, Block 4 dauert dann plötzlich 5 Sekunden.

Noch eine Info zu meinem PC: Der ist nagelneu (also kein Müll drauf), hat Windows 8.1 64 Bit, 4 GB RAM.

Folgender Befehl ist der problematische:

dadp.Update(dtbl)

Ich programmiere schon lange mit VB6 und Access-Datenbanken und natürlich gibt es da einige Einschränkungen bezüglich der Performance, die aber in Hinsicht auf die bequeme Handhabung bei kleineren Projekten weniger ins Gewicht fallen. In unserem Fall sehe ich aber das Problem in der Schnittstelle, also ADO.NET.

Wie dem auch sein, Du hast mich aber erst einmal auf den richtigen Weg gebracht und das soll erstmal genügen. Die Variante mit der IN..-Klausel probiere ich mal.

Nochmals besten Dank, Gruß Schorsch

Answer 5

Die Folge ist eine Verkürzung der Laufzeit von 30 auf 9 Minuten. Das ist akzeptabel.

Das Problem der "Trägheit" tritt ab ca. 16.000 zu aktualisierenden Datensätzen auf. Die Tabelle hat 30 Spalten/Felder mit jeweils ca. 10 Stellen Text. Alle Felder sind gefüllt.

Mit Verlaub halte ich dieses Verhalten von ADO.NET für einen Fehler, denn selbst eine solch relativ geringe Menge an Datensätzen müsste schneller aktualisiert werden können. Unter dieser Anzahl von ca. 16.000 ist das Verhalten einwandfrei und sehr schnell!

Das ist weniger ADO.NET als vielmehr Access, ist halt kein DBMS. Ich tippe da (im Ernst) auf eine etwas lahme Festplatte. 4 GB RAM sind ja nicht viel, bloß mehr nützt einem unter x86 auch nix. Also swapt der PC viel herum. Dank ReadyBoost läßt sich da einiges stark beschleunigen. Ein 8GB-Stick am USB 3-Port als ReadyBoost-Device bringt vielleicht mehr ...

Gruß

Johannes

Answer 6

Hallo Schorsch,

es ist nicht die Taktfrequenz eines Prozessor oder das Betriebssystem was den Rechner "schnell" macht, sondern seine Ressourcen. Das falsche BS auf einem System hemmt es genauso wie zu wenig Arbeitsspeicher. Windows8.1 mit 4 GB, hier runzelt sich meine Stirn. Und dann vielleicht noch eine "relativ" langsame Platte, "Gute Nacht".

Ganz davon abgesehen, dass jemand der 30.000 Datensätze gegen eine Access-Datenbank richtet Suizid gefährdet ist. An dieser Stelle sollte es doch eher ein SQL-Server sein.

Du brauchst also nicht nach irgendwelchen "falschen" Commands oder Statements suchen. 1. Falsche Maschine - 2 Falsche Wahl der Software.

Ganz davon abgesehen, wer will alle 5 Minuten zig-Tausend Datensätze updaten?

Gruß Scotty

Answer 7

Hallo Schorsch,

Wenn Dir die Antworten geholfen haben, dann bitte markiere diese als Antwort.

Danke und Gruss,

Ionut

---

Ionut Duma, MICROSOFT   Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-Prinzip„Entwickler helfen Entwickler“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.