explain

The best way to understand how DQL syntax will be processed is by using the explain facility. Explain is available as a method in the DominoQuery class (Java) and NotesDominoQuery class (LotusScript) and with the DomQuery utility using the explain (-e) flag.

Assume the following query: 
date_origin >= @dt('2016-07-01) and date_origin < @dt('2017-01-01') and
	sales_person contains ('Trudi Ayton') and 'all'.ord_status = ('shipping') and order_origin = ‘Detroit’
The following graphic describes the explain output for the query:
Graphic depiction of explain output with descriptions

In this example, DQL processing treats all "sibling" AND Boolean operators as a single parent node and the individual terms its children. Each term is serviced one of full text searching, by view processing, or NSF scan operation. The number of Entries (views) or ScannedDocs (NSF scans) indicate the relative work done to satisfy the term. There are two millisecond timings, Prep and Exec, where Prep is the time it took to compile, validate and prepare the term and Exec the time to execute it. The FoundDocs value shows the total documents that passed the term’s conditions.

All counts and timings are rolled up into the parent node where the total cost of the query is displayed.

explain output gets more complicated when queries are more complicated. But these same tuning practices will be effective in optimizing performance:
  • Locate the most expensive terms using both timings and counts.
  • Consider using the contains operator, if possible.
  • If there is a full text index created and you can avoid using the time portion of timedate values and only use the date, do so.
  • For field terms, consider the overall usage of the field being used. If it commonly used in queries PLUS it is expensive, consider creating a view column with attributes specified above so the term can be optimized.
  • For view column terms, consider the width (how many columns) and the size of the view. If you can use a view with fewer columns and/or one with more restrictive selection criteria, consider that course of action.

In general, keeping even intermediate results as small as possible is a win. In that regard, ANDing terms together shrinks results while ORing terms together grows them. Of course, the application logic has its requirements, but the size of results should also be considered.