Re: AW: SQL views

Hi

I agree that index (access path) maintenance does not happen with views, hence, that performance hit does not exist. I look at views as essentially a SELECT statement encapsulated in the LF - and that statement is run each time you use the view - as a dynamic SELECT - so the optimization is where the cost comes, at run time. As with all SQL optimization, the impact of that time is less if you are retrieving a lot of records and more important if retrieving only 1 or 2 records.

Views are very convenient for the developer, and often give functionality unavailable any other way, as you said. And SQL lets you have sorts over any of the columns in a JOIN, unlike join logicals, which can have keys over only fields from the first file.

But we found at Centerfield, a few years ago, that views on views might (repeat - MIGHT) perform fairly poorly, since they often result in building temporary indexes - reference to existing indexes is often lost during optimization. This is likely being improved by the DB team at Rochester. Again, for long-running processes, probably no big deal; for record level access - relatively costly.

Bottom line - there is always a cost. Depends where you pay it.

Vern

At 03:17 PM 8/8/2007, you wrote:

Hi,

Yes, the optimizer will evaluate and may use access path built (SQL indexes
or DDS described logical files) over the physical files specified in the
view.
But a view must not be updated when the base tables are changed (contrary to
keyed DDS described files or SQL indexes).
That's why you can have thousands of views, without any performance impacts.


Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"



-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Nathan Andelin
Gesendet: Wednesday, August 08, 2007 22:06
An: Midrange Systems Technical Discussion
Betreff: Re: SQL views


>Birgitta H. wrote: Because a view has no access path, access path
>>maintenance is always rebuilt.

Doesn't the query engine use existing access paths of physical files
referenced through views, when available?

Nathan.






____________________________________________________________________________
________
Pinpoint customers who are looking for what you sell.
http://searchmarketing.yahoo.com/
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.





--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.