Datenexport Performance optimiert

[Legolas]

Hallo Forum,

ich habe eine Anwendung mit ca. 600.000 Dokumente in einer Ansicht mit ca. 150 Spalten.
Diese möchte ich zur Auswertung nach EXCEL exportieren.

Das funktioniert soweit auch bis auf die Tatsache, dass dies eine gefühlte Ewigkeit geht.
Ich habe das Ganze über  NotesViewEntryCollection = view.AllEntries  gelöst, da ich davon ausgegangen bin, dass die Daten dann direkt aus der view gelesen werden ohne dass ich jedes Dokument anfassen muss.

Gibt es evtl. noch eine andere Möglichkeit die schneller ist als diese?
Bin offen für (fast) alles!

Grüße
Bernd

[Tode]

Hier gab es mal eine Abhandlung zu dem Thema. Ich habe gerade eben nochmal in einer 80.000 Dokumente View den Vergleich zwischen NotesViewNavigator und NotesViewEntryCollection laufen lassen, und kann die Ergebnisse für Notes 9.0.1 nicht bestätigen. Bei mir hat der NotesViewNavigator mit 9 gegenüber 14 Sekunden klar gewonnen...

Also dieser Code:

Code

Dim varTest as Variant
Dim viwTest as NotesView
Dim vec as NotesViewEntryCollection
Dim viwNav as NotesViewNavigator

Dim ve as NotesViewEntry

Dim dblStart as Double
Dim varX as Variant

dblStart = Now
Set viwTest = g_dbFocus.GetView( "($All)" )
Set viwNav = viwTest.CreateViewNav
Set ve= viwNav.GetFirst()
While not ve is Nothing
  varX = ve.ColumnValues(0)
  Set ve = viwNav.GetNext(ve)
Wend
Messagebox Now - dblStart   

gewinnt mit nur 9 sec. Laufzeit gegen

Code

Dim varTest as Variant
Dim viwTest as NotesView
Dim vec as NotesViewEntryCollection
Dim viwNav as NotesViewNavigator

Dim ve as NotesViewEntry

Dim dblStart as Double
Dim varX as Variant

dblStart = Now
Set viwTest = g_dbFocus.GetView( "($All)" )
Set vec = viwTest.AllEntries
Set ve=vec.GetFirstEntry()
While not ve is Nothing
  varX = ve.ColumnValues(0)
  Set ve = vec.GetNextEntry(ve)
Wend
Messagebox Now - dblStart   

diesen Code mit 14 sek. Laufzeit. (auf zwei verschiedenen Datenbanken getestet, die beide zwischen 80.000 und 90.000 Dokumenten haben)

[umi]

Hallo

Die entscheidende Frag hier ist zuerst:
Wie exportierst Du das ganze nach Excel ?
Via CSV oder via COM ?

[jBubbleBoy]

in Excel kann durch das setzen der ScreenUpdating-Eigenschaft die Performance erhöht werden:

Code

excel.ScreenUpdating = false
'-- Excel Werte setzen 
excel.ScreenUpdating = True  

[Andrew Harder]

Hier Ich habe gerade eben nochmal in einer 80.000 Dokumente View den Vergleich zwischen NotesViewNavigator und NotesViewEntryCollection laufen lassen, und kann die Ergebnisse für Notes 9.0.1 nicht bestätigen. Bei mir hat der NotesViewNavigator mit 9 gegenüber 14 Sekunden klar gewonnen...

Was mich interessieren würde:
Wenn Du den Autoupdate der View auf False setzt und den Navigator Cache auf 400 hochsetzt, wird es dann (Debugger muss unbedingt aus sein) noch schneller?
Oder ist das in Notes 9.01 schon so optimiert, das das nichts mehr bringt?

Ich hatte da mal einen Export großer Datenmengen in 8.5.x gemacht, da dachte ich erst das da ein Fehler sein müsse, so schnell war das dann.

[Legolas]

Hallo Zusammen,

Danke für die Rückmeldungen.

@Umi: Ich exportiere zuerst in eine CSV Datei. War schon deutlich schneller als direkt nach EXCEL zu exportieren.
@Tode: Den Tipp mit der NotesViewEntryCollection werde ich heute noch ausprobieren. Ich gebe dann noch eine Rückmeldung.
@Andrew Harder: OK, das Thema mit dem Navigator Cache ist mir komplett neu. Werde mal prüfen wie das geht und ob das was bringt. AutoViewUpdate auf False hatte ich schon gesetzt.

Grüße
Bernd

[Legolas]

Hallo Zusammen,

hier nun die angekündigt Rückmeldung:

@Andrew Harder: Die Cache-Einstellungen hatten leider keinerlei Einfluss auf das Zeitverhalten. Laut Hilfe wird diese Einstellung sowieso nicht verwendet!
@Tode: Die zwei Varianten hatten beim Export von 6000 Dokumenten nur geringe Unterschiede. Auch bei mir war Deine schnellere Variante die Schnellere. Statt 1 Min, 31 Sek brauchte die 1 Min 25 Sek.
Die große Bremse beim Export ist das Auslesen der 159 Spalten mit der Methode "viewentry.ColumnValues(iCols)". Diese benötigt mit Abstand die meiste Zeit. Zudem muss dann noch der Inhalt auf Chr$(10) und Chr$(13) sowie auf das Delimiterzeichen ";" gescannt werden, bevor ich den Datensatz in die CSV Datei schreiben kann. Schreibe ich nur die erste Spalte in die CSV Datei, benötigt die Funktion nur noch 8 Sekunden!

Grüße
Bernd

[Tode]

Hast Du schonmal probiert, wie schnell ein GetItemValue gegenüber dem ColumnValues ist? Normalerweise ist der Zugriff auf den Index VIEL schneller, aber bei der Menge an Daten könnte der direkte Dokumentenzugriff vielleicht sogar gewinnen (obwohl ich in einem Fall, in dem es auch um Performance ging, festgestellt habe, dass GetItemValue eine sehr teure Operation ist)...

[Ralf_M_Petter]

Hallo Zusammen,

hier nun die angekündigt Rückmeldung:

Die große Bremse beim Export ist das Auslesen der 159 Spalten mit der Methode "viewentry.ColumnValues(iCols)". Diese benötigt mit Abstand die meiste Zeit. Zudem muss dann noch der Inhalt auf Chr$(10) und Chr$(13) sowie auf das Delimiterzeichen ";" gescannt werden, bevor ich den Datensatz in die CSV Datei schreiben kann. Schreibe ich nur die erste Spalte in die CSV Datei, benötigt die Funktion nur noch 8 Sekunden!

Grüße
Bernd

Äh du machst aber nicht wirklich viewentry.ColumnValues(iCols) in deinem Code, oder?. viewentry.ColumnValues gibt dir doch ein Array zurück, dass du natürlich zwischenspeichern solltest und nicht immer wieder über das API holen solltest. Wenn du Glück hast, cached der API Aufruf auch, ich würde mich aber nicht darauf verlassen.

Wenn du maximale Performance willst, dann solltest du die View auf Autoupdate false setzen und dann beim NotesViewNavigator den BufferMaxEntries auf 200 setzen. Das sollte das Auslesen der View sehr stark beschleunigen. Noch weitere Performance könnte es bringen, wenn du von Lotusscript auf einen Javaagenten umsteigst.

Der Code sollte dann ungefähr so aussehen.

Code

Dim varTest as Variant
Dim viwTest as NotesView
Dim vec as NotesViewEntryCollection
Dim viwNav as NotesViewNavigator

Dim ve as NotesViewEntry

Dim dblStart as Double
Dim varX as Variant

dblStart = Now
Set viwTest = g_dbFocus.GetView( "($All)" )
viwTest.autoupdate=false
Set viwNav = viwTest.CreateViewNav
viwNav.buffermaxentries=200
Set ve= viwNav.GetFirst()
While not ve is Nothing
  varX = ve.ColumnValues()
  Set ve = viwNav.GetNext(ve)
Wend
Messagebox Now - dblStart   

Grüße

Ralf

[Ralf_M_Petter]

Habe es schnell mal ausprobiert, die Variante mit den gesetzten Buffermaxentries benötigt bei einer Demodatenbank von mir 2,8 Sekunden und ohne Buffermaxentries benötigt es 74,6 Sekunden. Also eine beinahe unglaubliche Performancesteigerung mit einer simplen Änderung. Weil es mich interessiert probiere ich jetzt auch nicht wie sehr sich das mit der ungünstigen Verwendung des Arrays auswirkt.

[Ralf_M_Petter]

Wie ich erwartet habe ist die Methode die Columnvalues in einem Array abspeichern und dann nur auf das Array zugreifen wesentlich schneller als wenn ich jedes mal ve.ColumnValues(col) aufrufe. In meinem Test ca. 70%. Also wenn du diese beiden Optimierungen anwendest, sollte dein Code leicht ausreichend schnell laufen.

Was mir noch aufgefallen ist, der Lotusscript Debugger blockiert etwas wenn man ViewNavigatoren verwendet. Bitte daher alle Tests mit abgeschalteten Lotus Script Debugger machen.
 

[Andrew Harder]

Mit Buffer hochsetzen ist es also schneller und wenn der Debugger an ist muckt das rum.
Außerdem sollte man ein Array nicht jedesmal neu holen.

Wenn man das ganze NICHT nach Excel exportiert, sondern in ein CSV und erst das über COM in Excel speichert, dann ist der Export noch schneller.
Wurde hier von umi auch schon angedeutet, nur falls das zufälligerweise überlesen wurde.

[Legolas]

Hallo Ralf_M_Petter,

habe heute mal Deine Vorschläge umgesetzt.
Geht auch alles deutlich schneller bis auf eine Situation!

Was machst Du, wenn eine Ansicht Text und Multivaluefelder beinhaltet?
Dann kannst Du varX = ve.ColumnValues() nicht setzen. --> Führt zum Typmismatch!
Die als "DIM varX as Variabe" deklarierte Variable, kann keine Wertekombination von Strings und Arrays aufnehmen.
Z.B.: 5 Spaten als String, 1 Spalte mit Multivaluewerten.

Oder habe ich hier Tomaten auf den Augen?

Grüße
Bernd

[Ralf_M_Petter]

Eine Idee wäre es das ganze nicht einer Variable zuweisen, sondern direkt in der For all Schleife zu verwenden.

Sinngemäß, da ich jetzt keinen Designer zur Hand habe.

Code

forall werte in ve.columnvalues
if isArray(werte) then
rem Mache Verarbeitung multi value
else
rem Mache Verarbeitung Single Value
end if
end forall

Ich hoffe, dass das funktioniert, aber ich kann es derzeit nicht testen, da ich morgen unterwegs bin.

[diali]

kannst du nicht mehrere Spalten in der Ansicht zusammenfassen?

Es soll doch nur csv hintern herausfallen.
Jetzt kann doch in der ersten Ansichtsspalte syntaktisch ein Teilstring der CSV drin stehen, der die ersten 10 Spalten zusammenfasst.
Wendest du dies mehrfach an, werden aus 150 Spalten nur noch 15.
Wie viele Spalten du zusammenfasst, würde ich vom Inhalt (Größe) abhängig machen.

Die Zeichenbehandlung für CR, LF usw. kann auch gleich in den Ansichtsspalten mit erledigt werden.

Falls du schon Formatierungen usw. mitgeben willst, dann wäre auch das Excel-XML-Format interessant.

[Thomas Schulte]

Habe es schnell mal ausprobiert, die Variante mit den gesetzten Buffermaxentries benötigt bei einer Demodatenbank von mir 2,8 Sekunden und ohne Buffermaxentries benötigt es 74,6 Sekunden.

Wie groß ist hier deine Datenbasis?

[Legolas]

Hallo Zusammen,

nachdem ich gesehen habe, dass hier wohl reges Interesse an diesem Thema ist, anbei meinen vereinfachten Quellcode.
Vielleicht kann den ja mal jemand brauchen.

Zielsetzung:
- Möglichst performanten Datenexport. Datenbasis mit sehr großen Datenmengen. (100.000 Dokumente und mehr, Ansicht mit 160 Spalten)
Bedingungen:
- Die Exportansicht darf keine Multivalue Einträge beinhalten. Diese müssen zuvor in der Ansicht mit dem @Implode Befehl in einen String umgewandelt werden.
- Der Export wird als CSV Datei geschrieben und kann dann in EXCEL importiert werden.

Verbesserungsvorschläge zur Performanceoptimierung sind erwünscht!

Grüße
Bernd

Code

Sub Initialize
	On Error GoTo errhandler

	Dim ses As New NotesSession
	Dim db As NotesDatabase
	Dim view As NotesView
	
	Dim viewColumn As NotesViewColumn
	Dim viewentry As NotesViewEntry
	Dim viewNav As NotesViewNavigator
	
	Dim vColValues As Variant
	Dim lDocQuantity As Long
	Dim iColQuantity As Integer
	Dim iK As Integer
	Dim vHeadline As Variant
	Dim sHeadline As String
	Dim sPath As String
	Dim sFilename As String
	Dim iFilenum As Integer
	Dim sLine As String
	Dim lLauf As long
	Dim zwerg As String

	Set db = ses.currentDatabase
	Set view = db.Getview("Excel-Export")
	If view Is nothing Then
		MsgBox "EXCEL-Export Ansicht ist nicht vorhanden!", 64, "Hinweis"
		Exit sub
	End If
	
	view.Autoupdate = False
	iColQuantity=view.ColumnCount
	
	Set viewNav = view.Createviewnav()
	lDocQuantity = viewNav.Count
	viewNav.BufferMaxEntries = 400	
	viewNav.EntryOptions = Vn_entryopt_nocountdata
	
	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	'Spaltenüberschriften ermitteln
	vHeadline = view.columns()
	For iK=0 To iColQuantity-1
		If iK = 0 Then
			sHeadline = vHeadline(iK).title
		Else
			sHeadline = sHeadline +";" + vHeadline(iK).title
		End If
	Next 

	'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	'Export Dateiname
	sFilename = "C:\temp\All-EXCEL-export.csv"
	Print sFilename
	
	'Falls die Datein vorhanden ist, wird diese gelöscht.
	On Error Resume Next
	Kill sFilename
	On Error GoTo errhandler
	
	iFilenum = FreeFile
	Open sFilename For Output As iFilenum
	Print #iFilenum, sHeadline
	
	Set viewentry = viewNav.Getfirst()
	Do While Not (viewentry Is Nothing)
		lLauf = lLauf+ 1 
		If llauf Mod 20  = 0 Then
			Print "Export Datensatz...  " & lLauf & " / " &  lDocQuantity
		End if

		vColValues=viewentry.ColumnValues()
		For iK = 0 To iColQuantity-1
			zwerg = vColValues(iK)
			zwerg = Replace(zwerg, Chr$(10), " ")
			zwerg = Replace(zwerg, Chr$(13), " ")
			
			If iK = 0 Then 
				sLine = zwerg
			Else
				sLine = sLine +";" + zwerg
			End If
		Next
				
		'Daten schreiben...		
		Print #iFilenum, sLine
		
		Set viewentry = viewNav.GetNext(viewentry)
	Loop
	Close iFilenum
	
	MsgBox "Ende", 64, "Note"	
	Exit Sub
	
errhandler:
	MsgBox "Error in Agent: MS-EXCEL export      Error: " & Error & " (" & Err &  ") in Zeile: " & Erl, 64, "Hinweis"
	close
	Exit Sub
End Sub

[Tode]

Vielleicht ein Paar Gedanken zum Code:

1. Wenn Du noch mehr Performance brauchst (und zwar SIGNIFIKANT mehr), dann musst Du die Zeile sLine = sLine + ";" + zwerg durch die StringBuffer- Klasse von Julian Robichaux ersetzen, oder einen NotesStream verwenden, siehe hier.
2. Ein CSV- Export so wie Du ihn machst ist gefährlich:
Du solltest vor der String- Concatenation ENTWEDER alle Strings, die bereits einen Strichpunkt enthalten durch Anführungszeichen einschließen (dann musst Du bestehende Anführungszeichen ebenfalls maskieren oder ersetzen), oder die Strichpunkte durch etwas ersetzen, was Dir Deine CSV NICHT kaputt macht.
3. view.ColumnCount ist oftmals GRÖSSER als entry.ColumnValues, weil in ColumnCount ALLE Spalten gezählt werden, und in ColumnValues Konstanten und spezialwerte (wie z.B. @DocNumber) nicht drin sind, Du bekommst also in vielen Ansichten einen "Array Index out of Bounds" mit Deinem Code
Gruß
Torsten

[Legolas]

Hi Torsten,

danke für die schnelle Rückmeldung:

zu 1) Werde ich mir noch anschauen. Danke für den Topp.
zu 2) Das ist über die Ansicht schon gewährleistet. In den paar Spalten in denen Freitext eingebbar ist, werden die Werte in der Spalte schon geparst.
zu 3) Die Ansicht ist speziell für den Export aufbereitet und beinhaltet keine versteckten, kategorisierten oder sonstige Spalten, die nicht als Standard Spalte im Format "String" angezeigt werden.

Aufgrund der Performanceoptimierung hatte ich schon so viel Formatierungsprüfung wie möglich in die Ansicht verlegt. Das @Chr(10) und $Chr(13) soll dort auch noch mit rein.

Grüße
Bernd

[diali]

du kannst die Anzahl der Spalten reduzieren, indem du mehrere Spalten in eine zusammenfasst.