Re: SQL Views worth it?

I want to add a point to the discussion about using SQL functions. Over
the past several years we have implemented what are called in-line
functions. If you have a simple SQL function that only contains a RETURN
statement, it could be eligible to be in-lined by the optimizer directly
into the query that invokes it. This avoids the overhead of a call to the
C service program.

You can read more about this in developerWorks at the following link. The
information is also in the SQL Reference as part of the CREATE FUNCTION
(SQL scalar) statement.

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/Improved%20Inlining%20support%20for%20SQL%20Scalar%20User%20Defined%20Functions%20(UDFs)

Sue Romano
Db2 for IBM i Development

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.