On 13-May-2016 16:41 -0500, dlclark wrote:
<<SNIP>> The paradigm shift for us was not a simple choice between
embedding an SQL statement in a program or embedding it in a view --
though we were given the impression that the same statement would
perform a bit better in a view.
A VIEW stores an Access Plan, so a SELECT * FROM THE_VIEW should
benefit from prior stored resolutions and optimization decisions;
similar to some of the benefits for embedded vs dynamic. And while the
Access Plan stored in the VIEW is for that specific SELECT statement [so
not a plan for generic statements referencing that VIEW], any embedded
statement referencing that VIEW as a table-reference is stored in the
program as an Access Plan of the *merged* statements. Thus a SELECT
from a VIEW in embedded, is little different than a SELECT that was
explicitly coded by the programmer as a merger of the VIEW SELECT and
their SELECT without actually naming the VIEW in a table-reference.
Rather, it was mostly about coming face-to-face with the realization
that the view is rendered at run time as opposed to a DDS-based LF
which is rendered at object creation time.
Not an entirely appropriate characterization\realization. The DDS LF
is more tightly\directly coupled with the physical data[spaces] and
physical files (PF), for which a keyed Access Path (ACCPTH) [or more]
may be created initially [and according to MAINT specifications, also
maintained since creation], but both the DDS LF and the SQL VIEW are
dynamically executed objects upon OPEN. The status messaging suggesting
effects like 'query running' are conspicuously indicative of a dynamic
effect, but lack of similar messaging opening the non-VIEW is not
indicative of the opposite effect.
Allusions of conspicuous benefits and /completeness/ of the DDS LF
since create-time is oft-stated, as contrasted with a VIEW, but is an
inaccurate implication; esp. notable, for both DDS Join Logical Files
(JLF) and use of Dynamic Selection (DYNSLT). The implication approaches
conflating the concept of DDS LF with that of the MQT.
Omitting the DYNSLT designation in the DDS LF merely ensures the
keyed Access Path (ACCPTH) omits\selects the rows since creation and
[typically, according to MAINT specifications] throughout maintenance.
That is no different however, than an SQL INDEX; now also capable of
including selection with a WHERE clause. Opening such a DDS keyed LF is
much the same as opening an SQL VIEW; the former opened with a keyed
access method has direct access to the key values to locate the RRN via
the keyed ACCPTH, or the former opened with the Arrival access method
has direct access to the underlying data[spaces] values. In either
case, after the open, the data must be read, either through the keyed
access path or the arrival access path; i.e. the path to the data
exists, the Open Data Path (ODP), but that is merely potential record
access, pending actual Read requests. In opening the VIEW, again the
ODP exists, but again that is merely potential row access, pending the
actual FETCH requests. What _is_ the big difference, is that the query
open has an optimizer that will review [and perhaps update] a stored
plan or create a new plan [of merged SQL statements] and use the fresh
plan to generate the ODP. The query access potentially has a plethora
of paths to the data [but with the overhead of an optimizer to make
those decisions], whereas the DDS LF has a fixed path to the data;
depending on the needs, either one could be a strikingly better access
method than the other, for which the fixed-path access method depends on
the programmer to be the optimizer. While the DDS LF is fixed to the
access path created\maintained, the SQL VIEW is free to pick any
existing keyed or sequential access path and even implement
multi-threaded or with other complex algorithms and other access methods
[e.g. index-only, EVI, etc.] that have the potential to give
astoundingly better\faster access as contrasted with using RLA from the
DDS LF.
Thus, a particular case that came out of the performance review was
that we had to change a particular view to an MQT in order to render
the many hundreds of thousand of rows of data from a 14-way join just
once per day (in a separate process) rather than 10,000+ times per
day in the middle of user's interactive jobs.
That describes a choice between VIEW and MQT. Unless the SQL 14-way
join was compared with a 14-way DDS JLF, that choice was not a
reflection on a decision being made between use of DDS LF and SQL VIEW;
i.e. seems incongruous with respect to the aforementioned /realization/.
midrange.com
