Brute Force View Tuning

Complex view selection formulas can slowdown your Domino performance substantially. So keeping them simple is a mandate for high performance applications. One of my favorite approaches here is to have a set of hidden fields in my forms that compute to true or false and then simply use SELECT vsf_Demo as selection formula, where " vfs" stands for "View Selection Formula". In large applications that results in a rather nice performance. Downside of the approach: you need to compute these values on each alteration of a document (but that's not too hard). You can use this method even to retrofit existing applications to improve performance. These are the steps:

Create a FORM with one field for each view that contains the view selection formula
Change the view selection formula (short of the SELECT @All ones) to point to that field
Alter/Create the QuerySave event to: switch to that form, do a computeWithForm, switch back to the original form
Create a script library with an easy function that takes a document and tells you if the document's selection formulas have been updated
Add a call to that function to all agents
Add an agent that runs on "documents created or changed" to catch any case where the two calls above didn't work (e.g. @Formula agents)
Test, Test, Test
Enjoy better performance

The code you need to add to the querySave event is rather light: while this is the code in the querySave event of any form:

Option Declare
Use "CoreHandlerLibrary"

Sub Querysave (Source As Notesuidocument, Continue As Variant )
Dim handler As DocumentHandler
Dim doc As NotesDocument
Set doc = Source. Document
Set handler = New DocumentHandler (doc )
'We recompute here, since we will save anyway we don't care for the result
Call handler. docHasChangedAfterUpdate ( )
End Sub

Being lazy and notoriously prone to typos I created some code that does step 1 and 2 for me in one go.

%REM
Class ViewTuner
Description: Creates a form with all view selection formulas and updates all views
%END REM
Public Class ViewTuner
Private ourForm As NotesDOMDocumentNode
Private formName As String
Private fieldNamesUsed List As String
Private resultStream As NotesStream
Private parser As NotesDOMParser
Private s As NotesSession

Public Sub New
formName = "ViewSelectionFormulas"
Set me. s = New NotesSession
End Sub

%REM
Property Set formToUse
Description: Overwrite the formName if necessary
%END REM
Public Property Set formToUse As String
me. formName = formToUse
End Property

%REM
Function getEmptyForm
Description: Get an empty form DOM in DXL
%END REM
Private Function getEmptyForm (formName As String ) As NotesDOMDocumentNode

Dim rawform As NotesStream
Dim s As New NotesSession
Set rawForm = s. Createstream ( )
Set resultStream = s. Createstream ( )

Call rawform. Writetext ( "<?xml version='1.0' encoding='utf-8'?>" )

Call rawform. Writetext ( "<form name='(" + formName + ")' alias='" + formName + "'>" )
Call rawform. Writetext ( "<body><richtext>" )
Call rawform. Writetext ( "<pardef id='1' hide='web mobile'/>" )
Call rawform. Writetext ( "<par def='1'><run><font color='red'/></run></par></richtext></body></subform>" )

rawform. Position = 0

Set parser = s. Createdomparser (rawform, resultStream )
Call parser. Process ( )

Set getEmptyForm = parser. Document

Call rawform. Close ( )

End Function

%REM
Sub addField
Description: Add a field to the form DOM
%END REM
Public Function addField (fieldName As String, fieldFormula As String ) As Boolean
If IsElement ( me. fieldNamesUsed (fieldName ) ) Then
'We have that field already, can't use it again
addField = False
Exit function
End If
Dim tmp As NotesDOMNodeList
Dim insertionpoint As NotesDOMNode
Dim newFieldElement As NotesDOMNode

'Lazy initialization
If me. ourForm Is Nothing Then
Set me. ourForm = me. getEmptyForm ( me. formName )
End If

Set tmp = me. ourForm. Getelementsbytagname ( "run" )
Set insertionpoint = tmp. Getitem ( 1 )
Set newFieldElement = me. getFieldElement (fieldname,fieldFormula )
Call insertionpoint. Appendchild (newFieldElement )
me. fieldNamesUsed (fieldName ) = fieldFormula
End Function

%REM
Function getFieldElement
Description: Generate the XML representing a field with a selection formula
%END REM
Private Function getFieldElement (fieldname As String,fieldFormula As String ) As NotesDOMNode
%REM
We need to create something like this:
<field type='number' kind='computed' name='vsf_DemoView'><code
event='defaultvalue'><formula>@If(Color="Blue";@True;@False)</formula></code></field>
%END REM
Dim result As NotesDOMElementNode
Dim code As NotesDOMElementNode
Dim formula As NotesDOMElementNode
Dim formulaContent As NotesDOMTextNode

Set result = me. ourForm. Createelementnode ( "field" )
Call result. Setattribute ( "type", "number" )
Call result. Setattribute ( "kind", "computed" )
Call result. Setattribute ( "name", fieldname )

Set code = me. ourForm. Createelementnode ( "code" )
Call code. Setattribute ( "event", "defaultvalue" )

Set formula = me. ourForm. Createelementnode ( "formula" )
Set formulaContent = me. ourForm. Createtextnode (fieldFormula )

Call formula. appendChild (formulaContent )
Call code. Appendchild (formula )
Call result. Appendchild (code )

Set getFieldElement = result
End Function

%REM
Function saveSubformIntoDatabase
Description: Saves the subform into the database specified
%END REM
Function saveSubformIntoDatabase (db As NotesDatabase ) As String
Dim importer As NotesDXLImporter

'Write the changes into the stream
Call me. parser. Serialize ( )
me. resultStream. Position = 0
Set importer = s. Createdxlimporter ( )

importer. Designimportoption = 6
Call importer. Setoutput (db )
Call importer. Setinput ( me. resultStream )

On Error Resume next
Call importer. Process ( )
If Err <> 0 Then
me. resultstream. Position = 0
MsgBox me. resultstream. Readtext ( )
End If

saveSubformIntoDatabase = importer. Log

End Function

End Class

For handling changed documents this agents comes in handy:

%REM
Created Jan 5, 2010 by Stephan H Wissel/Singapore/IBM
Description: Agent Sample how to update changed view selection formulas
%END REM
Option Public
Option Declare

Use "CoreHandlerLibrary"
Use "AgentSupport"
Sub Initialize ( )
Dim support As New AgentSupport

Dim doc As NotesDocument

Dim factory As New DH_Factory
Dim dh As DocumentHandler

Set doc = support. docToProcess ( )

Do Until doc Is Nothing
Set dh = factory. getDocumentHandler (Doc )
If dh. docHasChangedAfterUpdate ( ) Then
Call dh. save ( )
End If

Call support. Updateprocesseddoc (doc )
Set doc = support. docToProcess ( )
Loop

End Sub

You need the CoreHandlerLibrary and the AgentSupport As usual YMMV

Posted by Stephan H Wissel on 24 November 2011 | Comments (3) | categories: Show-N-Tell Thursday

posted by Nathan T. Freeman on Friday 25 November 2011 AD:
Why not just use folders?

posted by ursus on Friday 25 November 2011 AD:
Hi Stefan

Cool idea - love your implementation, well done.

I have inherited a couple of databases that are quite large (40 - 60 GB on an AS/400 or whatever its called this week :o) and they VERY slow. The reason for this is that every document has reader lists attached to it. I am having a difficult time convincing the users that they do not need them :o( There aren't TOO may views so I could apply your technique but was wondering if you could quantify how much of a speed advantage I would get? I would have about 100 DB's that I would use this on and would have to run the conversion agent by hand as I need to have "Full Access Admin" set!

Greetings from Austria
Ursus

PS: you at LotusPhere this year?

posted by Stephan H. Wissel on Friday 25 November 2011 AD:
In a Notes client app users can mess up by moving documents in an out of folders. So you need extra code to check for all documents, including the unchanged ones, that they are in all Folders they belong to and out any they are not supposed to be in.
Also audit gets much harder. Definitely nothing you want to do in a retrofitting exercise.
Emoticon smile.gif

stw