| Data |
Defined in a database schema. Data is contained in tables. All records in a table are uniformly the same. Fields without values are there but empty. Different types of data require different tables. New data requires ALTER TABLE statements wich affects existing data (and often requires down time). Each column can have one value |
Data is contained in documents. Documents are schema free (there is a Meta Schema). Items in documents (the closest to a column in a table) can have multiple values. Each document can have a different set of items. New data is added as needed, no downtime or change of existing documents required |
| Views |
Selection of rows, often accompanied with a JOIN operation to denormalize normalized data. Can be all data of a table or a subset, picked by a WHERE part in the SELECT statement.
By definition views don't contain any data, but pull them ad hoc from the participating tables.
Various RDBMS systems use indexes on keys used in SQL statements to improve performance. The ad-hoc nature of the queries offers maximum flexibility at CPU and I/O cost.
There is a whole industry around SQL Query Optimization (and there is the whole story about SQL Injection attacks even by mothers) |
- are pre-created and updated by a view indexer task when documents are created or updated.
- They do occupy space inside an NSF (there are discussions in development to externalize them to reduce backup load and allow more I/O tuning).
- Since they don't require a document to be opened, they are fast (unless you use performance killers - see below)
- Each column in a view shows data. Using Domino designer that data can be different for each row (defined by
@Formula
- Since items can have multiple values, they can appear more than once in a view (controlled by the view design)
- Different document types (e.g. a customer entry and an order entry) can show up in the same view without the need for a JOIN operation (which anyway isn't a feature of a Notes view - this is one more reason why Notes favours inheritance over relation)
- Accessing a view using a
ViewNavigator is very fast
- A view can contain data that is computed and not contained in the documents. Typical uses are translations of status codes to human readable text)
|
| Hierarchy |
SQL Tables and views are flat by definition. Some SQL extensions (e.g. Oracle) allow hierarchical queries, but the result will be a flat query result. |
- views can be flat or hierarchical
- A common use for hierachical views is the option to categorize a column value. This operation will sort the column and show each value once only above the result rows (in the UI typically with a twistie icon)
- A categorized column also can perform some additional simple Reduce operations (albeit not distributed)
- Programmatic categorized views are typically used with
@DBLookup; @DBColumn; getAll[Documents|Entries]byKey
- Multiple columns can be categorized
- Typically the categorized columns are the first ones on the left, but that isn't necessary
- Subcategories can be created by having the item value containing a backslash. If an item contains more than one value, the document will appear in multiple categories. An alternative approach turns multiple values into subcategories:
@Implode(fieldname;"\\")
- Besides categorization there is a secondary hierarchy mechanism available: the response hierarchy. Defined by a view property, response documents (of all levels) are shown automatically below their respective main document. A special column can be defined that is shown only for response type documents (containing a valid
$REF item). In this case only the columns left of this are shared with main documents, columns right of it are ignored for responses and only the response column is shown (check the discussion template for how it looks)
|
| Sorting |
Sorting happens using an ORDER BY clause. For resorting a new select clause needs to be issued. RDBMS systems allow indexes to be created to improve sorting speed. |
- Sorting is a property of a view column. It will increase the view index size and thus I/O and reindexing time
- Sorting happens from left to right
- Sorted columns don't need to be visible in the view (there is a property to hide columns conditionally or in any case)
- Columns can be resorted in the UI when the respective property is set. The custom sorting sequence is also available as URL parameter
- A common design mistake (creating index data unused): make every column sorted, even if the sorting has no more effect on the result sequence and/or make every column resortable even if users might not use it. For the later case there is a remedy: set a column property to defer the creation of the alternate index until it is requested the first time
|
| Definition |
Views are defined in SQL using any editor of your choosing and then uploaded/executed in the database. Database admin rights are required |
Views are defined in Domino Designer or even the standard Notes client. Users can be allowed to create "private" views only visible to themselves, or at access level Editor views that can be shared. Columns are defined by picking item names from a list, predefined functions or writing @Formula a LISP like language (easy to learn, quite powerful to use). Brave souls have been spotted using DXL to write view definitions in XML and upload them into designer (but you don't want to do that) |
| Selection |
View entries are selected by WHERE in SQL |
- The selection statement also start with
SELECT, but uses the @Formula language for picking the documents
- All documents (typically in a view named ($All)) are selected by
SELECT @All
- More samples: all customer documents by
SELECT Form="Customer", incomplete workflows by SELECT @IsMember(Status;"Submitted":"In Progress")
- Overly complex select statements slow down view computation and you might need to employ some brute force view tuning
- A performance tip is to compute the criterion for selection inside the document to result in a field value
@True|@False and then limit the selection formula to SELECT showInView_Pending
|
| Performance killers |
There is nothing better to make your hardware seller more happy than code that violates performance considerations. In SQL the typical performance killer are OUTER JOINS especially over many large tables. Missing indexes are another cause |
Since Notes views contain actual data, they need to be treated a little different. There are a few killers around:
- Performance killer number one is the use of time based view selection formula:
@Now; @Today; @Yesterday; @Tomorrow. In one word DON'T. IBM isn't so strict, but I am. You do have options
- The second killer is the use of
@IsResponseDoc as part of a view selection when you end up with only a few parent documents, show documents in response hierarchy and many "hidden" responses since the parent isn't displayed. Better use @AllDecendants, check the explanation for more details
- As mentioned above: be stingy with sorting of columns, both presorted and optional. Make sure if you define optional sorting, that you defer creation until first use
- Overly complex view selection formulas and too many views slow down the view indexing process, so take only as much as you really need. In classic Notes we often find countless variations of views for display. In XPages you can often combine them (given they differ only in columns) and use the XPage for the variation in look and feel
- Last not least: your code. Let the view indexer do its job. If you use view.refresh() everywhere "as a precaution" you pay dearly with sucking performance. A view gets locked when reindexed, so 100 users all executing a view.refresh() lock the view 100 times sequentially
|
