Re: SQL Views worth it?

On Wed, Aug 29, 2018 at 1:20 PM, Jonathan Ball <jonball52@xxxxxxxxx> wrote:

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.

I think they are fundamentally different. Scalar and table functions
accept arguments. A view does not. Nested views must get almost
exponentially complicated.

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.

I have had performance problems with SQL functions also. My default
language for SQL function is SQL. But if the code examines every character
in a string, that can run very slow. In those cases, rewriting the
function in RPG improves performance dramatically. Other than that I am
frequently impressed with how quickly the code runs.

But what is the alternative? In the code that converts decimal to a date
you likely have to guard against invalid date digits. Month set to 99, or
to zero. Very time consuming to troubleshoot when an SQL statement fails
in production. And any code that runs without parameters is code with
business logic that is repeated throughout the application. We learned that
lesson back in the RPG subroutine days.