Re: SQL Views worth it?

Views do not carry any performance benefit themselves. They are a
shortcut, or snippet, so that you don't have to copy the SQL statement
between programs. SQL Indexes are the performance tool, which are similar
to DDS logicals, but without the selection criteria. If you're thinking
of building logicals, and want to use SQL instead, you build the index
with the keys from the logical, and put the selection criteria in a view.

The problem in this is that you'll probably badly select the index keys,
because you're thinking in terms of RPG database access. The method I use
is to build the view, run the program with a database monitor activated to
report what indexes might be helpful, and then create the indexes it
recommends. Rerun and see if the indexes made any difference. Be aware
that the database monitor tool has a negative impact on the performance of
SQL on the system, so, be sure its off before you do any benchmark
testing.



___________________________________
Darren Strong
Dekko





From: "Thomas Garvey" <tgarvey@xxxxxxxxxx>
To: "RPG programming on the IBM i (AS/400 and iSeries)"
<rpg400-l@xxxxxxxxxxxx>
Date: 08/24/2018 02:56 PM
Subject: SQL Views worth it?
Sent by: "RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxx>



Hi,

I'm developing a new application and trying to have the DB do as much of
the work as possible.
So, I'm incorporating SQL Views but finding the throughput underwhelming.
Granted that some of the views are based on other views (in keeping with
the attempt to have the DB do some work for me)
but it appears that every time a view is queried the views are rebuilt
by the OS.

I read somewhere that Views can be considered somewhat like logical
files, but at least logicals
can be set to have immediate updates as underlying physicals change.
Views have no such attribute settings.

So, what's considered best practices for SQL Views?
Should I just rewrite these things as logicals?