This forum is closed to new posts and responses. New discussions are now taking place in the IBM Developer Answers forum.

Dec 23, 2017, 8:05 AM
4 Posts

Lotus view performance boost

  • Category: Performance
  • Platform: Windows
  • Release: 9.0.1
  • Role: Administrator,Developer,End User
  • Tags: DB2 views index
  • Replies: 6

I have one application with more than 3 million documents and more than 100 views and i have performance problems, when update or update all is indexed the views the whole application hangs, users are unhappy. I read about DB2 Access Views but also i read that is not more supported with lotus domino 9.0.1. I need a view boost performance (relational database style). A database split and reduce document numbers is not an option. Lotus is great but with many documents is a nightmare, is very slow.

I am waiting for a solution.

Dec 24, 2017, 3:41 PM
21 Posts
Possible options

Consider the following workarounds:

- Splitting the data into different views. For example try to use a view for each Year (if the documents are created over a range of years), or a similar way of splitting the data.

- If the view is categorized, consider moving some categories to new views.

- Also consider simplifying the View's design. Reduce the number of columns, and simplify the formulae. If too many formulae are getting computed, consider storing the result inside individual documents. As the saying goes: you can reuse space, but you cannot reuse time.

- And if you're using any formula that involves @Now, @Today, @Tomorrow, get rid of it. This one thing kills the server.

- Reschedule the Updall task to run at off hours.

Dec 27, 2017, 1:19 PM
4 Posts
Waiting for more solutions

I have only 9.0.1 FP7, indexes out of the NSF is from FP8.

The updall task is working hourly and i need this for searching the documents.

@Now, @Today, @Tomorrow are not in my views.

View indexes is my big problem, are performance bottleneck

Dec 28, 2017, 5:09 AM
105 Posts
Server Performance

As a redesign of the database is not on the cards, you should look at the server performance as a whole as there is no magic "go faster" notes.ini setting.

In my experience the best bang for your buck will be moving to fast disks (maybe dedicate an SSD just for your app).  Like Carl has suggested you can also move the view indexes out of the .nsf to a dedicated disk too.

There is an option in Domino 9.0.1 Fix Pack 3 to have a dedicated view indexer for a database which could help if you know which view is taking the time to index:

Jan 3, 2018, 12:59 PM
362 Posts
ok, some ideas.

It doesn't really sound like a performance problem. Seriously, it doesn't. It'll probably be much improved by doing less, than by forcing the server to do more.

That's a responsiveness problem.

Here's what I hear you're doing. "Some people are reporting that it's taking a long time to open up a view. In response, I've decided to force 100 views to be updated before they can open the view they want to open."

You see, view updates are serialized in one database. You don't get to update 2 views at once. You simply don't.

So if you're "updall"ing 100 views every hour -- you're forcing everyone to wait for every view to be updated. Every hour. Your views don't open fast. It's 100x worse if you're updating 100x the views. That's no improvement.

That's what's happening. It's counterproductive to what you say you want. So don't do it.

So were it my first action to take, I'd simply stop updating all the views at once.

It's likely happening automatically, too (that is, Updall is not the only culprit). I would go to advanced options on the database properties and Disable automatic updating of views. This is the server's way of doing ... well, mostly what Updall is doing, only automatically. Shut it off.

Next thing, shut off all the updall attempts, or if you're updating the FT index quit forcibly refreshing all the views. They will delay someone opening one view, until all views are up to date. Don't do that. Qualify the command so you're only updating full text indexes, with the "-F" option. It should be something akin to: "Load updall dbpath -F -M".

If you're updating FT indexes I would also recommend updating the FT indexes in a separate thread, to really free-up the view update: UPDATE_FULLTEXT_THREAD=1. You can also set memory outside the standard Domino pool: FTG_USE_SYS_MEMORY=1. These free-up some other system-level delays in view updates.

I wouldn't expect the Notes.INI settings to improve things as dramatically as shutting off the autoupdates to all the views. But they may get you a percentage improvement.

Now Check Your Design:

Yeah. You can play with administrative settings that at most will get you 20,30% improvement. And I'm being optimistic here.

The proper designs will cut times by 70-90% instead.

Do you have 100 users, each with their own view? That's an exercise in futility. If you have user-specific views, then you need a redesign. Period. Examine or consider single-category views to replace user-personal views. You can stop here, if you have personal or completely user-specific views. Nothing else will help you unless you fix this. And single-category views can fix this. There will still be an annoying "shudder" when the view goes single-category. And you're restricted in the ability to use alternate-sorting on columns (take it from me, you can't). But just make more views instead.

Consider what's happening if you have 100 user-specific views. Every document must be selected and checked, 100 times, mostly excluded, but added to 1 of 100. With a single-category view the document is selected and checked, and probably added (which costs more, admittedly). But it's checked once. That's probably a 50:1 improvement v. updating 100 views. You could make 5 more views the same way and still run your app in 1/10 the time. And users will get the responsiveness of an application that only has 5 views.

That's how redesign works. It collapses the amount of work the server must perform. And your users get the benefit.

Btw, relational databases have this identical problem. Submit 1500 SQL statements instead of 1, and you'll have 700x the delay. Design the view the way you design the SQL. Both can be inefficient. Both can cause delays.

If you don't have 1 view/user, then you'll need to change your view designs to reduce the number of views. Users never use 100 views in a day, or even 5 views in a day. Each will probably use 1 to 3 views. And often they'll share use of those views with other people. Responsiveness is all about sharing views. There're two directions to take (at once) to improve responsiveness:

  1. optimize those views so they're faster, without disrupting the users, and at the same time,
  2. look for minor functions that other views supply, Supply those features in other, heavily-used views. When you combine view functions, you kill off the need to update multiple views. Then you can delete views.

You always have some delay when one user pops in looking at his chosen view. But you want to use less of his time for that view, as well as offering more features in the view so other delays are reduced. You want less view updates and less processing work to get to the data. Not more.

The easiest way to cut into load times for views is to do slimming things in them. However, this is not likely to shave more than 20% of your time off.

  • Categorized, non-single-category views should be opened with categories "collapse all on first open". Seriously, this helps a lot in opening views with lots of documents.
  • Response documents should be added in the select expression with "@AllDescendants", not with "@IsResponseDoc".
  • Views without response documents should deselect the option to "show response documents in a hierarchy".
  • Large fields should not be included in any view column formula.
  • Alternate sorts should not be created when first opening the view. It's a view column option when you set up alternate sorts on a column.
  • Framesets that use @DbLookup or @DbColumn cost you. If you're looking up documents to configure a sidebar or a frameset, you're opening more views and rummaging around in them. Consider using a profile for slightly better, and cached, access to configuration data.
  • Never -- Ever -- use the fully "Automatic" setting on a view. Your application has over a million docs. This is asking for updates every 5 minutes after a doc is updated instead of every hour. Once again, the "response vs. performance" issue raises its head.
  • FT indexing a million docs requires its own special handling. Don't make it immediate. I'm pretty sure you haven't, you're probably trying to refresh it hourly. But you'll need to reduce that work as well. Offload as much as possible to other memory, and only refresh the FT index -- not all the views.

But the real way to improve on delays opening the most commonly-opened views is to examine what's going on with them: why they take so long. Often it has something to do with the column formulas or selection formulas and what complicates them. Not much I can do without examining what's going on there. Sometimes you can slough off the @function to a field on a form, for instance. It can be a complicated response hierarchy, or a complicated categorization. It can be a lot of things.

But enhancing one view so you can delete another: this is a huge shot in the arm for your application. Because view updates are serialized, you'll discover that multiple people are waiting in one view while another view is updated, and only afterward is their view being processed. If that first view never existed, it simply strips away all that time they wait.

So if you can enhance one view in a large database to perform the functions of two views, response can be greatly improved.

Further afield, other tasks could also be sucking the life out of your application. Your next design options are:

  • Check whether the server has some other problem sucking up performance. I ran into a server that was constantly trying to contact a shutdown server, and then trying to contact itself. External server contacts delay the server. Overly-obsessive agents also delay operations on the server: do not schedule agents for less than 15 minutes. IBM normally recommends 30 minutes, so consider 15 minutes a hard stop delay.
  • Check whether your agents are updating a lot of documents they shouldn't. Agents are often poorly written. Write them so they don't re-save a document they haven't changed. Write them so they don't delete & re-create documents. Write the entire DB so you're not adding daily for each event that might occur: think about modifying documents to record more events, not creating new ones for each event.

Are there more system-server level actions you could take? Sure. Some will look amazingly good at first. But again, don't expect dramatic improvements, and expect them to disappear as your application gets even bigger. Structural suggestions to speed up the server are unlikely to solve your problem. It's a problem with the database or view design, or counterproductive attempts to solve a responsiveness problem.

That said, the new DB structure available in FP9 is better support for an old idea discussed and implemented long ago on Domino servers: moving view updates to faster drives.

The NIFNSF feature added to FP9 will give you performance improvements. Don't expect it to improve response more than a few 10s%, though.

If you can get a solid-state drive to run the updates, it'll be great at first. But SSDs have a finite lifespan, and your view updates will be rewritten a lot.

There's also another old method of marginally improving updates. Do not do this with your NIFNSF files or you'll destroy all your views every shutdown.

Your server, does it have a lot of RAM it doesn't use? Consider allocating a RAM drive 30% bigger than your largest collection. Then put the TEMP directory on the RAM drive. This is not the NIFNSF drive. And beware: if your RAM drive is too small & runs out of memory, Domino will cook that database, and it may not be easy to repair. Make backups you can use. Keep the RAM drive size properly maintained. You can obtain the size of views from Domino Administrator.

Background to this: I believe Domino collections are still updated where the TEMP storage resides, and then they're transferred to the NSF. So with RAM plentiful, you may be able to improve view speed generically. It'll be faster; it's not going to be fast enough to let you do silly things like user-specific views.

This forum is closed to new posts and responses. New discussions are now taking place in the IBM Developer Answers forum.