Factory Tour DQL Performance Explanation

Posted by

The Andrews – Manby and Davis – presented the Domino AppDev work we’ve done and are doing for V11 and V12 this morning.  There were several slides on DQL, one of which cited some performance gains in V11:

The queries used to produced these numbers traversed at least 50K view entries each, with the high end being 400K.

It was hard to quickly present these numbers in context but rather than saying in a Boston dialect and accent “DQL is gonna be wicked fast!”, I can do so here.

For view-based query terms like:

Sales_person in (‘Trudi Ayton’, ‘Cole Henderson’, ‘Julie Delight’)

Date_origin >= @dt(‘2019-01-01’) and date_origin < @dt(‘2019-07-02T12:00:00.000’)

‘Order_no’.order_no < 123221

we have sped up view traversal by 72%.  If a term took 10 seconds in V10, it will now take 2.8 seconds.  As announced, this speedup will likely come to all NIF index traversal in the V12 timeframe once we have “completed the thought” (which means expanding it to all relevant calls and contexts) and tested the snot out of it (where “snot” is a diplomatic term).

Now, even after that improvement, we have added Full Text searching, long known to be the fastest way to find a Lotus Note, let alone a set of them.  The 95% number applies to the 2.8 second result, so, a query term that took 10 seconds to service using a view in V10 will take around 0.14 seconds using the new CONTAINS operator in V11.  Yes, that fast.

Not all query terms are serviceable using the full text index.  As the operator “CONTAINS” indicates, it is meant to find terms within a long body of text.  Though, of course, if a field contains a short body of text, even a single word, it works fine there too.  What it cannot do is range terms (unless one uses wildcard suffixes on strings).  

Here is a DNUG slide I put together indicating what kinds of query terms can use which of the 3 strategies in V11 DQL:

I hope this brings a little clarity and sets expectations, because DQL is already wicked fast and getting faster.

Happy coding to all!

-John

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s