Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1320to1324
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Performance verbessern

Performance verbessern
11.07.2013 07:47:01
Pascal
Guten Tag miteinander
In der beiligenden Excel-Beispielsdatei geht’s um folgendes:
https://www.herber.de/bbs/user/86301.xlsm
Auf dem Tabellenblatt "Probleme" werden aus einem Ticket-System raus Datensätze kopiert. Diese Liste umfasst derzeit im Original-File über 170'000 Datensätze (und es werden laufend mehr)
Auf dem Tabellenblatt "Such-Kriterien" sind diverse Suchbegriffe aufgeführt, nach welchen die Datensätze durchsucht werden sollen.
Es wird also nach Klick der Schaltfläche "Auswertung aufbereiten" auf dem Tabellenblat "Such-Kriterien" in den Datensätzen (=Probleme) nach Übereinstimmungen (=Such-Kriterien) gesucht.
Wird eine Übereinstimmung gefunden, so wird der Datensatz komplett auf das Tabellenblatt "Auswertungen" kopiert.
Gleichzeitig wird auf dem Tabellenblatt SM7_Tickets die entsprechende Übereinstimmung mit den gefundenen Ticket-Nummern ergänzt. (Anzahl)
Das Ganze läuft soweit gut und ohne Fehler
Doch … ich würde Euch nicht um Hilfe bitten, sollte ich keine Probleme haben.
Mein Problem besteht darin, dass das Makro unendlich loopt wenn ich über 170'000 Datensätze damit durchsuchen will.
Kann ich irgendwas im Code ändern / anpassen, damit das Makro auch dann noch läuft, wenn bald mal über 300'000 Datensätze dasein sollten ?
Besten Dank für Eure Tips

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Performance verbessern
11.07.2013 09:18:09
selli
hallo pascal,
mal ganz ehrlich 170000 datensätze sind eben 170000 datensätze. dass das dauern kann bleibt unumstritten.
eine lösung die hier im forum schon oft angeboten wurde ist folgender code. (wer den ursprünglich mal angeboten hat, entzieht sich meiner kenntnis)
ohne mir deine datei angesehen zu haben, könnte dir das aber schon etwas helfen und viel mehr darüberhinaus wird wohl nicht möglich sein.
den code in ein standard modul kopieren und zu beginn deines makros
GetMoreSpeed True einfügen.
am ende dann
GetMoreSpeed False
wichtig noch zu erwähnen, dass wenn es zu einemfehlerbedimgten abbruch deines codes kommen sollte, müsstest du sicherstellen, dass GetMoreSpeed False ausgeführt wird, um excel wieder mit allen annehmlichkeiten gangbar zu machen. trotzdem : vorsicht damit.
Sub GetMoreSpeed(Optional ByVal Modus As Boolean = True)
Static intCalculation as Integer
If Modus = True Then intCalculation = Application.Calculation
With Application
.ScreenUpdating = Not Modus
.EnableEvents = Not Modus
.Calculation = IIf(Modus = True, xlManual, intCalculation)
.Cursor = IIf(Modus = True, 2, -4143)
End With
End Sub
gruß
selli

Anzeige
Excel ist eine Tabellenkalkulation, keine ...
11.07.2013 10:01:34
Klaus
... Datenbank, Pascal.
Das es 17.179.869.184 Zellen gibt heisst nicht, dass man auch alle ausnutzen sollte.
Es gibt keine "magische Grenze" und es kommt auch immer auf den Fall an, aber bei einer 6-stelligen Anzahl von Datensätzen sollte man sich ernsthaft überlegen, ob man nicht auf Access, SQL oder ein anderes geeigneteres Format ausweichen kann.
Grüße,
Klaus M.vdT.

AW: Excel ist eine Tabellenkalkulation, keine ...
11.07.2013 10:31:41
Pascal
hallo zusammen!
besten Dank für Eure antworten
@selli
besten Dank für den Code. Aber ... auch nach über einer Stunde Laufzeit noch kein Makro-Ende. (habs nun abgebrochen)
@Klaus
... Recht hast Du! Das hätte ich schon lange in Aces gemacht. Doch ... leider haben wir hier kein Aces im Einsatz :-(

Anzeige
Frag mal ....
11.07.2013 10:46:21
Klaus
.... deinen Cher, wie du die Formel-1 gewinnen sollst, wenn er dir nur einen VW-Polo zum mitfahren gibt!
Oder, das Makro starten, Zeitung raus, Kaffe in die Hand und Füße hoch. Wenn der Chef reinkommt, sagst du "ich arbeite. Mit Excel. Access haben wir ja nicht." :-)
Ich hab hier auch eine Tabelle im Einsatz, mit über 300.000 Einträgen - die aus diversesten Gründen und Chef-Entscheidungen in Excel bleibt. Der Makrodurchlauf dauert übersichtliche 45 minuten.
Haben wir so gelöst, dass mein Kollege das Ding anstößt bevor er ins tägliche Teammeeting geht, das dauert auch 45 minuten :-)
Bei ein paar Stunden ... vielleicht kannst du das Ding einmal über Nacht laufen lassen? Einfach um zu wissen, ob es nach sagen wir 16h überhaupt zu einem Ergebniss kam?
(wenn du ganz pfiffig bist, schreibst du dan den Makroanfang:
dim t
t = timer
u
nd ans ende
application.statusbar = "brauchte " & timer - t & " sek gesamt"
dann weisst du worauf du dich einstellen musst)
Den Code den ich kenne halte ich für mit meinen schmalen Kentnissen nicht für weiter optimierbar - du benutzt bereits Arrays statt in den Tabellen zu schreiben usw.
Jetzt liegt es an dir, ganz tief in die Anaylse zu gehen: Brauchst du wirklich alle ZEilen? Kannst du vielleicht per Autofilter einen guten Prozentsatz herausfiltern, bevor du die Schleife über alle Zeilen anlegst? Kannst du die Datenflut sonst irgendwie eindämmen, bevor du analysierst? Müssen die Berechnungen während es Makrolaufs an sein (vgl GetMoreSpeed-Modul), wenn ja: müssen langsame und volatile Formeln aktiv sein oder kannst du diese tempörär deaktivieren?
Grüße und viel Erfolg,
Klaus M.vdT.

Anzeige
AW: Frag mal ....
11.07.2013 11:01:16
JACKD
Hallo Pascal..
Ich bin mir grad nicht sicher, aber Rudi oder Luc (oder einer der Konsorten :-D) meinte glaub ich das man mit nem scripting dictionary schneller vorran kommt (als mit array) ob der Datenmenge, wahrscheinlich auch noch nicht performant genug.
ansonsten wäre vielleicht PowerPivot noch ne Option. Arbeitet ja wie ein originäres Datenbank-Tool vielleicht auf diesem Wege
Grüße

AW: Excel ist eine Tabellenkalkulation, keine ...
11.07.2013 11:33:00
fcs
Hallo Pascal,
ein paar Sachen kann man schon noch machen, aber Masse ist halt Masse.
23 Suchbegriffe mal 100000 Datenzeilen sind halt 2,3 Mio Schleifendurchläufe.
In deinem Makro sind folgende Optimierungen möglich:
1. Die Begriffe in Spalte O werden in jedem Schleifendurchlauf auf 11 Zeichen abgeschnitten.
Das sollte man nur einmal machen und die Ergebnisse in einem separaten Array ablegen.
Dabei sollte dann der Text bis zum 1. Punkt ausgelesen werden (Das scheint ja der wichtige Vergleichstext zu sein)
2. Der in 1. ermittelte String kann dann per = mit den Kriterien verglichen werden.
Das ist schneller als Instr-Wert zu vergleichen und du bekommst keine Mehrfachausgabe einzelner Zeilen für Kriterien, denn "Drucker2" als Kriterium liefert auch die Zeilen mit "Drucker21" als Ergebniszeilen.
3. Du muss nicht in jedem Schleifendurchlauf die Daten im Blatt "SM7_Tickets" eintragen.
Es reicht wenn du es wie beim Auswertungsblatt einmal am Ende machts.
Das dürfte auch bei dir der Zeitfaktor sein, denn Datenarrays sind schon extrem schnell im Vergleich zu jeder Zelloperation in einem Tabellenblatt.
Ich hab dein Makro mal in diese Richtung modifiziert.
Ansonsten wäre noch ein Ansatz im Blatt Probleme in einer weiteren Spalte P den Text aus Spalte O bis zum 1. Punkt einzutragen. Danach kannst du dann mit dem Autofilter oder auch Pivot-Tabellenbericht arbeiten um Auswertungen zu machen.
Gruß
Franz
Public Sub Auswertung()
On Error Resume Next
Dim arrIn As Variant
Dim ArrKriterien As Variant
Dim arrOut As Variant
Dim L As Long
Dim lngCount As Long
Dim lngIndex As Long
Dim CI_Text_11() As String
arrIn = Sheets("Probleme").Range("A1").CurrentRegion
ArrKriterien = Sheets("Such-Kriterien").Range("A3:A26") 'Anpassen
ReDim arrOut(1 To UBound(arrIn), 1 To UBound(arrIn, 2) + 1)
ReDim CI_Text_11(1 To UBound(arrIn))
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
'Altdaten löschen
With Worksheets("Auswertungen")
L = .Cells(.Rows.Count, 1).End(xlUp).Row
If L > 2 Then
.Range(.Rows(3), .Rows(L)).ClearContents
End If
End With
With Worksheets("SM7_Tickets")
L = .Cells(.Rows.Count, 1).End(xlUp).Row
If L > 2 Then
.Range(.Cells(3, 1), .Cells(L, 6)).ClearContents
End If
End With
'liest aus der Spalte O (CI) die Zeichen bis zum 1. Punkt aus
For lngCount = 1 To UBound(arrIn)
L = InStr(1, arrIn(lngCount, 15), ".")
If L = 0 Then
CI_Text_11(lngCount) = arrIn(lngCount, 15)
Else
CI_Text_11(lngCount) = Left(arrIn(lngCount, 15), L - 1)
End If
Next
For L = LBound(ArrKriterien) To UBound(ArrKriterien)
For lngCount = 1 To UBound(arrIn)
If InStr(1, arrIn(lngCount, 6), ArrKriterien(L, 1)) Or _
CI_Text_11(lngCount) = ArrKriterien(L, 1) Then
lngIndex = lngIndex + 1
arrOut(lngIndex, 1) = ArrKriterien(L, 1)
arrOut(lngIndex, 2) = arrIn(lngCount, 1)
arrOut(lngIndex, 3) = arrIn(lngCount, 2)
arrOut(lngIndex, 4) = arrIn(lngCount, 3)
arrOut(lngIndex, 5) = arrIn(lngCount, 4)
arrOut(lngIndex, 6) = arrIn(lngCount, 5)
arrOut(lngIndex, 7) = arrIn(lngCount, 6)
arrOut(lngIndex, 8) = arrIn(lngCount, 7)
arrOut(lngIndex, 9) = arrIn(lngCount, 8)
arrOut(lngIndex, 10) = arrIn(lngCount, 9)
arrOut(lngIndex, 11) = arrIn(lngCount, 10)
arrOut(lngIndex, 12) = arrIn(lngCount, 11)
arrOut(lngIndex, 13) = arrIn(lngCount, 12)
arrOut(lngIndex, 14) = arrIn(lngCount, 13)
arrOut(lngIndex, 15) = arrIn(lngCount, 14)
arrOut(lngIndex, 16) = arrIn(lngCount, 15)
If lngIndex = UBound(arrOut) Then
MsgBox "Outarray ist voll"
End If
End If
Next
Next
Sheets("Auswertungen").Range("A3:P" & lngIndex + 3) = arrOut
Sheets("SM7_Tickets").Range("A3:B" & lngIndex + 3) = arrOut
With Worksheets("SM7_Tickets")
L = .Cells(.Rows.Count, 1).End(xlUp).Row
If L > 2 Then
.Range(.Cells(3, 4), .Cells(L, 4)).FormulaR1C1 = "=IF(RC[-3]=R[-1]C[-3],"""",RC[-3])"
.Range(.Cells(3, 4), .Cells(L, 4)).FormulaR1C1 = "=RC[-4])"
End If
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige