I have written a simple script that looks in 3 very similar databases for purposes of finding website addresses to present to our users. My problem is that one of the databases performs extremely slowly, whilst the others are extremely fast. The code slows to a crawl when processing getFirstDocument and getNextDocument for the second database, whilst the other 2 databases sail through the code instantly.
My question is: is there a database or view setting that affects the performance of the notesDocumentCollection?
I chose to build this using FTSEarch to avoid yet more views to these database which are already overcrowded with too many views. The code is run against local replicas.
Here’s some stats on the 3 databases
Database 1 (Referred to as Residential) - 693MB, 103768 docs, FT size 583MB, process time for database <1 sec
Database 2 (Referred to as Consumer) - 28MB, 32 docs, FT Index size 160KB, process time around 37 seconds !
Database 3 (Referred to as Commercial) - 75MB, 240 docs, FT size 531KB, process time <1 sec.
So far I have tried adding a default opening view for the database, also default design for new views, but neither have had any noticeable effect. I have also deleted the second database and downloaded a new replica in case of corruption. When the code is running it generates a lot of disk activity, it’s behaving as if it’s building a view or something equally hard disk intensive.
I have searched the designer help but there’s nothing in there to indicate what may effect performance, apart from using views.
Thanks in advance,
Phil
Here’s the code:
Sub promptForWebsites
%REM
Created by Phil Jeffery 10th March 2008
this will look at the lending configuration database and prompt them with a list of lenders and their websites.
Modifications
16/06/2008 (PJ) - changed the title on the dialog box, (copy and paste error)
26/06/2008 (PJ) - stopped updating the FT Index if it already existed.
%END REM
On Error Goto errHandling
Dim session As New notesSession
Dim workspace As New notesuiworkspace
Dim websiteDB As notesDatabase
Dim websiteCollection As notesDocumentcollection
Dim websiteDoc As notesDocument
Dim x As Integer
Dim websitesFound As Integer
Dim tmpDocument As NotesDocument
Dim queryString As String
Dim databaseToSearch(0 To 2, 0 To 4) As String
'add the database type, database path, the form, the field, the lender name field
'residential lenders
databaseToSearch(0,0) = "Residential"
databaseToSearch(0,1) = ReturnDBPath("LENDING CONFIG")
databaseToSearch(0,2) = "LENDER"
databaseToSearch(0,3) = "websiteAddress"
databaseToSearch(0,4) = "LendName"
'consumer lenders
databaseToSearch(1,0) = "Consumer"
databaseToSearch(1,1) = ReturnDBPath("CONSUMER LENDING")
databaseToSearch(1,2) = "CONLEND"
databaseToSearch(1,3) = "websiteAddress"
databaseToSearch(1,4) = "LenderName"
'commercial lenders
databaseToSearch(2,0) = "Commercial"
databaseToSearch(2,1) = ReturnDBPath("COMMERCIAL LENDING")
databaseToSearch(2,2) = "COMLEND"
databaseToSearch(2,3) = "websiteAddress"
databaseToSearch(2,4) = "LenderName"
'loop through the databases and
For x = 0 To 2
'find the database
Set websiteDB = session.GetDatabase("", databaseToSearch(x,1), False)
If websiteDB Is Nothing Then Error 10000, "Unable to open database " + databaseToSearch(x,0)
If Not websiteDB.IsOpen Then Error 10000, "Unable to open database " + databaseToSearch(x,0)
Print "Finding websites for " + databaseToSearch(x,0) + " lenders..."
'check whether the database is FT Indexed
If websiteDB.IsFTIndexed Then
'26/06/2008 (PJ) - don't update the FT Index, it should do it by itself. Calling this will corrupt the index if Notes is already doing this by itself.
'Call websiteDB.UpdateFTIndex(False)
Else
Print "Full Text Indexing the " + websiteDB.Title +" for searching..."
Call websiteDB.Createftindex(4, False)
Print "Full Text Indexing the " + websiteDB.Title +" for searching...done"
End If 'ft index check
'build the Search - we want the correct form, without conflicts, and with the website filled out!
queryString = |[Form] = | + databaseToSearch(x,2) + " and [" + databaseToSearch(x,3) + |] = http*|
Set webSiteCollection = websiteDB.FTSearch(queryString, 0)
'Set websiteCollection = websiteDB.Search(|Form = "| + databaseToSearch(x,2) + |" & !@IsAvailable($Conflict) & | +databaseToSearch(x,3) + |<>""| , Nothing, 0)
'*****CODE CRAWLS HERE
Set websiteDoc = websiteCollection.GetfirstDocument
While Not websiteDoc Is Nothing
'loop through the documents and add the lender's name and the website to our array
If websitesFound = 0 Then
Redim websites(0)
Else
Redim Preserve websites(0 To websitesFound)
End If
'set the array value to Lender (Source type, e.g. commercial) | website url
If Not websiteDoc.HasItem("$Conflict") Then
websites(websitesFound) = websiteDoc.GetItemValue(databaseToSearch(x,4))(0) + " ("+ databaseToSearch(x,0)+ ")|" + websiteDoc.GetItemValue(databaseToSearch(x,3))(0)
websitesFound = websitesFound + 1
End If 'conflict checking
'grab the next document
'*****CODE CRAWLS HERE
Set websiteDoc = websiteCollection.GetNextDocument(websiteDoc)
Wend
Next
Set tmpDocument = session.CurrentDatabase.createDocument
'copy the array onto the temp document
tmpDocument.websites = websites
Call workspace.DialogBox( "PromptLenderWebsites" , True , True , True, True , True , False , "Lender Websites" , tmpDocument , True , True , True)
Exit Sub
'##############################
'ERROR HANDLING!
'##############################
errHandling:
'Log the error
currentSubName = Lsi_info(2)
Call MainMenuErrorHandling (currentSubName, errorText)
End Sub