Re: SQL Views worth it?

The function doesn't "do" anything until it is invoked, either.  That doesn't appear to me to be a valid reason to avoid the use of views.  What the original poster and others are trying to achieve is to keep complex join and selection predicates out of the application code, and to increase reusability of the SQL code.  From that standpoint, a view or a function does the same thing.

However, there is a steep cost to using UDFs and UDTFs that quickly becomes apparent in heavy batch processing.  They are implemented as C service programs, and there is a high overhead to calling them.  It won't be noticed much if you have an interactive application that, say, invokes the function a dozen times in the course of loading a page of a subfile.  If you're calling it several hundred thousand times in a long batch application, on the other hand, you will incur a major performance hit.

At my last job, someone had written a general purpose function to convert date values from various formats to other formats.  The function had three parameters for input date value, from-format and to-format; I believe there may have been a parameter for separator character as well.  The function returned a character value.  I was asked to look at an application from a DBA perspective because it was "too slow".  The basic SQL was fine, and no new indexes were needed.  However, I noticed the use of that date function right away.  They were converting a date column defined as dec(9,0) that had dates stored in 0yyyymmdd format to *ISO (I now recall that there was indeed an input parameter to the function for the separator character.)  The process worked over a very large volume of data and was taking some 15-20 minutes.  I replaced the function invocation with the following ugly code in their statement:

substr(digits(dec_date),2,4) || '-' ||
  substr(digits(dec_date),6,2) || '-' ||
  right(digits(dec_date),2) as iso_date

and the process then ran in about two minutes over the same high volume of data.

UDFs and UDTFs need to be used very judiciously.


On 8/26/2018 5:39 AM, Steve Richter wrote:

I have limited use of SQL views. Views are not reusable in that they do
not, compared to scalar and table functions, perform a single thing. They
collect and calculate columns from the standpoint of the selected rows from
a single or set of tables.

I have an SQL function named ITBAL_GetAvailQty( whse, itno ). It returns
the quantity of an item in a warehouse that is available to be allocated to
a new order.

I could use a view to calculate that value. It would be one of the columns
selected from the item master. Other columns would also be calculated or
collected. Such as the fully qualified name of an item or the item
backorder quantity. Providing the consumer of the view with a nice
encapsulation of the calculations and where the data is selected from.

But this view would have limited reusability. To calculate the same
available and backorder quantities for the items in the order detail table
you have to repeat the calculations. Better to use SQL functions to
calculate those values.

Once you have SQL functions calculating specific column values the view
becomes simpler. The other calculation the SQL view does is in selecting
the rows. Join order header to order detail and item master. If you want
to encapsulate that logic consider using a table function. Now the view is
using SQL functions to calculate columns and table functions to collect the
data. What is the point of the view? Esp if using an SQL procedure that
accepts parameters and uses the view to return a result set. The view in
this scenario does not contribute much.



On Sat, Aug 25, 2018 at 5:35 PM, Niels Liisberg <nli@xxxxxxxxxxxxxxxxx>
wrote:

Gents,

You are alle right (imho) when i comes to views - I deploy all our
applications with “nice “ names on top of old nonsense DDS files - and
hiding the legacy that way. However, nested views can in some cases confuse
the optimizer - making a wall between logics in subsequent queries that
have serious performance issues. And when we find these, just report them
back to IBM and Scott Forstie. When I run into these issues I “simply”
rewrite a new view for now - that performs well. But in the end of the day
I think IBM need to work on nested optimization. ... what do you guys
think?
lør. 25. aug. 2018 kl. 18.35 skrev Nathan Andelin <nandelin@xxxxxxxxx>:

Jim,

Your assertion about us building a "nightmare" was not very thoughtful.
After some rumination on my part, it occurred to me that you're vested in
traditional save and restore functions and therefore defensive of them.

It is true that we have some SQL views that cross library boundaries. The
reason for that in our case is because we develop broadly scoped business
applications that include several packages that can be licensed

separately.

One customer may license our student information system, while another

may

license our student transportation system, for example.

As is often the case with broadly scoped business systems, "person" data

is

required in multiple packages. A "person" may be a "teacher" in on

package

and an "employee" in another, for example. So any database tables that

are

shared between multiple packages - we place them in a library that is
shared by all. Perhaps now you can understand the business justification
for having a "common" library, shared across multiple packages?

But that is not the only reason that traditional save and restore
operations fail. They also fail when you define cascading SQL views in a
single library. The restore operation is not smart enough to figure out

the

database relationships and dependencies, so it can't figure out how to
restore the views in their proper sequence.

So we came up with a workaround for our save and restore requirements. I
think that shops that (like us) adopt SQL views should be aware of the
need.

With regards,

Nathan.





On Fri, Aug 24, 2018 at 3:07 PM, Jim Oberholtzer <
midrangel@xxxxxxxxxxxxxxxxx> wrote:

If you have that much trouble restoring then you must be A) on an older
Version/Release, and B) not keeping the views, index objects, and

logical

files together in one library. You must be "cross attaching" them and
therefore building a nightmare.

The problem is not with traditional backup/restore functions but rather

how

you've chosen to implement parts of the database.


--
Jim Oberholtzer
Agile Technology Architects

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
Best regards

Niels Liisberg
Chief Software Architect
System & Method A/S
Phone +45 70 20 30 10
Mobile + 45 31 15 88 61
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD