Mindoo Blog - Cutting edge technologies - About Java, Lotus Notes and iPhone

  • Advanced view lookup strategies with Domino JNA for small view index sizes and dynamic filtering and sorting

    Karsten Lehmann  March 13 2019 00:09:50
    This might be interesting for some of you, a pattern how I am using Domino JNA in a recent customer project to speed up view lookups and reduce overall view index size.

    I am using at least three views to produce the content for a data table in the web application:
    • one or more key lookup views
    • one view for the sorting
    • and the final one to read all required view columns.

    1. Key lookup views
    The key lookup views has the minimum required columns for the lookup, e.g. just the sorted columns containing the lookup key(s) and I use NotesCollection.getAllIdsByKey(EnumSet findFlags, Object... keys) to collect the note ids of all documents matching my lookup criteria(s) and the com.mindoo.domino.jna.utils.SetUtil class to AND/OR multiple note id sets.

    The first view might even contain less lookup columns (e.g. just the default index position column) if I am just interested in all documents that match the view selection formula. In that case I am using the view as a stored database search result.

    You can read the note ids of all documents in the view with NotesCollection.getAllIds(Navigate navigator, boolean filterTable, NotesIDTable idTable), which is EXTREMELY fast with parameters navigator=Navigate.NEXT and filterTable=false, as long as the flag "show response hierarchy" is NOT set in the view design. Then NIF just copies an internal index into the IDTable and is done (=>no b-tree traversion and reader list check for the current user).

    Unfortunately, AFAIK, "show response hierarchy" is set by default when you create new views. This lets NIF use a secondary index to search for responses for all view rows, which is slow.

    I first thought that this response hierarchy flag would be required to find conflict documents in the view (as they are response documents), but this is not the case. My tests have shown that this information is still there, although there was a "bug" in Domino JNA which declared each row as conflict because I was reading the conflict flag as it is documented in the C API toolkit.
    I fixed that recently with a special case for "show response hierarchy"==false (details: https://github.com/klehmann/domino-jna/commit/cdfbc6f8e3087eed1eb8328341451f4f0ffbc7dd).

    Additional note id sets could be retrieved from fulltext searches (NotesDatabase.ftSearch(String query, short limit, NotesIDTable filterIDTable)).

    With Domino 10, a DQL search could be used to collect the relevant note ids as well, in Domino JNA: NotesDatabase.query(DQLTerm query, EnumSet flags, int maxDocsScanned, int maxEntriesScanned, int maxMsecs).

    2. Sort view
    The sort view may be identical to one of the key lookup views. I am using this view to find all note ids of my key lookups that are visible in the requested page in the web datatable (=> offset / count received from the browser) and get them returned in view sorting.

    I call NotesCollection.select(Collection noteIds, boolean clearPrevSelection) with clearPrevSelection=true to select all relevant note ids in the view and then call
    NotesCollection.getAllEntries(final String startPosStr, int skipCount, EnumSet returnNav,int preloadEntryCount, EnumSet returnMask, ViewLookupCallback callback) with the following parameters:
    • startPosStr "0" => start at the beginning of the view
    • skipCount = offset+1 =>skip rows based on paging parameters received from browser, "+1" to go from row "0" (which is one row above the first row) to the first
    • returnNav = EnumSet.of(Navigate.NEXT_SELECTED)        => only return previously selected rows
    • preloadEntryCount        => count parameter from browser
    • returnMask = EnumSet.of(ReadMask.NOTEID)        => just read the note ids (in view sorting)
    • callback = a ViewLookupCallback implementation similar to the one I am using for the getAllIds methods internally (https://github.com/klehmann/domino-jna/blob/master/domino-jna/src/main/java/com/mindoo/domino/jna/NotesCollection.java#L996) which returns a LinkedHashSet with the note ids in view sorting, but stops after "count" entries.

    3. Data lookup view
    This "masterdata" view contains all the view columns required to fill the web datatable columns. It only has one fixed sorting (here: by creation date) and no resortable columns.
    As discussed before, "show response hierarchy" is not set here as well.

    Once again I call NotesCollection.select(Collection noteIds, boolean clearPrevSelection) with clearPrevSelection=true to set the view selection, but in this case I select just the note ids read from the sort view in step 2.
    So I read the data columns for all rows in the visible web datatable page, but as they are returned in the wrong sorting (creation date), I need to hash them by note id and reorder them based on the sort view sorting (not a problem, because my page size of 400 entries is quite small).

    To read the view rows, I use NotesCollection.getAllEntries(final String startPosStr, int skipCount, EnumSet returnNav,int preloadEntryCount, EnumSet returnMask, ViewLookupCallback callback) as in step 2, but this time I want to read the UNID and column values for the rows as well:
    • returnMask = EnumSet.of(ReadMask.NOTEID, ReadMask.SUMMARYVALUES, ReadMask.NOTEUNID)
    and get a List returned by the callback:
    • callback = new NotesCollection.EntriesAsListCallback(count)

    Benefits
    Sounds difficult? Yes, I agree, this still needs some convenience methods to make the code more readable.

    The benefit is that I only have one stable view with all the data, resulting in a large view index size and slow initial index time (here 800 MB for 300.000 documents).

    The key lookup and sort views are very small and fast to build (e.g. 60 MB each). So adding another lookup criteria or result sorting later is cheap, which would not be the case if I had to touch my data lookup view and add another resortable view column.

    And even though I collect data from three views, the lookup performance is very fast, almost instant.







    Comments

    1Mark Leusink  03/13/2019 9:36:48  Advanced view lookup strategies with Domino JNA for small view index sizes and dynamic filtering and sorting

    Great explanation.

    I use a similar approach (using JNA ;-) with 2 views: one for ID lookups and one to read the data for the lists. The approach you're taking with a third view is worth investigating.

    You're right that this way of working with Domino data is a lot more complex than what we used to do, but on the other side: the benefits are huge. This allows you to build solutions with Domino data that before never were possible!

    Cheers,

    Mark

    2Karsten Lehmann  03/13/2019 10:05:02  Advanced view lookup strategies with Domino JNA for small view index sizes and dynamic filtering and sorting

    The two view solution is good as well. We were facing some I/O performance issues on the customer's servers doing view rebuilds, that why I try to avoid a rebuild of the masterdata view as much as possible.