How does local view indexing work on the client?

HCL Domino Domino Forum
1

We are currently diagnosing an application that when first used, runs very slowly, and on subsequent uses runs approximately 16 times faster. We’re 99% sure it comes down to view indexes.

The application runs locally on the client and looks up data from another notes database. The lookup database primarily contains product information & logs of product changes. The database is udpated everyday, so there are document changes in the order of 400 docs per day. The database has roughly 120,000 documents, with 56,000 being logs, with the remaining 65,000 documents being data.

My question is, when a view is accessed via lotusscript (using notesdatabase.getView), which of the following happens?

  1. any documents modified or created (regardless of whether they meet views criteria) need to be assessed for eligibility for the view and the eligible documents added into the view index OR

  2. the view index already knows which documents are valid for the view and only updates the index with the details from the eligible documents.

What we are trying establish is whether the addition of the log documents to the database affects the performance of the lookup views - even if the lookup views don’t contain the logs. So if indexing worked as described as option 1 above, then the prescence of the logs would cause the application to slow down more than than if it worked as described in option 2.

Additionally, does the size of the individual documents affect performance, or is it simply the number of documents?

Although we know our solution is to move the log documents out of the lookup database altogether (the users don’t need tha data locally), it would be beneficial for future application development to know how it works. 90% of our user base work locally on offline replicas, so it would be great to know how the indexing worked!

Also, I have done some research on view optimisation, but there’s always the assumption that applications run on Domino Servers, rather than on local disconnected clients which I always find frustrating! (e.g. IBM Developer).

Thanks in advance

Phil

2

Subject: RE: How does local view indexing work on the client?

First, I want to congratulate you for only modifying the documents that actually needed to be modified. Too often I come across applications that dump all the documents nightly and create new ones from wherever they’re copied from, and that of course causes severe performance issues because of all the deletion stubs (as well as having all those documents to replicate and re-index).

When a local view is accessed, all documents modified since the view was last used must be re-evaluated to see whether they (still) belong in the view and whether (if they were already there) their column values have changed. This is different from what happens on a server, since by default the server keeps the indexes of recently-used views up to date even when they’re not in use. The client, however, doesn’t have a background indexing process, unless you write a local background agent to do this.

What you’re describing here, with a database containing roughly equal numbers of documents of different forms, is the perfect situation to take advantage of the database advanced option “Optimize document table map.” This option speeds up view indexing in this situation by keeping a table of which documents use which forms, and immediately removing from consideration for view indexing all documents that use the “wrong” form for the view in question.

That said, 400 updated documents is not a lot, particularly in a local database, and 120,000 documents isn’t an excessive number either, for unmodified documents. I have a database I use locally for performance testing with 100,000 documents, which contains a date-based view (@Today in the selection formula). With such a view, the index must be rebuilt from scratch each time it’s used, and it takes 4 or 5 seconds. So really, evaluating 400 documents against an existing index should not be a big deal, especially if most of them don’t end up in the view.

I wonder, therefore, whether maybe your view is an unusually expensive one to evaluate. Does it contain a lot of columns, or any very complex column or selection formulas? Are there lots of re-sort columns in it? Hint: for a lookup view, there should be no re-sort columns because it’s generally best not to use a view that’s visible to users.

It’s also worth noting, that it’s possible to set replication formulas in local replicas programatically to exclude the documents you didn’t want. That should improve performance without your needing to refactor your whole design.

3

Subject: RE: How does local view indexing work on the client?

Hi Andre - thanks for your quick and thorough response!

So it was as I suspected that regardless of form, the documents need processing into the view index. The “Optimize document table map” is just the ticket by the sound of it!

Re: the content of the views. We were going to use the hints provided on the link I included to go through each view to ensure we didn’t have features enabled that would slow it down. Particularly column sorting, click to sort, @today etc. I think it will take us a little time to go through and find all of the views, the application has some 500,000+ words of lotusscript in it (we were proud of it and copied the code into word just to see count after a year of development!)

Re: the replication formulas. I had a crack at programatically setting rep formulas locally and found:

  1. it was really fiddly - I can’t recall the details right now, but I vaguely remember that server names were an issue.

  2. you had to have a local replica to start with before you could apply a formula to it (which meant downloading a full replica and THEN applying the formula to it - what we were using it for was to reduce the initial and ongoing replication, so this was ultimately not a complete solution because you had to download databases to start with anyway). Note: that was all with lotusscript. I know you can download them manually by specifying a formula, but we were working on an automated install.

So I’d be interested to know how you could rollout a replication formula.

Cheers,

Phil

4

Subject: RE: How does local view indexing work on the client?

So I’d be interested to know how you could rollout a replication formula.

I’ll have to write a Designer Wiki entry on it someday. Not today.

5

Subject: RE: How does local view indexing work on the client?

Fair call :slight_smile:

6

Subject: RE: How does local view indexing work on the client?

The client, however, doesn’t have a background indexing process, unless you write a local background agent to do this.

Hey, Andre!

Is it advisable to write this agent? Do you think it could improve performance? How can I measure that? Only by means of disk space and in terms of process and use of memory at the client?

7

Subject: RE: How does local view indexing work on the client?

I think it’s an occasionally useful thing to do, but only when there’s a specific view that causes a problem, as in this case. It also depends on the users’ work patterns. If they tend to replicate their databases and them immediately start using them, the background agent probably wouldn’t have time to run before they accessed the view anyway.

Plus, there’s the problem of making sure all users allow background agents to run on their workstations.

I’d save this sort of thing for after you’ve looked into why a view is slow and tried other techniques for speeding it up, such as the database option I mentioned and simplification of formulas.

8

Subject: RE: How does local view indexing work on the client?

This whole issue has now triggered us to review every view that is accessed by this application. Here’s a handy trick I thought of 2 days ago that may help people who have a similar problem. We downloaded a new replica of the database used as the data source. We then ran our application (which triggered all of the lookups). We then used the Admin Client, (through the file tab looking at our local machine), and used the “Manage views” function to see all of the views that had been built. More importantly this allowed us to see which views were “massive”. We could then examine the views for the following practices that go against the performance tips. What we then looked for in these views were:- lookup views that had categories (which are not necessary as standard sorting will do the same job without the category overhead)

  • any columns with “Click to sort”

  • any columns that were unnecessarily sorted

  • columns or selection formulas containing @Now or @Today.

Before coming up with the “Manage views” trick, we had been doing it the the hard way after going through 1000’s of lines of code finding every instance of getdocumentsbyKey and then reviewing the views that way. I thought it a good idea to share this!

Something also worth considering is your naming conventions. Fortunately we had all of lookup views labelled as such, so identifying these views as opposed to UI views was easy. We’ve been doing this for years out of habit but it really re-inforced why it’s such a good idea. We knew we could remove the click to sort and categories straight away as we knew these views weren’t being used in the UI by our users.

We also discovered through this methodology that our databases “default” view was huge and we have built an alternative default view which has nothing in it.

We have adjusted the database features for peak performance by turning on “don’t maintain unread marks”, turning on “optimize document table map”, turning on “don’t overwrite free space” and turning on “Don’t maintain Last Accessed property”.

Today we are testing the performance against a baseline. It will be interesting to see what impact all of these changes have.

Cheers

Phil

9

Subject: Please, don’t forget to post your results! :slight_smile: