Dear AllMy client has a database with near 60000 documents included. All theese documents can be accessed in one view. This database grows about 300-400 documents per business day. My task is to create report by iterate all theese documents and make some calculations based on documents fields to create balance list and transaction (turnover) register. I had created agent that allowed to run on server. Code placed below.
Please, tell me, does most effective way to iterate large amount of documents exist? Maybe some tricks or magics?
Dim ns As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim i As Long
Dim sum, sum1 As Currency
Dim oneSum As Currency
Dim FIO As String
Dim color As String
Dim pac As String
Dim action As String
i=1
Sum=0
sum1=0
Set db = ns.CurrentDatabase
Set view = db.GetView("main")
Set doc = view.GetFirstDocument
While Not doc Is Nothing
oneSum=doc.counter(0)
color = doc.category(0)
pac = doc.category_1(0)
FIO = doc.Category_2(0)
action = doc.Category_3(0)
If FIO = "John" Then
sum = oneSum + sum
End If
If FIO = "Joe" Then
sum1 = oneSum+sum1
End If
’ some similar actions…
i=i+1
If (i Mod 3000) = 0 Then
Print i & " " & Now ' to timing
End If
Set doc = view.GetNextDocument(doc)
Wend
In theory, view entries should be much faster, but I too have found that this is not always the case. They especially appear to be sensitive to the sort order in the view and where your documents appear in that order. Since you’re going through all the docs in the view, I’m not sure how that’ll pan out.
Also in theory, db.search will be faster because you’re going after 100% of the docs in the database, but I get nervous about holding really large collections of documents in memory, so I’d probably use the method you have, just with AutoUpdate = False.
Those results are not consistent with tests which I have run annually against R3, R4, R5, ND6, and ND7. But the exact situation is certainly important. My testing indicates that view.AutoUpdate = False is critical to performance if you’re using the view as your collection … set doc = view.GetNextDocument ( doc )
My testing also indicates that reading the view entries can be faster under some circumstances, but the view setup and how many docs/entries you retrieve and where they are located in the view appear highly relevant.
As I said these weren’t my results, but from an article in the VIEW magazine.
The test for view.autoupdate false was specifically on just reading data, not making any alterations to any documents which may affect the view in any way (which would make a difference of course).
But then this is still only one other user’s test effectively, and he does also give details on how to test your own applications for speed as no 2 apps are the same.
Create a view that categorizes and totals by the FIO field.
e.g.
John 20
Joe 10
Kilroy 52
Give this view to the managers, or if you need to create a report, simply use a NotesViewNavigator to extract the column values without having to read the documents whatsoever.
Thanx! It’s realy approach, but I have up to seven “categories” and need to have totals like “category two” on “category one” and “category one” on “category two” and so on. For examplePeter 47$