View Optimisation – A Cautionary Tale or When Children Can Help You

At the moment I’m reviewing some older databases to look for performance improvements. Some leap from the screen, like changing GetNthDocument for GetFirstDocument and GetNextDocument in script, removing unnecessary sorting, removing private views that had been created for support purposes.

Much of what I’ve been looking at is based on the whitepaper article by Andre Guirard which, if you’ve never come across it, is a must read. At the moment I’m going through views and comparing the view to the view index size on live. This is throwing up some interesting information. For example, I have one view that gets all documents based on two forms, so SELECT Form=”FormA” | Form = “FormB”. All documents of form B are children of documents of form A. And there is a virtually identical view (the formula for the response column is in italic and with the information in a slightly different order). This view has the selection formula SELECT Form=”FormA” | @AllChildren. Another article by Andre poses the hypothesis that in theory the latter should be better for performance than the former. How’s this for better performance: after some years of data, the view with @AllChildren has an index size of 22,400,256 kb; the view using form selection has an index size of 46,262,304 kb. That’s more than double the size. And before you pose the question of whether it’s because of orphan documents, it’s not: they’re regularly cleared down.

So why does @AllChildren have such a performance benefit? From Andre’s article, Domino uses an internal “response hierarchy” table, so it only needs to index the main documents and grab all responses as and when required. I guess it’s similar to the performance benefit of GetAllEntriesByKey to give a sorted collection compared to traversing all documents in a view, getting all matching documents and then sorting.

What this does throw into sharp focus is the benefit of response documents. For many years of Notes development I managed to avoid response documents, because I didn’t really understand them, I had to remember to deselect “Show response documents in hierarchy” for all the views I wanted to show them without their parents, and there was always the frustration of only being able to show a single column of data for each response. (Bear in mind that I also managed to survive for a couple of years developing on R5 without using LotusScript!)

With XPages, there is probably even more incentive to use response documents (when I take the time to get to grips with response documents in XPages). Like Domino, in XPages we can take advantage of the in-built “response hierarchy” table and can show as many columns as we want in a view. Not necessarily a View Container control, and I don’t think I’d approach it by using a Data Table Control. But imagine a repeat control generating a table row of data pulling from a view of your parent documents; then within the same repeat control, a second repeat control using as its source a NotesDocumentCollection of rowData.getDocument().getResponses(). And because we’re using repeat controls, we can populate the rows with whatever information is required. These are indeed interesting times.