Hallo zusammen,
folgende Situation:
Auf dem Rechner habe ich eine Access-Datei (DB1) geöffnet in der per Anfügeabfrage in Verbindung mit einer Funktion Daten generiert werden.
Dieser Vorgang dauert tlw. bis zu 36 Std.
Mein Problem ist, dass ich währendessen keine weitere Access-Datei (DB2, DB3...) öffnen kann,
praktisch ist Access in der Zeit nicht nutzbar.
Zu öffnen versuche ich über den Explorer und dann die db2.accdb zu klicken - nichts passiert.
Gibt es eine Möglichkeit irgendwie weitere Dateien zu öffnen?
Hat jemand eine Idee oder geht das techn. garnicht.
Gruß Torsten.
Hallo,
36 Std??
Was für einen Rechner hast Du denn? ;)
Ernsthaft: Ich würde mal an der Performance der Datengenerierung arbeiten und die Indizierungsverhältnisse in den beteiligten Tabellen prüfen.
Zeige mal die Prozedur und den SQL-String der Anfügeabfrage.
Eine andere DB kann vermutlich mit einer weiteren Access-Instanz geöffnet werden. D. h., die andere DB mit Doppelklick darauf öffnen und nicht die "blockierte" Instanz zum Öffnen benutzen.
Moin Franz,
neue Instanz hat funktioniert.
Das mit dem Zeitfaktor sehe ich auch als ein Problem, der Rechner ist ok.
Ich gehe davon aus, dass es an der Menge der Datensätze liegt - um die 800Tsd.
Gruß Torsten
Hallo,
Zitatum die 800Tsd.
Bei korrektem, normalisiertem Aufbau der Datenbank mit sinnvollen Indizierungen sind 800Tsd kein Problem.
Auch dann sollte die Verarbeitung im Sekundenbereich liegen.
Was wird denn in der Abfrage mit Funktionen gemacht ?
ZitatIch gehe davon aus, dass es an der Menge der Datensätze liegt
Ja. Wenn es nur 30 Datensätze wären, würde es sicher anders aussehen, da überspielt die Kraft der Maschine jede eigenverursachte Ungeschicklichkeit.
Zur Performance bei richtigen Datenmengen muss dann etwas Entwicklerintelligenz hinzugefügt werden.
36 Stunden sind unakzeptabel. Womöglich läuft das nicht einmal im Rahmen einer Transaktion ab, was dann bei einem unerwarteten Abbruch die Integrität der Daten in Frage stellt.
Hallo,
Vermutlich werden die DS einzeln angefügt, und da werden nach jedem DS
die Indices neu einsortiert. Das fängt dann nach ein paar 10k DS an immer
langsamer zu werden. Und wenn's dann noch mehrere Indices gibt, - gute Nacht. ;)
gruss ekkehard
@Beaker
Ja gut möglich, was ich leider nicht beurteilen kann...
Ich habe hier nochmal meine Demo_db angehängt, da ist Struktur auf wenige Testdaten runtergebrochen.
In der Tabelle "tbl" sind in echt die ca. 840Tsd DS enthalten.
Ja, die gute alte SQLListe: Sie tut, was sie soll, aber auf Performance ist sie nicht getrimmt. Sie enthält systematische Fehler (Neuinstanzierung CurrentDb pro Aufruf, Textzusammensetzung per VBA), die sich in benötigte Zeit umsetzen und bei genügender Wiederholung dann zeitlich anstrengend sind.
Alternative:
Public Function ADOConcatColumn(ByVal strSQL As String, _
Optional ByVal strSeparator As String = ", ") _
As Variant
Const adOpenForwardOnly = 0
Const adCmdText = 1
Const adLockReadOnly = 1
Const adUseClient = 3
Static rs As Object 'ADODB.Recordset nur 1x erstellen
On Error GoTo x
If rs Is Nothing Then
Set rs = CreateObject("ADODB.Recordset")
rs.ActiveConnection = CurrentProject.Connection
rs.CursorLocation = adUseClient
End If
rs.Open strSQL, , adOpenForwardOnly, adLockReadOnly, adCmdText
If Not (rs.EOF And rs.BOF) Then
ADOConcatColumn = rs.GetString(, , , strSeparator)
ADOConcatColumn = Left$(ADOConcatColumn, Len(ADOConcatColumn) - Len(strSeparator))
End If
rs.Close
Exit Function
x:
ADOConcatColumn = CVErr(6001)
End Function
Die Abfrage dann:
SELECT DISTINCT
T.gruppe,
ADOConcatColumn("SELECT beschr_wert FROM tbl WHERE gruppe = '" & T.gruppe & "' ORDER BY sortnr", ";") AS liste
FROM
tbl AS T
Zusätzlich sollte auf sortnr ein Index gesetzt werden.
// Nachtrag:
Ich bin mir nicht sicher, ob in der gezeigten Abfrage zuerst gruppiert wird und erst danach mit der verringerten Zeilenanzahl die Textzusammensetzung erfolgt, oder ob erst Textzusammensetzung und dann Gruppierung erfolgt. Der erste Fall wäre zu bevorzugen, und sicherheitshalber könnte man die Abfrage so formulieren:
SELECT
T.gruppe,
ADOConcatColumn("SELECT beschr_wert FROM tbl WHERE gruppe = '" & T.gruppe & "' ORDER BY sortnr", ";") AS liste
FROM
(
SELECT DISTINCT
gruppe
FROM
tbl
) AS T
Hallo Eberhard,
ich habe testweise 160Tsd DS in die Tabelle "tbl" importiert und Feld "sortnr" indiziert.
Habe soeben nach 2 Std. abgebrochen, da die Anzeige unten rechts unter "Abfrage ausführen" bei ca. 1/5 Stand.
Gefühlt ist es schneller als mit dem alten Code, aber hast Du da noch einen Ansatz wie es schneller geht?
Gruß Torsten
Das Arbeiten an einer Lösung ist doch erst der dritte Schritt.
Vorher sollte man einen Überblick haben,
- warum das stattfindet,
- wo die Daten herkommen,
- wo (welches Backend) das stattfindet,
- wie oft das stattfindet (über alle 800k Datensätze),
- wieviel Beschreibungen maximal zu verketten wären
?
Ein Weg leitet sich von Ausgangssituation und eigentlichem Ziel ab, also nicht zwingend von einem Weg, den man gerade kennt.
Gut, wie kann ich Dir den Überblick geben?
Manchen muss man tatsächlich manchmal erst die deutsche Sprache verständlich machen
Zitat- warum das stattfindet, ??
- wo die Daten herkommen, ??
- wo (welches Backend) das stattfindet, ??
- wie oft das stattfindet (über alle 800k Datensätze), ??
- wieviel Beschreibungen maximal zu verketten wären ??
Hallo Eberhard,
kurze Zwischeninfo:
Dein "Nachtragscode"
SELECT
T.gruppe,
ADOConcatColumn("SELECT beschr_wert FROM tbl WHERE gruppe = '" & T.gruppe & "' ORDER BY sortnr", ";") AS liste
FROM
(
SELECT DISTINCT
gruppe
FROM
tbl
) AS T
ist es. Da werden die Testdaten (160Tsd) innerhalb 1 Std. generiert.
Jetzt muß ich sehen das ich weitere Daten der Tabelle in die Zieltabelle mit übergeben bekomme.
Da werde ich sicherlich noch Fragen haben.
Bis hierhin schonmalvielen Dank für Deine Hilfe.
Gruß Torsten.
Ich habe die Demo-DB aktualisiert.
Darin ist zu sehen, dass wenn ich den "Nachtrag-Code" in der Abfrage verwende,
es keine Möglichkeit gibt weiteren Felder der Tabelle in die Abfrage zu ziehen um die Daten an die Zieltabelle zu übergeben.
Ich habe unter Funktion jeweils "Ausdruck" und "Gruppierung" ausprobiert.
Gibt es da eine Möglichkeit?
Eine Abfrage kann nur die Felder verwenden, die im FROM-Teil (=> Unterabfrage) bereitgestellt werden:
ZitatINSERT INTO tblErgebnis ( gruppe, [-liste-so soll es werden], textfeld1 )
SELECT
T.gruppe,
ADOConcatColumn("SELECT beschr_wert FROM tbl WHERE gruppe = '" & T.gruppe & "' ORDER BY sortnr",";") AS liste, [textfeld1] AS test,
T.textfeld1
FROM (SELECT DISTINCT gruppe, textfeld1 FROM tbl) AS T;
Mit einen Mewhrfachindex über die Felder Gruppe, Textfeld1 lässt sich das Laufzeitverhalten vermutlich noch weiter verbessern.
Eine Variante, die man vielleicht auch prüfen könnte, wäre der Weg der Kreuztabellenabfrage:
TRANSFORM
MIN(beschr_wert) AS Minvonbeschr_wert
SELECT
gruppe,
textfeld1
FROM
tbl
GROUP BY
gruppe,
textfeld1
PIVOT
sortnr
Falls die Textzusammensetzung notwendig ist, könnte man die so anschließen:
SELECT
K.gruppe,
K.[001] & (';' + K.[002]) & (';' + K.[003]) & (';' + K.[004]) & (';' + K.[005]) & (';' + K.[006]) AS liste,
K.textfeld1
FROM
tbl_Kreuztabelle AS K
Hallo.
Ich habe mich der Sache auch mal angenommen und ein Beispiel gebastelt.
Hier werden zwischen 700.000 und 800.000 Datensätze in 7 - 8 Minuten verarztet. (Also weit unter 36 Stunden)
Im Modul mdTest die Prozedur <machtabelle> ausführen. Die erstellt
> 700.000 Datensätze
Anschließend die Prozedur <machErgebniss> ausführen.
Frithjof
Moin zusammen
@Eberhard
Ich habe zunächst den Code aus Antwort #14 in meine orig. DB eingebaut und funktioniert. Vielen Dank.
Ich werde jetzt versuchen den Code aus #16 auszuprobieren.
Melde mich dann wieder.
@Lachtaube
ZitatMit einen Mewhrfachindex über die Felder Gruppe, Textfeld1 lässt sich das Laufzeitverhalten vermutlich noch weiter verbessern.
Gut, nur wie stellt man denn fest wann eine Indizierung nötig/von Vorteil (wahrscheinlich wenn zu langsam) ist und welches Feld sollte den Index bekommen?
Z.B. der Hinweis von Eberhard in Antwort #7
ZitatZusätzlich sollte auf sortnr ein Index gesetzt werden.
da wäre ich nie drauf gekommen das umzusetzen, weil einfach das Verständnis fehlt - aber ich versuche zu lernen.
@Frithjof
Da werde ich mich mit beschäftigen, melde mich dazu dann wieder
Vorab vielen Dank
Gruß Torsten.
@Torsten,
Grundlektüre von Microsoft: Erstellen und Verwenden eines Indexes zur Verbesserung der Systemleistung (https://support.office.com/de-de/article/erstellen-und-verwenden-eines-indexes-zur-verbesserung-der-systemleistung-0a8e2aa6-735c-4c3a-9dda-38c6c4f1a0ce)
Erweiterte Lektüre: Performance in Abfragen (http://www.donkarl.com/Downloads/AEK/AEK8_Abfragen_Performance.zip)
@Lachtaube
Ok, danke für die Quellen.
Gruß Torsten.
@Frithjof
Ich habe testweise einen Teildurchlauf gestartet, ca. Werte meiner Daten im Vergleich zu Deiner Demo_Ver_1 ist wie folgt:
840Tsd Ds in tbl
230Tsd Ds in tblErgebnis (erhalten mit Eberhards Code aus #14 ca. 7 Std.)
Nach Start "machErgebnis" habe ich ca. 1500 Ds nach 8 Min. erhalten.
Hochgerechnet auf das Ergebnis von 230Tsd DS würde es ca. 20 Std. dauern.
Zur Info noch:
In den 840Tsd Ds sind pro gleicher "Gruppe" bis zu 35 Ds.
Feld "Liste" in tblErgebnis ist dementsprechend ein Memo-Feld, falls das wichtig ist.
Gruß Torsten.
@Eberhard
im Vorschlag aus #16 funktioniert der Code für die Textzusammensetzung nicht.
Kannst Du bitte schauen was ich möglicherweise falsch gemacht habe?
DB ist angehängt.
Gruß Torsten
Hallo Torsten.
Zitat
In den 840Tsd Ds sind pro gleicher "Gruppe" bis zu 35 Ds.
Feld "Liste" in tblErgebnis ist dementsprechend ein Memo-Feld, falls das wichtig ist.
Die Frage ist was sich zwischen deiner Originaldatei und der Test-DB sonst noch alles unterscheidet? Ich habe in der Test-DB Tabelle <tbl> lediglich einen Index auf die Gruppe gesetzt.
Ein Versuch mit
- tblErgebnis = Memo-Feld
- bis zu 35 Datensätzen in einer Gruppe
- mit > 1.000.000 Datensätzen in Tabelle <tbl> .
Die Ergebnistabelle enthält 93.750 Datensätze.
Bearbeitungszeit: 219 Sekunden
Ein Versuch mit
- mit > 230.000 Datensätzen in <tbl> .
Die Ergebnistabelle enthält 31.750 Datensätze.
Bearbeitungszeit: 78 Sekunden
(Access 2013, ssd-platte, 8 GB Ram, core i5 )
Du kannst das einfach mit den Testdaten ausprobieren.
1. Im Modul mdTest <machtabelle> ausführen.
Es werden zwischen 200.000 und 300.000 Datensätze erstellt.
(An den Forschleifen kann man noch rumspielen und die Anzahl erhöhen oder vermindern.)
2. Anschließend <machErgebnis> ausführen.
Im Direktfenster wird ein Wert hochgezählt und am Ende die Bearbeitungszeit in Sekunden ausgegeben.
Frithjof
In meinem Vorschlag bezieht sich die Bezeichnung "tbl_Kreuztabelle" auf die vorher gezeigte Kreuztabellenabfrage (Bezeichnung ist etwas unglücklich gewählt). Leider ließ sich diese Abfrage nicht direkt als Unterabfrage einbinden.
@Torsten.
ich habe es jetzt noch mal ohne Index getestet. In der Tat läuft es ist es dann extrem lahm. (=aus Minuten werden Stunden)
Also setze in jedem Fall auf das Feld Gruppe einen Index.
Ansonsten ist meine Routine etwas schneller als Eberhards. 260 Sekunden zu 400 Sekunden bei ca. 1.000.000 Datensätzen und 100.000 Ergebnissen.
So, jetzt gehe ich Sonne.
Frithjof
@Frithjof
So, habe den Index gesetzt und es ist um ein vielfaches schneller geworden:
840Tsd DS zu 234Tsd in 316 Sekunden, allerdings unsortiert.
Würdest Du bitte noch die Sortierung nach "sortnr" einarbeiten?
Danach könnte ich mal die gesamten Tabelle auf mein Projekt umschreiben (Feldnamen etc.) und dann sehen wie schnell es geht.
Gruß Torsten.
@Eberhard
ZitatIn meinem Vorschlag bezieht sich die Bezeichnung "tbl_Kreuztabelle" auf die vorher gezeigte Kreuztabellenabfrage (Bezeichnung ist etwas unglücklich gewählt). Leider ließ sich diese Abfrage nicht direkt als Unterabfrage einbinden.
Ich lese Deine Info 8)"rauf und runter, rechts und links" 8) aber ich verstehe nicht wie ich es umsetzen muß um zum Ergebniss zu kommen. Bitte helfe mir das zu verstehen.
Vielen Dank, Torsten.
In Beitrag #16 sind zwei Abfragen gezeigt. Die erste ist eine Kreuztabellenabfrage. Diese ist unter einem Namen abzuspeichern.
Die zweite Abfrage verwendet diese Kreuztabellenabfrage als Datenbasis, also müsstest Du
- beide Abfragen übernehmen,
- in der zweiten Abfrage den Namen der Kreuztabellenabfrage übernehmen, der unglücklich "tbl_Kreuztabelle" lautet.
Sinnvolle Indizierungen dabei:
=> zusammengesetzter Index (http://www.ardiman.de/datenbanken/grundlagen/tabellen/#SEC1) auf gruppe + textfeld1
=> Index auf sortnr
Alles klar, verstanden.
Vielen Dank für die Geduld :)
Hallo Torsten
Zitat von: Torsten. am Februar 24, 2018, 16:44:52
@Frithjof
Würdest Du bitte noch die Sortierung nach "sortnr" einarbeiten?
Sortierung habe ich eingebaut. (ich dachte das schaffst Du selber ;))
Allerdings muss ich sagen, dass Eberhards Variante mit der Kreuztabelle erheblich schneller ist.
Die habe ich ebenfalls implementiert: Prozedur <machErgebnisKreuz>.
Die Abfrage <qryKreuz_U1> enthält die Kreuztabelle
Die Abfrage <qryKreuz_U2> wird in der Prozedur <machErgebnisKreuz> aufgebaut und dann
für die Anfügeabfrage (nur im VBA-Code) genutzt.
Frithjof
@ Frithjof,
vielen Dank für die Nachbearbeitung.
Gruß Torsten.
An alle
@Eberhard
@Frithjof
@Lachtaube
@Klaus
@Beaker s.a.
@Franz
die Ihr mir bei dem Projekt geholfen habt, möchte ich mich hiermit bedanken.
Noch zur Info:
Ich habe jetzt die Funktion und den Nachtrags-Code von Eberhard aus Antwort #7 in mein Projekt eingebunden.
Dadurch habe ich eine Zeitersparnis von 36 Std. auf jetzt knapp 4 Minuten - einfach enorm :)
Nochmals vielen Dank.
Beste Grüße
Torsten.