SQL views

An SQL view is a rowset which generates its rows from an SQL statement. DBMS supports various DDL (Schema update) statements and DML (Data update) statements.

Initially a view must be prepared, passing in parameters which describe the query and how the rowset should be evaluated. This takes the form of a pre-evaluation window, which gives the user program the ability to trade off memory usage for faster navigation, or an attempt to balance both of these resources. The desired window is described by the TDbWindow class.

A no pre-evaluation window means that the rowset is evaluated on the fly as it is navigated using the rowset cursor. This has the consequence that every cursor navigation has an unknown time cost.

With a full pre-evaluation window, the view maintains the complete set of rows following evaluation. Although this does not require a lot of memory per row, large rowsets require significantly more memory when fully evaluated.

A partial pre-evaluation window strikes a balance between a no pre-evaluation window and a full pre-evaluation window. This can be useful when local navigation in the rowset is important, and the rowset can also be, potentially, very large. This is done by hinting how many rows before and after the current row should be present in the window when the view is evaluated. As the current row changes, further evaluation changes what is in the window. This type of window is harder to program.

The query evaluator allows queries to be optimised and has a significant effect on database design and use:

• An ORDER BY clause can be evaluated without an index.

• A WHERE clause, which specifies a comparison, may be evaluated using an index, if a suitable one can be found, e.g. WHERE Id = 1234, when Id is an indexed column.

Both evaluation schemes require the full pre-evaluation of the query and, thus, a full pre-evaluation window. The optimiser provides such a view even when a client requests a no pre-evaluation window. In general, this means that if a no pre-evaluation window is specified on a call to RDbView::Prepare(), there is no guarantee that a no pre-evaluation window is used. In effect, the TDbWindow parameter to RDbView::Prepare() is a minimal specification to DBMS. DBMS may choose to use a full pre-evaluation window in order to evaluate the query faster, resulting in full pre-evaluation window behaviour for some queries, even when a no pre-evaluation window was requested.

Ordering behaviour is efficiently provided without using an index (both in terms of memory and performance); however, look-up can be extremely fast with an appropriate index.

For example, in the following SQL query, this evaluates faster if there is an index on the SupplierId column in the Parts table, and no index on Name.

SELECT * FROM Parts WHERE SupplierId=1234 ORDER BY Name