Think about a big database with lots of documents in it and you want to find only one particular document. You can do that with FTSearch, or you can use a db.search with some formula.
As of V10.0.x, you also have DQL and the new NotesDominoQuery class.
The class is available in Lotsscript and Java.
I want to demonstrate in this sample, how you can find the needle in the haystack with DQL in Lotusscript.
My database has about 12.500.000 Documents. It is one of our customers database at midpoints. The amount of documents was created by accident. Some call it a bug. Anyway, the database is a good playground.
The code is typical for a LS developer. It initiates objects and stuff, assigns variables like our dqlTerm (line 6 see the similarity to the @formula, you would probably use with db.search? ), does a check, if the target database is open (line 9 ) and also has some basic error handling (17, 20-21). We will come to that later.
I am running the sample on the client. As of today , you cannot run a query client / server. I will show in another post, how you can run the query on the server and work with the results on the client. But that is another story.
public Sub foo() Dim session As New NotesSession Dim db As NotesDatabase Dim col As NotesDocumentCollection Dim dqlTerm As String dqlTerm = "form = 'frm.rules.device.rule' And rule_unid = '99A242AAB69B5BB9C1257FFC005DE6C4'" Set db = session.Getdatabase("","trul-big.nsf", False) If db.Isopen Then Dim dql As NOTESDOMINOQUERY Set dql = db.CreateDominoQuery() Dim parse_result As String parse_result = dql.parse(dqlTerm) If LCase(parse_result) = "success" Then Set col = dql.Execute(dqlTerm) MsgBox dql.Explain(dqlTerm) Else MsgBox parse_result End If End If End Sub
Before you run the code and try to search such a huge amount of data, you need to set some notes.ini variables.
By now, there is no other way to increase the number of documents. There are setters in the NotesDominoQuery class, but those setters are broken. This is a known issue and HCL is working on a solution.
When we now run the code, we will get the following result
It took 51.3 secs to find 110642 documents that use the form “frm.rules.device.rule” and zero to none secs to find 1 document in that resultset.
Would it be faster or slower, if we modify our query to use only the rule_unid field and search over the entire set of documents?
dqlTerm = "rule_unid = '99A242AAB69B5BB9C1257FFC005DE6C4'"
Here is the result
32.2 secs to find the needle in the haystack. Use this kind of single field search, if you are sure that the field “rule_unid” is only used on one form.
But even in the case that the field is used on another form; to filter the resulting NotesDocument collection afterwards is much faster than the AND dqlTerm from the original code.
Maybe, you already have a view in your application where the rule_unid is in a sorted column.
Then we can use a modified dqlTerm to find the document in that view.
Again, let’s change our dqlTerm a little bit
dqlTerm = "'all'.rule_unid = '99A242AAB69B5BB9C1257FFC005DE6C4'"
And here is the result
THAT is pretty cool, isn’t it? 5.6 msecs to find the needle in the haystack.
Can it be even faster? I think no, but let us take another look at the code that does the query.
Dim parse_result As String parse_result = dql.parse(dqlTerm) If LCase(parse_result) = "success" Then Set col = dql.Execute(dqlTerm) MsgBox dql.Explain(dqlTerm) Else MsgBox parse_result End If
We can safely remove all of our “Error handling”, that means lines 14-17 and 20-22. Why? Because dql.Excecute(dqlTerm) does the parse before executing the query.
So we end up with the following code
public Sub foo() Dim session As New NotesSession Dim db As NotesDatabase Dim col As NotesDocumentCollection Dim dqlTerm As String dqlTerm = "'all'.rule_unid = '99A242AAB69B5BB9C1257FFC005DE6C4'" Set db = session.Getdatabase("","trul-big.nsf", False) If db.Isopen Then Dim dql As NOTESDOMINOQUERY Set dql = db.CreateDominoQuery() Set col = dql.Execute(dqlTerm) MsgBox dql.Explain(dqlTerm) End If End Sub
When we run the code, we get
Keep in mind that the numbers vary a litte bit from run to run.
If we now modify our dqlTerm and add an error, we are shown a nice dialog box explaining the error in detail.
That’s all for today, I hope you find this information useful.
12 thoughts on “NotesDominoQuery – Find the needle in the haystack (LS)”
Did you happen to do an equivalent formula language search? As I understand it, DQL can do some things that formula language probably can’t, but this search should be easily reproducible in an @formula. I am just curious how the performance results compare. (I’m making a demo where DQL is called from an event raised by Midas while creating JSON, so it might get called a lot of times, at least 50K separate calls.)
Thank you very much to shed some light on this.
If one is able to use the unid of a doc to search, how fast is a db.getdocumentbyunid in comparison?
@Christian I do not have the exact msecs, but db.getDocumentByKey is the same as DQL. In my test code, the “found” msgbox is displayed almost immediately after starting the search.
Also db.Search is comparable to the DQL numbers. As @BEN said. All samples can be done in the traditional way.
I think the strength of DQL is to build complex but readable queries in the first place. (flexibility).
Doing this with @formula or LS could become PITA at some point.
I really need to find a real world sample that demonstrates this.
When executing the note-api method Database.createDominoQuery in Lotus Domino 10.0.1 under linux, i get runtime-error “java.lang.UnsatisfiedLinkError: lotus/domino/local/Database.NcreateDQuery()J)”. The same in Lotus Domino 10.0.1 under windows works without errors. What could be the reason?
What is the exact Linux version, you are using?
Looks like an unsupported Linux to me. Or something went wrong during installation and some symbolic links are not set.
CentOS Linux release 7.6.1810. Lotus Domino has been updated from version 9.0.1 to 10.0.1 without any errors/warnings. Lotus Domino work fine. All other old/standard (from version 9.0.1) java classes/methods also work fine (e.g Database.getDocumentByUNID, getView, …).
Do you have experience using DQL from notes-api java environment (or from XPages) in Domino on Linux ?
I can confirm the error even for RHEL
Will try to get an update from the developers. You should open a PMR with HCL/IBM
Ok. Thanks for confirm.
It looks like liblsxbe.so is wrong version and not updated to 10.0.1 when upgrading from 9.0.x.
I will check tomorrow. My RHEL was upgraded too, but I also have a Linux server with a fresh install of Community Server 10.0.1.
By the way. The agent works on Domino 10.0.1 on Windows.
I already checked on CentOS 7.6 with fresh Lotus Domino 10.0.1 and got the same problem. I also compared the size of all *.so libraries in upgraded and fresh Lotus Domino 10.0.1 – their size was the same.
Double checked with an upgraded Domino as well with a “fresh” install. Same issue in both cases.
According to HCL, the “have not seen this yet”
Created case #TS001863705
Perhaps you can also open a case and refer to the existing case number.
Talked to the developer. A software defect has been found. This will hopefully be fixed in FP1.
Comments are closed.