× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



A SQL View is only a stored SELECT statement.
When accessing a view (or an complex SQL statement) it is analyzed and included in the SQL statement.
After having rewritten the optimization process starts.
First it will be checked if for the appropriate (rewritten) SQL statement an access plan (which was used in a former execution) exists.
If not exists the access plan will be created, if an access plan exists it will validated, by interviewing the current statistics and estimating the current access paths (SQL indexes - binary radix tree and encoded vector indexes, DDS described logical files and key constraints.
There are definitely only 2 ways to affect the query optimizer:
1. Creating the optimal access paths (indexes preferred)
2. The way how an SQL statement is written (e.g. with this syntax the optimizer may not be able to use an access path, with a different syntax, an access path can be used)
The most time consuming process (after the access plan is written or validated) is opening the access plan (ODP).
SQL tries to keep the ODP open as long as possible. Deleting and recreating the ODP (for example by modifying the library list without having qualified the accessed database object with the library), will slow down performance.

Creating and using an MQT may be an good idea for complex queries for statistical issues. Because an MQT is a table (physical file) which can be accessed directly and for which additional indexes can be built.
But an MQT must be refreshed/refilled manually (the automatic update is not yet realized), otherwise the data may not reflect the current data. When refreshing an MQT exclusive access is needed and the complete table is cleared and rebuilt. In this way MQT can only be used effectively for statistical issues, i.e. without refreshing the MQT permanently.
... IMHO clearing an table with a few hundred thousand records and repopulating it again, cannot be faster than accessing a view.

What the best way is, always depends on multiple factors and must be analyzed for each statement.

BTW could your problem with the wrong data be an not updated/refreshed MQTs.

BTW if you specify a logical file in an SQL statement it is treated like a view, i.e. select fields, join clauses and select omit clauses are taken from the logical file and the SQL statement rewritten based on the physical files/tables. After having rewritten the SQL statements the optimization process is started. If the specified logical file is used, it is nothing else than hazard.
In this way for query execution, it makes no difference whether a logical file is updated with each insert update or delete, while a view is not.

I'd really like to know your consultant.

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 [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von dlclark@xxxxxxxxxxxxxxxx
Gesendet: Friday, 13.5 2016 23:42
An: Midrange Systems Technical Discussion
Betreff: Re: Performance Impact of SQL Views (was: SQL Statement length)

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 05/13/2016
05:10:17 PM:
​I think the idea is that if you need a given SQL statement, the
performance hit is there regardless of rather or not it's in a view.

We don't disagree with that. 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. 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. 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.

Sincerely,

Dave Clark
int.ext: 91078
direct: (937) 531-6378
home: (937) 751-3300

Winsupply Group Services
3110 Kettering Boulevard
Dayton, Ohio 45439 USA
(937) 294-5331



*********************************************************************************************
This email message and any attachments is for use only by the named
addressee(s) and may contain confidential, privileged and/or proprietary information. If you have received this message in error, please immediately notify the sender and delete and destroy the message and all copies. All unauthorized direct or indirect use or disclosure of this message is strictly prohibited. No right to confidentiality or privilege is waived or lost by any error in transmission.
*********************************************************************************************
--
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.

Please contact support@xxxxxxxxxxxx for any subscription related questions.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.