Your results are very interesting and having run quite a few
on SELECT from INSERT I have never tried one with SELECT from
So, I thought I'd give it a go. First of all I am assuming that
application is a local application, either CICS or Batch? If it
remote application I would expect that the difference in cost would
more dramatic and favor SELECT from UPDATE.
Here are my benchmark results using SPFUI and a DB2 sample table
executions of the same statements:
UPDATE then SELECT
+ CP CPU Time 00:00:00.103
SELECT from UPDATE
+ CP CPU Time 00:00:00.095
So, in my example the SELECT from UPDATE was more efficient from a
perspective. However, the SELECT from UPDATE had a higher GETPAGE
I ran my test multiple times, and the results were consistent.
UPDATE + SELECT accessed the table and index more often (about
much) the SELECT from UPDATE used a workfile to pass the results
the data change table. You can see this in the EXPLAIN output. So,
suspect that the performance in your case might require a
examination of what is being accessed and when. If your SELECT in
UPDATE + SELECT scenario is highly efficient, say index only, then
possible that the overhead of workfile initialization and
exceeds the savings of half the cross-memory calls. In my test it
index and table access for both statements.
If your application is a remote application there can be a
potential for savings.
I ran another test using CLP and the same SQL I used for the SPUFI
UPDATE then SELECT
+ CP CPU Time 00:00:00.069
+ IIP CPU Time 00:00:00.054
SELECT from UPDATE
+ CP CPU Time 00:00:00.000
+ IIP CPU Time 00:00:00.104
So, I believe there is a potential for savings, especially for
calls. Your example is very interesting and I recommend you look at
access path and you may find an answer there. If your SELECT is
only then you indeed may have a situation where a highly
UPDATE and SELECT will outperform the SELECT from UPDATE. However,
were you I would run my benchmark hundreds or more times to be
your application is local then you may have to stick with the
design, but if it is remote then the new design could be a cost
Make sure you test!
I hope you found this helpful and thanks for the though
On 10.14.2015 06:37, Shay Miller wrote:
> Hello Joe, Thank you for your reply.
> The data for version of the program with separate SELECT &
UPDATE is from one day before the data of the version with the
> Regrading the I/O:
> In The original version,
> The SELECT waited in average for 0.0080 sec for sync I/O and
there were no async I/O.
> There wasn't any I/O of any kind for the UPDATE.
> The SELECT from UPDATE waited in avarage for 0.0053 sec for
sync I/O and there were no async I/O.
> In Reply to Joe Geller:
> One question - were there any I/Os? If so, which one had the
I/O? Which did you run first?
> In Reply to Shay Miller:
> Hello everyone.
> One of our most active programs displays the last time the
user used that screen.
> The logic is as follows:
> Select last time the screen was used by this user ;
> Update the last time the screen was used for this user with
> We thought that this is a great chance to use the "SELECT from
> The two sql statements where merged to:
> SELECT last_logon
> FROM OLD TABLE
> (UPDATE tab
> SET last_logon = CURRENT TIMESTAMP
> WHERE c1 = :hv1
> AND c2 = :hv2
> AND c3 = :hv3
> The access path for locating the row is a index only matching
scan (3/3) on a unique index.
> We excpected that the unified statment will perform better
than the SELECT + UPDATE separately ,
> because the process of locating the requested row is done once
instead of twice.
> In fact, the performance is about 20% worse
> (these are avargaes per command from about 200 activations in
our QA environment):
> Type Elapsed CPU Getpage
> ---------------------- ------------- -------------
> Select 0.00091 0.00010 4
> Update 0.00051 0.00021 4
> Select + Update 0.00142 0.00031 8
> Select from Upd 0.00165 0.00038 8
> Has anyone tried this syntax and can share his/hers
> Are there any suggestions to make the unified statement run
-----End Original Message-----