Re: SQL Views worth it?

On Mon, Aug 27, 2018 at 1:51 PM, Thomas Garvey <tgarvey@xxxxxxxxxx> wrote:

These nesting, or cascading views are exactly my circumstance, and the
apparent slow response time
are due to using them (I believe), and prompted my original post.

In regard to the performance (slow response) of your nested views, what are
you comparing to? Can you consolidate the SQL syntax into a single
protracted SQL statement or a combination of CTEs and get better
performance? In my experience, the answer to that question is no. Quite to
the contrary, we have achieved better response times by creating nested
views. That was counter-intuitive to me at first because the number of the
number of views (files) involved. But that's how it has worked out for us
in every case that I'm aware of.

For best performance, you want queries to locate a starting row by key
(comparable to SETLL), then read rows by key so long as the key matches a
filter criteria such as an SQL WHERE clause. With nested views, you want
your base view (especially), and as many subsequent views to be able to do
that, as opposed to performing full-table scans.

Since nested views are so much simpler in syntax, you should be able to
better predict whether the query engine will be able to perform the query
processing the way that you want (i.e. efficiently).