Office: (Office 2007) copyfromrecordset verdammt langsam

Hallo,
ich mache eine Datenbankabfrage via ADODB.

Habe nun ein volles Recordset (mit so 1000-1500 Einträgen je 6 Spalten).

Nun kopiere ich mit "CopyFromRecordset" das gesamte Ergbnis in ein Blatt
Code:

Das dauert aber ewig. Excel friert ein, nach 5 Minuten löst es sich dann kurz und man sieht neue Einträge auf der Seite und dann friert es abermals für 5 Minuten ein.

Habt ihr eine Idee, wie man das verbessern könnte oder an was diese Trägheit liegt (1500 Ergebnisse finde ich jetzt nicht extrem viel, dass ihn das überfordern könnte).

Danke für die Hilfe

:)

 

Hallo Unregi,

die Datenmenge ist eigentlich zu klein für das auftretende Problem.

Andere Idee:

Die Variable target deutet darauf hin, dass du den Code möglicherweise in einem Change-Ereignismakro verwendest. In diesem Fall musst du am Anfang des Codes die Ereignisse mit

Application.EnableEvents = False

ab- und am Ende mit

Application.EnableEvents = True

wieder einschalten. Ansonsten läuft der Code in eine Endlosschleife, denn der Code löst ja selbst wieder das Ereignis aus, das ihn starten lässt, weil er den Inhalt von Zellen des Tabellenblattes ändert.

Gruß Ingolf

 

Hallo,
Danke fuer den Tipp.

Aber das ist kein Event. Ich habe die Range als "Ziel" Target genannt.

Habe es trotzdem mal versucht mit ausschalten der Events und der automatischen Berechnung - war leider trotzdem nicht schneller.

 

Hallo,

neben der CopyFromRecordset-Methode gibt es ja noch die Möglichkeit, die Daten per GetRows in ein Variantarray zu holen und dieses dann in das Tabellenblatt zu schreiben. Dabei muss das Array allerdings transponiert werden. Hier mal ein Beispielcodeschnipsel, der Daten aus einem ADODB-Recordset in ein Variantarray holt, eine Funktion aufruft, um das Array zu transponieren und die Daten anschließend in ein Tabellenblatt schreibt:

Und hier noch die verwendete Funktion zum Transponieren des Arrays:

Ich befürchte allerdings, dass der Fehler an einer anderen Stelle liegt.

Gruß Ingolf

 

Das eigentlich Zeitkritische ist das results.open, also das Ausführen der Abfrage. Je nach Abfragedesign, Datenmenge und Verbindung kann das von etwas größer null bis mehrere Stunden dauern.

Also solltest Du etwas ausführlicher schildern, was Du genau machst.

 

Ich bin im Debug Modus schon durch. Die abfrage an sich, also das open, ist innerhalb paar wenigen Sekunden fertig.


Zu dem was ich mache:
Ich habe 2 sehr große Excel-Tabellen. Ich frage nun ab, welche Datensätze nur bei Tabelle 1 drin ist aber nicht bei Tabelle 2 (2 Spalten Einträge müssen gleich sein, der Rest ist unterschiedlich).
Alle fehlenden Einträge werden in eine neue Tabelle geschrieben.

Das ganze ist nur paar Zeilen lang.

Öffnen der Connection,
Ausführen der Query
In die andere Tabelle schreiben

Erst wollte ich es auch schon ganz über SQL machen, also mit einem Insert Into aber das ging nicht.

 

Hallo,

bei wirklich großen Exceltabellen und einem etwas älteren Rechner kann ich mir schon vorstellen, dass das Ganze einfach aus dem zur Verfügung stehenden RAM läuft. Um mal eine Beispielrechnung aufzumachen:

100 Spalten á 20 Zeichen mal 50000 Zeilen = knapp 100MB Daten - und da sind die Tabelleninformationen noch gar nicht mit drin. Wenn die Exceldatei, aus der die Daten stammen, dann auch noch gleichzeitig geöffnet ist, sind die Daten gleich 4 mal vorhanden (die beiden Vergleichstabellen in der Quelldatei plus das Recordset plus die zu schreibende Tabelle). Das macht dann 400 MB. Dann kommt noch die Excelanwendung selbst hinzu und was sonst vielleicht noch so alles geöffnet ist. Ein 512 MB RAM Rechner sieht da ganz schön alt aus.

Bei so einer Konstellation würde ich vorzugsweise auf Access ausweichen. Access hat den Vorzug, dass nicht der gesamte Datenbestand in den Arbeitsspeicher geladen wird, sondern nur die gerade tatsächlich benötigten Datensätze.

Wenn es denn Excel sein muss, würde ich die Abfrage so anlegen, dass die Daten in eine andere als die Quelldatei geschrieben werden, so dass die Quelldatei geschlossen bleiben kann. Dann muss natürlich darauf geachtet werden, dass die Datei nicht versehentlich doch geöffnet ist.

Wenn das noch nichts bringt, könnte man noch versuchen, das Recordset in ein Array zu übernehmen und dann zunächst per FileSystemObject-Zugriff in eine CSV-Datei zu schreiben. Dabei wird direkt auf die Platte geschrieben, was die RAM-Belastung niedrig hält. Nach der Übernahme in ein Array sollten natürlich zunächst die ADODB-Objekte verworfen werden, insbesondere das Recordset, um Speicherplatz frei zu bekommen. Die CSV-Datei kann dann, nach dem Verwerfen des Arrays, wiederum um Speicherplatz zu schaffen, in eine Exceldatei importiert werden.

Falls du dabei Hilfe benötigst, würde ich dir auch ein kleines Beispiel schreiben. Im Moment weiß ich allerdings noch nicht, ob das für dich überhaupt infrage kommt.

Gruß Ingolf

 

Code:

Ich glaube nicht, dass das eine Rolle spielt, aber diese Anweisung würde ich außerhalb (nach) der With-Anweisung vornehmen.

Hast Du spaßeshalber mal probiert, nach Erstellung des Recordsets die Quell-Arbeitsmappe zu schließen und erst dann das CopyFromRecordset auszuführen?

Hast Du das gleiche Problem, wenn das Ergebnis in ein weiteres Arbeitsblatt der Quellmappe eingefügt wird und somit die Zielmappe nicht verwendet wird (geschlossen ist)? Als Variante könnte man das Arbeitsblatt / den Bereich im Nachgang in die andere Mappe kopieren.

Hinweis bzgl. CSV: Für ein ADODB-Recordset gibt es die Methode GetString, womit Du den Inhalt der CSV-Datei (= ein String) auf einen Schlag erzeugen kannst. Die CSV-Datei lässt sich so einfach schreiben: WriteFile

Deine Inkonsistenzabfrage sieht etwa so aus?
Code: