In a recent discussion thread in one of the forums on IBM developerWorks, the issue of which is faster, doc.GetItemValue() or viewentry.ColumnValues(). I decided to test this, using two Lotusscript agents, created to be as similar as possible, working against the same view of the same database.
First, here is Agent #1, using ColumnValues() to get the value we are looking for from the first column of the view. This agent took 66.3 seconds to run, and below you can see exactly how that time was spent:
And this is Agent #2, identical to Agent #1, except two lines, first getting the NotesDocument from the view entry, and then using GetItemValue() to get the value out of the document. This agent took 225.8 seconds to run:
In both agents, the call to get the next document in the ViewEntryCollection takes about 60 seconds. As you can see, the 30,000 calls to GetColumnValues() in agent #1 takes pretty much no time — 1.3 seconds — while it takes 133 seconds to open the 30,000 documents and read the value of the field from each one in agent #2. Almost exactly 100 times longer!
In agent 2, you also have to add 26 seconds to get the NotesDocument object from the ViewEntry object.
I hope this settles the discussion.
I think the performance profile on this may have changed in recent versions (i.e. you may be seeing the improved behavior here).
It’s also worth noting that if you use the syntax of @DbLookup to retrieve data from a “field name” as opposed to a column, you can actually use a column name. The formula engine will open the document to look for fields (which takes longer) only if there’s no matching column.
If a column just displays a field, the column is automatically named after the field. So you can use the field name in such cases without sacrificing performance, and not have your code be as vulnerable to changes in the design of the view, compared to hardcoding a column number. Even if the column is deleted, your code would still work — just slower.
Unfortunately, if there’s a way to take advantage of this to have more efficient LotusScript code that references the column by name, it’s not obvious how. Because columnvalues array only contains columns that are stored in the index (i.e. non-constant values), it’s hard to match them to the NotesViewColumn objects contained in the view.
I forgot to mention the version used in the test I made, it was Notes 8.5.3.
I knew about using column names in @DbLookup, even if I actually rarely use it. But you are right, it makes the application easier to maintain when you can actually insert columns and not break lookups.
@DbColumn is waaay faster.