Hi Vinay,
Start with what Visual Explain has to say about the UDTF (I'm assuming
you're using a table function). Does it recommend indexes to be built?
Visual Explain ability to drill into a UDTF
<
https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/Visual%20Explain%20ability%20to%20drill%20into%20a%20UDTF>
Second, if your WHERE clause row filtering is in the query invoking the
function, relocate the WHERE clause filtering to inside the function, which
means you'll probably need to enhance the parameter list of the function to
support the WHERE clause inside it.
Third, consider taking the SQL out of a function, and running it as stand
alone SQL, or relocate the function SQL to an SQL view.
It can be difficult to integrate an SQL function fetching data into a query
invoking that function, and yield good performance. I'm not saying it can't
be done, just that in my experience using a design like that, performance
tuning is more difficult.
I've had great success using SQL functions to perform SET based database
modification SQL (inserts, updates, deletes), but I tend to avoid functions
for fetching data. For the few table functions I use to fetch data, they
perform all the row selection inside the function, and the invoking queries
don't perform joins, and/or operate over a small number of rows.
I've not tried to use a function for a data fetch in a while, so perhaps
there have been improvements to the SQL optimizer to better optimize
function invoked by a query. If you think about it, a function is a bit of
a black box, which makes it difficult for the SQL optimizer to tune *in
conjunction with the query invoking the function*. I'm sure the SQL
optimizer can optimize the functions internals as a standalone unit, but
the difficulty seems to arise when trying to optimize the combination of
the invoking query plus the function.
I recall relocating a couple of SQL table functions where performance was
poor or inconsistent, to SQL views, which resulted in better performance
that was more consistent. SQL run time will never be 100% consistent,
because the optimizer takes competition for resources into account, meaning
the access plan can change from one execution to another.
If you post the function SQL and the invoking query SQL, we could provide
more targeted advice.
Mike
date: Sun, 22 Nov 2015 20:58:14 -0500
from: Vinay Gavankar <vinaygav@xxxxxxxxx>
subject: Re: Checking Thresholds in a file using SQL
I created the Query as an SQL Function and called it from an SQLRPGLE
program.
When trying this on 100,000 records, I found that though the query
statement executed online (under strsql), always ran in a couple of
seconds, when the RPG program was submitted to batch, the job sometimes ran
in a couple of seconds and sometimes took over 90 minutes to complete. The
input file was exactly the same. One job would be done in a few seconds and
the next one would take 90 minutes. And after a few tries it would again
complete in a few seconds.
Any thoughts or suggestions?
I am extremely wary of putting this in production, not knowing why this is
happening, and how to control it.
Vinay
midrange.com
