Mehrere unabh. Instanzen, nicht nur Copy-Problem?
22.02.2024 23:26:29
Frank Dreier
ich habe wieder mal ein Problem. VBA is ja grundsätzlich Single-Core Basiert und Excel soll ja eigentlich auch nicht in mehreren Instanzen geöffnet werden. Bei reinen Kalkulationsarbeiten kann die Geschwindigkeit bei 2 Instanzen allerdings um ca. 80% beschleunigt werden und bei 4 Kernen um bis zu 360% (meine Erfahrung).
Ein bekanntes Problem beim Nutzen mehrerer Instanzen ist die Copy-Funktion bzw. Zwischenspeicher da dieser von allen Excel-Instanzen gleichsam genutzt wird. Man darf also nicht mit dem Zwischenspeicher von Windows arbeiten. Das kann man ja umgehen mit z.B. Range("A1").value = Range("B1).value.
Soweit so gut.
Ich habe nun noch ein anderes Problem gesehen (neben der Copy-Problematik) und wollte fragen welche Funktionen Ihr noch kennt: Bis 2 unabhängige Instanzen kann Excel mit minimalem Leistungsverlust folgenden Code ausführen. Dieser Code ist z.B. gut wenn sich nur wenige Daten in der Ursprungs-Einlesedatei ändern (bei mir eine TXT),- sollte der abgeglichene Wert erreicht sein wird das Einlesen abgebrochen. Das erspart enorm viel Zeit bei vielen Datensätzen da damit nicht die kompletten Datensätze neu eingelesen werden müssen sondern nur die geänderten. in meinem Beispiel ist der Einlesestopp in Spalte AA festgelegt, der loop endet dann und die Datei wird mit "Close" geschlossen. Hier der Code:
Dim Quelldatei As String
Dim Zeile As Long
Dim Inhalt As String
Dim Information() As String
Dim i As Integer
Zeile = 2
Quelldatei = "E:\test.txt"
Open Quelldatei For Input As #1
Do While Range("AA" & (Zeile - 1)) = 1
Line Input #1, Inhalt
Information = Split(Inhalt, ";")
ActiveSheet.Cells(Zeile, 20 + i) = Information(0 + i)
ActiveSheet.Cells(Zeile, 20 + 1) = Information(0 + 1)
ActiveSheet.Cells(Zeile, 20 + 2) = Information(0 + 2)
Zeile = Zeile + 1
Loop
CloseNun aber zum Problem: Excel scheint auch hier nur einen begrenzten Einlesespeicher zu besitzen (oder ist Windows das Problem?). Bei 2 unabhängig geöffneten Instanzen ist der "Einlese"-Leistungsverlust noch erträglich, kaum merkbar, ca. 15% langsamer als bei nur einer offenen Instanz. Bei 3 Excel-Instanzen allerdings sinkt der Speed zur Single-Instanz schon auf 50% ab,- also halb so schnell und bei geöffneten 4 Instanzen sind es schon 60% langsamer.
Excel scheint also (zumindest beim obigen Code) ein Nadelöhr zu treffen. Weiß jemand warum die obige Importfunktion so fragil ist? Hat jemand noch andere Importfunktionen parat? Man kann natürlich die Workbook.open funktion verwenden. - Hier entsteht allerdings wieder das Problem, dass die Datensätze die eingelesen werden von vornherein feststehen, - nämlich die Menge die nunmal in dem Workbook stehen. Man kann dann natürlich einen Internen Abgleich durchführen nach dem Einlesen aber das einlesen muss komplett vorher geschehen (und kostet entsprechend wieder Zeit) - so zumindest mein Kenntnissstand.
Wärt Ihr so lieb und würdet Eure externen Datenimport-Funktionen mal darstellen? Auch gerne die Workbook.open Funktion nochmal. Ich teste dann mal alle durch ob Excel am Ende evtl. mit einer kompletten Einlesung einer Datei mit Datensätzen evtl. doch wieder schneller ist bei z.B. 3 geöffneten unabhängigen Instanzen, als mit einzelnen Zeilen einlesen so wie oben im Code, da dann eben das Nadelöhr umgangen wird.
Die Einlesung selber ist sehr schnell, insbesondere nur für z.B. 6 Zellen mit ca. 10 Zeichen. Das Problem ist aber dann, dass Excel (oder Windows?) in dieser Millisekunde merkt hier gibt es ein Nadelöhr und reduziert auch den Folgespeed den Excel verwendet in den Instanzen um diese 15, 50 bzw. 60% (z.B. auch bei Berechnungen) und zwar über ca. eine halbe Minute (zu sehen im Taskmanager - die Kernauslastung sinkt entsprechend ab wenn Excel bzw. Windows dies veranlasst)! Erst nach einer gewissen Zeit (meistens so 30 Sekunden) erhöht Windows (oder Excel) den Speed wieder und die Kernauslastung steigt wieder an - bis es wieder zu diesem Nadelöhr kommt und der Speed wird wieder reduziert - über viele Sekunden.
1. Ansatz:
Kann man hier evtl. etwas mit DoEvents machen? Ich habe schon DoEvents for den Code gesetzt. Leider keine Änderung. Der Speed wird trotzdem über ca. 30 Sekunden runtergesetzt. Man würde also versuchen Excel zu sagen: Warte bitte bis der Einlesevorgang wieder frei ist und starte dann wieder. Somit würde man die Systembegrenzung die danach einsetzt, umgehen- da weder Excel noch Windows ein Problem feststellt. Das ist wohl der sinnvollste Ansatz. Leider kenne ich mich damit nicht gut aus.
Mein 2. Ansatz: Evtl. kann man dieses Nadelöhr mit unterschiedlichen Importfunktionen von Excel umgehen, daher die Frage an Euch. Ich brauche 3 unabhängig Laufende Instanzen mit Importfunktion leider. Ich hoffe mal nicht, dass sämtliche Importfunktionen von Excel über dieses Nadelöhr laufen.
PS: Ich konnte allerdings den Speed der Berechnungen durch den Split in 3 unabhängige Excelmappen schon um ca.800% Steigern (nicht bei allen Daten die ich brauche aber bei einem großen Teil) - nun sind es noch ca. 400% schneller durch diese oben beschriebene Problematik. Also immer noch deutlich besser als über eine Instanz falls das jemand wissen will. Für mich hat es sich schon gelohnt aber es ist natürlich ärgerlich, dass nach der ganzen Arbeit wieder so ein (unbekanntes?) Problem auftaucht.