I suspect that Xpages (new design element coming to Notes 8.5) is where this type of thing will be implemented. Check out Andrew Pollack's article:
{ Link }

I think what your are really asking for is the possibility to do all kinds of dynamic stuff in a view column, like getting a document based on a key from the current document and obtain some field-value from that second document.

I know that there will be a performance penalty.

I actually added such capability back in R3 with my old @YourCommand Toolkit, but I am not so sure IBM should be spending time on this now.

I have never seen a notes DB2 database. How does the view behave when the row is built from more than document? When you open the row - which document is opened?

@4, it's based on which document is the source of the data in the noteid column (if any) of the result set.

@1 - Yes, XPages will be a good start towards that end, IF it ever makes it into the notes client ... we still do stuff in the notes client right? And "semi-static" page display isn't the same as a view ... there's some power there for the user and the UI that I don't think XPages is going to be competeing for.

@2 - Actually, what i'm asking for is just to be able to join two records and dislay them as a single line in a view. It will reduce field overhead as we can stop putting inherited fields on response documents just to show data for the item in a column, I can make much cleaner, easier to understand and more powerful interfaces when you can link the data together. "on-the-fly" Data analysis within views can become much more powerful, and more meaningful views can be created when we can link records together.

@3 - I'm all for progress in other areas of the notes platform, but to most organizations and users the view interface is still the number one location where people interact with a notes application. We haven't had any great groundbreaking capabilities added to the basic notes view in quite a while, and some huge steps forward here could really help boost the end user productivity, which is what this is all about, collaboration, etc ... to boost productivity ... So if not now, when ?

@6 -- but at the cost of doing the equivalent of, say, 10000 @DbLookups when opening a view? That's essentially what you are asking for. I'd be more attuned to UIs like Chris Blatnik's "anti-view" approach to document location (which doesn't require waiting for Notes client XPages).

I'd hate to think of the performance hit on some of the uglier Notes apps I've maintained if somebody got ahold of an NSF-native JOIN.

@7 - To an extent, i agree with the idea of a "viewless" UI ... in that views that are simply lists of documents sorted by the field of the month given to the users as the means of "document location" is bad UI design.

But, if you follow me here for a second, i think you can see that having this ability actually leads to a cleaner, more friendly user interface, where the views that are joined are used more for data analysis and the like rather than hunt and pick views.

For example I have an application that allows the user to create a budget for a project. They can setup income and expense categories, plan what they are going to spend where, etc. Later, as they start to work the project, they can spend money, and apply it to the particular budget category. I then give the user three views that show them their budget, budget vs actual, and a profit/loss. In the budget vs actual, the view becomes a complex thing to read because, for control and other purposes, the budget amount is on one document, and the expense on another. In the view, I want them to see on one line, what the budget was, what the expense was, and if they were over/under budget. To do this, you have to have 3 view lines, a categorized view line to put the numbers on a single line, and then one line each for the budget and actual. If we had the join capability, its one line, the budget and the expense joined, and no need for the category. A much cleaner UI.

I'm sure the next question could be "well what about doing that as a report?" Reports are really a BAD ui design concept when you are actively working an application. Take the same example from above; If the users wants to "work their budget" and they drill down to an budget record and change the budget, do you want to have to force them to rebuild the report to see the change? No.

Yes there will somewhat be a performance hit more than a normal view, but its not the equivelant of 10,000 DB lookups, thats not how a JOIN works. It operates on two indexes and an equality statement. An index on the left is built, and an index on the right is built, and then they are line by line compared based on the left or the right based on the type of the join. On the surface that looks like what you are saying, a dblookup for every record, but its really not. What do you think takes the time in the dblookup? Opening and retrieving and positioning within the index to get the data from it, not actually retrieving the data. Thats only done one (extra) time in a JOIN. After the indexes for each side are built, its just incremental stepping thru the indexes to evaulate the equality condition. SQL does it already, and does it well, we can already do it with DB2, and it can be done with hundreds of thousands of records, so its not outside the realm of capability. And its not because DB2 is a faster engine. You don't get more performance out of DB2, it get more scalability.

Finally, there are always going to be bad designs, bad applications, etc as long as notes is as open and easy to develop a bad design, and for the platform, thats not necissarily a bad thing. But it also doesnt mean we should limit our capabilities and power to the lowest common denominator of application design, then all we get badly designed apps. Decisions on when and where to use these elements that do have a higher "operating cost" to the application are trade off decisions that need to be made by the developer when looking at cost vs benefits, and there are time, that the benefit outweighs the cost.

Hmmm... don't quite know what happened to my previous reply.

That is EXACTLY how a JOIN works. (How _did_ you think it worked? Data in one table is looked up in another table, and the appropriate columns of the matching row(s) are returned.) The only thing that makes a JOIN practical in a relational database is the fact that the data live in known locations in tabular structures. While there are a few bits of data stored at known locations in a notes document, the vast majority of the data in most notes is of unknown size and unknown dimension, stored in variable locations (items may or may not be present, the content of the items are typed by their data, and the order of items in the document structure is not fixed). The DB2NSF store is a different critter. The element in Notes that lends tabular structure to the data is, oddly enough, a view. And now we're back to @DbLookup.

I think I have two issues with what you are proposing...

1. A dblookup is not an apple to apples comparison of what would happen during a JOINed view index build process.

2. What you describe as what happens during a JOIN process, while on the surface seems correct, it isn't


Issue #1

Consider the steps required to do a DBLookup. (for simplicity in this, lets always assume a 1 to 1 ratio of key to document)

1. Retrieve the handle to a view index
2. Load the index
3. Retreive the first index entry into the 'current index'
4. compare the key of the current index entry to the requested key, if they match, go to step 6
5. Retrieve the next index entry into the 'current index', go to step 4
6. If the requested value is a column number or UNID, retrieve the value from the index (not the document), goto step 10
7. The value is not in the index, load the document
8. Return value value from the document
9. Drop the document
10. Drop the index
11. return the value
12. end


In your thought that this is what would have to happen in a JOIN, you would have to repeat these steps, 10,000 times for each record, each time loading the index, repeatedly positioning the 'current index' to the requested key value, then dropping the index. This loading, extraneous positioning and dropping of the index would not occour during a JOIN... Which leads me to my

Issue #2

You propose that in a join, data for the right of the join is "looked up" for each record on the left, but What actually happens during a join is the comparison of two indexes, either pre-established or built on the fly. And this is what the proper performance comparison should be against, the build of an index that has the same document selection criteria as the joined view, just not joined..

To build the join, here's what would happen. (lets assume a left inner join)

1. A note id collection would be created of the notes that matched the left selection criteria. (partially already done once in the non-joined view index build process)
2. A Temporary index is built with one sorted column who's value is that of the result of the left equality statement.
3. A note id collection would be created of the notes that matched the right selection criteria (again partially already done once in the non-joined view index build process)
4. A Second Temporary index is built with one sorted column who's value is the result of the right of the equality statement.
5. Retrieve the first index entry in both the left and the right temp indexes
6. if the left index value equals the right, an index is added to a third permanent index that represents the view, if not go to 9
7. Column formulas are evaluated and the view entry is built. with the added overhead that any value in a column formula not found in the left document is checked for existance in the right document, unless the value is explicitly declared from the left or right using the new @JoinLeft or @JoinRight forumla
8. The right index current index position is incremented
9. if the right index value is less than the left, increment the right position until it is equal to or greater than the left, go to 6 until you run out of left indexes


So normally a view would build the note id collection, and evaluate each document in the collection against the view to build the index.

A Joined view has to additionally build two temporary small indexes, then compare them and build the permanent index.

The result is that you would have a view index that takes somewhere around (guessing slightly based on the above) 3 or 4 times longer to build the joined view index than it would to build the equivalent view without the join...

Thats a number you can compare against and decide if the cost of the joined view is outweighed by the benefit of what you are trying to accomplish. To throw out a number like 10,000 dblookups is incorrect, misleading, and overblown to make a point with lots of zeros :)

just wanted to clarify:

"repeat these steps, 10,000 times for each record" = "repeat these steps 10,000 times, once for each record"

and step 9 of the JOINed view build should also increment the left index before going to step 6

10,000 view entries is hardly unreasonable (and may be considered conservative) for a production database, and JOINing on an existing view (at least for the right view) is the most efficient way to go about things. Now the only thing different between individually-called @DbLookups and a LEFT OUTER JOIN (the smallest/quickest lookup JOIN in SQL and the JOIN-type most likely to see use in Notes) is that getting and releasing the index can be done once per indexing run. That may make indexing a JOINed view appreciably faster than individual @DbLookup calls, but there is still a non-zero lookup cost for every view entry in the left table (essentially the same as using a multivalue key in an @DbLookup versus doing individual @DbLookups in an @For loop). It will still be godawful slow in a typical Notes application since we can't rely on the kind of pointer math that SQL uses on database tables -- and many, many times slower than a real-time query and join on the working document set that a user actually needs at any given time.

I do not disagree that there is going to be a performance hit, how much and where the hit occours is really a pointless argument, as I am sure the Notes Development team can figure out some cool way to accomplish it, and there are a lot of points here on both sides that will impact performance positively or negatively that we aren't even discussing.

The idea itself is solid, would be hugely benneficial. There are existing features and capabilities in lotus notes application development that have performance costs. The whole idea is to weigh the costs vs the benefits, like any other application design decision.

I really like this idea as well, but what if the use of a JOIN could only be used on indexed views and the columns available in those views (not the documents themseleves). In otherwords, the views act like SQL tables - rigid items of data and even data sizes possibly. Perhaps it would require a new view "Type" that allows for views specifically for JOINs to another view. Then the hit may not be too bad as the indexes are maintained by the JOIN views. Just a thought.

@4 If the Query View is in the same database as the documents, then the #NoteID you select to return is used to open the document. In the SQL command you select which fields from which documents are returned. It's like having a view container and you have controll over what it's filled with. This is more like a web view where you can build the link to be what you want and not driven by Notes.

@14 has the right idea.

View-based lookups are FAST. Relational-database fast. As long as you don't have to "crack open" the document, it should be possible.