Dynamically sortable Lotus Notes view columns are trouble: Here’s how viewEZ can help you find and fix them

We recently posted a tip to help you figure out which views in a Lotus Notes databases are the least often used, so you’ll know which ones to cut if the application’s size needs some trimming (the fewer the views, the fewer the space-consuming view indexes). But after you’ve finished throwing all the dead weight views overboard, you might start to wonder, ‘what about those views that I just can’t throw away–is there anything I can do about them?’

Well, you might do well to start looking for non-essential parts of views that contribute to index bloat–you might be surprised by what you find. For this tip we’re going to focus on dealing with views with dynamically sortable columns.

Dynamically sortable columns–especially columns that allow both ascending and descending sorting–are nasty contributors to view index bloat. Many people aren’t aware that these innocent looking sorting options can have a devastating impact on both database size and performance. While they can certainly be helpful for end-users when intelligently implemented, the trouble with dynamically sortable columns is that they force the creation of an alternate index for each type sorting sequence supported

The innocent-looking setting highlighted above will lead to creation of two alternative view indexes. Are you sure you want to maintain three indexes for this view?

The Performance Considerations for Domino Applications Redbook summed it up pretty neatly by saying: “dynamic column sorting is a killer when it comes to both space usage and rebuild time.” (Though this Redbook was published way back in the year 2000, it’s still very much relevant and authoritative on this subject). If that’s not enough for you, the authors of this venerable tome really hammered home the point with this passage:

“If dynamic sorting is included on several columns, an index for each unique sequence is required. Before you know it, a view can suddenly grow to the point where index size far outweighs the data size. It is not uncommon for a database to be three to ten times larger with all indexes built compared to the size with no indexes. If you do need dynamic sorting, only use ascending or descending. Don’t allow both types of dynamic sort.”

So for this tip we’ll show you how to use Ytria viewEZ to quickly find all the dynamically sortable columns in an application, then mass-modify their properties to something more sensible. But first check out the two images below for an example of how your views and their columns shouldn’t look.

This many columns with ascending and descending (dynamic) sorting options is just asking for trouble

Yikes! Those are some bloated view indexes. The indexes take up more than 18 times the disk space of the database itself!

1) First open the database you’d like to look at in viewEZ.

2) Now, click the checkboxes for all the views and folders you’d like to analyze then click the Columns button.

3) The Columns dialog should now be open; now click the rightmost tab to see the ‘Columns by Properties‘ grid.

4) Now that we’re in the Columns by Properties screen we’ll need to add dynamic column sorting information to our grid (it’s not displayed by default). To do this, we just need to right-click anywhere inside the grid and select Grid Columns>Tab 2- Resort type in the contextual menu as pictured below

5) By doing this we added the information on dynamic sorting to the grid. Next we’ll want to drag the the Tab 2 – Resort type column header to the ‘grouping area’ (see the images below). This makes it easier to quickly see which columns have dynamic sorting options enabled.

Select the column header…

…then drag…

…and drop to the dark gray-colored grouping area

Here we can see which columns in our views and folders have dynamic sorting options available. We can see that there are quite a few dynamically re-sortable columns and what’s even worse–20 of these columns have both ascending and descending resort options available.

Now that we’ve identified the view columns with dynamic sorting options enabled, viewEZ makes it easy for us to mass-modify them.

1) We’ll just select the view columns we’d like to modify in the grid (in this case we’ll pick those that have both ascending and descending re-sort options) then right-click and choose Notes column properties from the contextual menu.

2) From here you’ll get a dialog very similar to what you’d see in Domino Designer (the main difference being that viewEZ allows you to edit many columns at once). Now, just click the Sorting tab and change the Column header to sort setting to ‘none’ (or whatever is appropriate in your case) and click Apply.

3) Now you just have to click the ‘Save‘ button in the lower right-hand side of the viewEZ Columns dialog to confirm your changes.  That’s it.

Now, after having cutting back on the use of dynamic sorting columns, and after all your views have been refreshed, you should see a significant reduction in view index sizes.