Select from update performance - DB2 10 for z/OS

Shay Miller

Select from update performance - DB2 10 for z/OS

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 current timestamp.

We thought that this is a great chance to use the "SELECT from UPDATE" syntax.

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 experience?

Are there any suggestions to make the unified statement run faster?

Thanks,

Shay.

Edited By:
Shay Miller[Organization Members] @ Oct 14, 2015 - 09:49 AM (Asia/Jerusalem)

Joe Geller

RE: Select from update performance - DB2 10 for z/OS
(in response to Shay Miller)

Shay,

One question - were there any I/Os?  If so, which one had the I/O?  Which did you run first?

Joe

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 current timestamp.

We thought that this is a great chance to use the "SELECT from UPDATE" syntax.

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 experience?

Are there any suggestions to make the unified statement run faster?

Thanks,

Shay.

Shay Miller

RE: Select from update performance - DB2 10 for z/OS
(in response to Joe Geller)

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 merged statement.

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 average for 0.0053 sec for sync I/O and there were no async I/O.

Shay.

 
In Reply to Joe Geller:

Shay,

One question - were there any I/Os?  If so, which one had the I/O?  Which did you run first?

Joe

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 current timestamp.

We thought that this is a great chance to use the "SELECT from UPDATE" syntax.

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 experience?

Are there any suggestions to make the unified statement run faster?

Thanks,

Shay.

Edited By:
Shay Miller[Organization Members] @ Oct 14, 2015 - 03:37 PM (Asia/Jerusalem)

Daniel Luksetich

Select from update performance - DB2 10 for z/OS
(in response to Shay Miller)


Hi Shay,
Your results are very interesting and having run quite a few benchmarks
on SELECT from INSERT I have never tried one with SELECT from UPDATE.
So, I thought I'd give it a go. First of all I am assuming that your
application is a local application, either CICS or Batch? If it were a
remote application I would expect that the difference in cost would be
more dramatic and favor SELECT from UPDATE.

Here are my benchmark results using SPFUI and a DB2 sample table for 500
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

7.7% savings

So, in my example the SELECT from UPDATE was more efficient from a CPU
perspective. However, the SELECT from UPDATE had a higher GETPAGE count.
I ran my test multiple times, and the results were consistent. While
UPDATE + SELECT accessed the table and index more often (about twice as
much) the SELECT from UPDATE used a workfile to pass the results from
the data change table. You can see this in the EXPLAIN output. So, I
suspect that the performance in your case might require a careful
examination of what is being accessed and when. If your SELECT in the
UPDATE + SELECT scenario is highly efficient, say index only, then it is
possible that the overhead of workfile initialization and scanning
exceeds the savings of half the cross-memory calls. In my test it was
index and table access for both statements.

If your application is a remote application there can be a greater
potential for savings.

I ran another test using CLP and the same SQL I used for the SPUFI test:


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

15.4% savings.

So, I believe there is a potential for savings, especially for remote
calls. Your example is very interesting and I recommend you look at the
access path and you may find an answer there. If your SELECT is index
only then you indeed may have a situation where a highly optimized
UPDATE and SELECT will outperform the SELECT from UPDATE. However, if I
were you I would run my benchmark hundreds or more times to be sure. If
your application is local then you may have to stick with the original
design, but if it is remote then the new design could be a cost savings.
Make sure you test!

I hope you found this helpful and thanks for the though provoking
question!

Cheers,

Dan

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 merged statement.
>
> 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.
>
> Shay.
>
> In Reply to Joe Geller:
>
> Shay,
>
> One question - were there any I/Os? If so, which one had the I/O? Which did you run first?
>
> Joe
>
> 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 current timestamp.
>
> We thought that this is a great chance to use the "SELECT from UPDATE" syntax.
>
> 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 experience?
>
> Are there any suggestions to make the unified statement run faster?
>
> Thanks,
>
> Shay.

-----End Original Message-----


Links:
------
[1] http://www.idug.org/p/fo/st/post=171957&anc=p171957#p171957
[2] http://www.idug.org/p/fo/si/topic=19
[3] http://www.idug.org/p/us/to/
[4] http://www.idug.org/p/cm/ld/fid=639
[5] http://www.idug.org/p/cm/ld/fid=2

Venkat Srinivasan

RE: Select from update performance - DB2 10 for z/OS
(in response to Shay Miller)

Shay,

When comparing the testcases do you notice a higher lock counts for SELECT FROM UPDATE clause?

I setp a small testcase and played with 1 row. I am in a controlled environment so noise is almost non existent. I did stop and start the pageset before running the two SQL flavors. I ran the tests using SPUFI with autocommit. I see that the SELECT FROM UPDATE clause does show a higher class-1 / class-2 time however in my case I do see a higher count against Global-L-lock. I have 7 LOCKS with 3 claims with Update followed by a separate SELECT. I had 14 LOCKS with 21 claims with SELECT FROM OLD TABLE (UPDATE.....). There will be access to work file in SELECT FROM OLD TABLE and that will result in extra locks.

I do notice that I take these extra claims in SELECT FROM UPDATE clause.

TS         CS        DB=DSNDB06        PS=SYSTSTRG
IX         CS        DB=DSNDB06        PS=DSNAUH01
TS         CS        DB=DSNDB06        PS=SYSUSER
TS         CS        DB=DSNDB06        PS=SYSTSDBU
IX         CS        DB=DSNDB06        PS=DSNDDH01
TS         CS        DB=DSNDB06        PS=SYSTSDBA
IX         CS        DB=DSNDB06        PS=DSNATX02
TS         CS        DB=DSNDB06        PS=SYSTSTAU
TS         CS        DB=DSNDB06        PS=SYSTSIXS
IX         CS        DB=DSNDB06        PS=DSNDSX01
TS         CS        DB=DSNDB06        PS=SYSTSTSP
IX         CS        DB=DSNDB06        PS=DSNDCX05
TS         CS        DB=DSNDB06        PS=SYSTSCOL
IX         CS        DB=DSNDB06        PS=DSNDTX01
TS         CS        DB=DSNDB06        PS=SYSTSTAB

With the extra IRLM requests it can be explained why cpu time is more but Sync IO wasn't an issue in my test. Is it possible that your test case had noise (from other processing)

Why not compare the counts against the class-3 buckets and see where you spend more processing? 

 

Venkat

Daniel Luksetich

Select from update performance - DB2 10 for z/OS
(in response to Venkat Srinivasan)


Venkat,

Is it possible that those extra locks are from a long prepare? If you
run the statements hundreds of times what kind of performance difference
do you see?

Thanks,

Dan

On 10.14.2015 11:36, Venkat Srinivasan wrote:

> Shay,
>
> When comparing the testcases do you notice a higher lock counts for SELECT FROM UPDATE clause?
>
> I setp a small testcase and played with 1 row. I am in a controlled environment so noise is almost non existent. I did stop and start the pageset before running the two SQL flavors. I ran the tests using SPUFI with autocommit. I see that the SELECT FROM UPDATE clause does show a higher class-1 / class-2 time however in my case I do see a higher count against Global-L-lock. I have 7 LOCKS with 3 claims with Update followed by a separate SELECT. I had 14 LOCKS with 21 claims with SELECT FROM OLD TABLE (UPDATE.....). There will be access to work file in SELECT FROM OLD TABLE and that will result in extra locks.
>
> I do notice that I take these extra claims in SELECT FROM UPDATE clause.
>
> TS CS DB=DSNDB06 PS=SYSTSTRG
> IX CS DB=DSNDB06 PS=DSNAUH01
> TS CS DB=DSNDB06 PS=SYSUSER
> TS CS DB=DSNDB06 PS=SYSTSDBU
> IX CS DB=DSNDB06 PS=DSNDDH01
> TS CS DB=DSNDB06 PS=SYSTSDBA
> IX CS DB=DSNDB06 PS=DSNATX02
> TS CS DB=DSNDB06 PS=SYSTSTAU
> TS CS DB=DSNDB06 PS=SYSTSIXS
> IX CS DB=DSNDB06 PS=DSNDSX01
> TS CS DB=DSNDB06 PS=SYSTSTSP
> IX CS DB=DSNDB06 PS=DSNDCX05
> TS CS DB=DSNDB06 PS=SYSTSCOL
> IX CS DB=DSNDB06 PS=DSNDTX01
> TS CS DB=DSNDB06 PS=SYSTSTAB
>
> With the extra IRLM requests it can be explained why cpu time is more but Sync IO wasn't an issue in my test. Is it possible that your test case had noise (from other processing)
>
> Why not compare the counts against the class-3 buckets and see where you spend more processing?
>
> Venkat
>
> -----End Original Message-----


Links:
------
[1] http://www.idug.org/p/fo/st/post=171964&anc=p171964#p171964
[2] http://www.idug.org/p/fo/si/topic=19
[3] http://www.idug.org/p/us/to/
[4] http://www.idug.org/p/cm/ld/fid=639
[5] http://www.idug.org/p/cm/ld/fid=2

Venkat Srinivasan

RE: Select from update performance - DB2 10 for z/OS
(in response to Daniel Luksetich)

The claims on the DSNDB06 objects are certainly from long prepare. That surprises me because I am the only user on this system. I did several iterations of the tests  but copied the metrics from the last one.

I redid the tests, executing the SQLs 201 times to mitigate the SQLCACHE.

Test 1:

-STOP DB(VENKATDB) SPACE(VENKATTS)

-START DB(VENKATDB) SPACE(VENKATTS)

Ran the following SQL sequence 201 times. No commit on the last one so I can swap and look at the monitor. 

UPDATE SYSADM.VENTEST01            
SET CINT = 4;                      
SELECT CINT FROM SYSADM.VENTEST01; 
COMMIT;                       

The class-1 time (cpu) was 4.316 seconds. Cl-2 cpu is .242 seconds. There were 951 parent-L lock waits. There were 7 locks and 3 claims held because I disabled autocommit.

Test 2:-

-STOP DB(VENKATDB) SPACE(VENKATTS)

-START DB(VENKATDB) SPACE(VENKATTS)

Ran the following 201 times. Last one had no commit. 

SELECT CINT FROM OLD TABLE  
  (UPDATE SYSADM.VENTEST01  
   SET CINT = 5);           
COMMIT; 

Cl-1 cpu is  5.417 secs with a cl-2 cpu of .803 secs

There were 1289 lock waits on Parent L-locks. At the end there were 9 locks and 5 claims. The extra locks were on work file object. 1 for the database and 1 for the pageset. Understandably the extra claims are on work file object too, a write claim and a CS claim, waiting for my commit.

Perhaps the extra entries into IRLM conume more cycles.

I am on a uniprocessor system with no other activity outside of the test case.

I dont have a DB2 driver with me to test this on a simple java call. DB2 metrics will be the same but I anticipate that the extra cpu cycles may outwigh the redundant entry/exit caused by splitting the SQL into two. However if someone can test it, we may be astonished.

Here is the DDL.

CREATE TABLE SYSADM.VENTEST01                                    
   (CINT                 INTEGER NOT NULL,                       
    CCHAR                CHAR(1) FOR SBCS DATA NOT NULL,         
    CTS                  TIMESTAMP (6) WITHOUT TIME ZONE NOT NULL)
  IN VENKATDB.VENKATTS                                           
  AUDIT NONE                                                     
  DATA CAPTURE NONE                                              
  CCSID      EBCDIC                                              
  NOT VOLATILE                                                   
  APPEND NO  ;                                                   
COMMIT;          

--INSERT INTO SYSADM.VENTEST01
--(CINT, CCHAR , CTS )        
--VALUES                      
--(1,'A',CURRENT TIMESTAMP);  

 

Venkat                                                

 

 

 

                          

Daniel Luksetich

Select from update performance - DB2 10 for z/OS
(in response to Venkat Srinivasan)


I had 501 of these

update emp
set lastname = 'Rady'
where empno = '000011'
;
select lastname from
emp
where empno = '000011'
;

versus 501 of these

select lastname from
final table(
update emp
set lastname = 'Rady'
where empno = '000011')
;

I would expect our results to be similar and yet they are not. I got a
7% C2 CPU reduction in SPUFI and a 15% C2 CPU reduction via distributed
CLI. If I read your results correctly then you are looking at a 231.8%
increase.

I'm quite surprise by the difference and will run my test again
tomorrow. In the meantime I think we at least can all agree on by even
simple benchmarks can teach you something.

Thanks,

Dan

On 10.14.2015 14:49, Venkat Srinivasan wrote:

> The claims on the DSNDB06 objects are certainly from long prepare. That surprises me because I am the only user on this system. I did several iterations of the tests but copied the metrics from the last one.
>
> I redid the tests, executing the SQLs 201 times to mitigate the SQLCACHE.
>
> Test 1:
>
> -STOP DB(VENKATDB) SPACE(VENKATTS)
>
> -START DB(VENKATDB) SPACE(VENKATTS)
>
> Ran the following SQL sequence 201 times. No commit on the last one so I can swap and look at the monitor.
>
> UPDATE SYSADM.VENTEST01
> SET CINT = 4;
> SELECT CINT FROM SYSADM.VENTEST01;
> COMMIT;
>
> The class-1 time (cpu) was 4.316 seconds. Cl-2 cpu is .242 seconds. There were 951 parent-L lock waits. There were 7 locks and 3 claims held because I disabled autocommit.
>
> Test 2:-
>
> -STOP DB(VENKATDB) SPACE(VENKATTS)
>
> -START DB(VENKATDB) SPACE(VENKATTS)
>
> Ran the following 201 times. Last one had no commit.
>
> SELECT CINT FROM OLD TABLE
> (UPDATE SYSADM.VENTEST01
> SET CINT = 5);
> COMMIT;
>
> Cl-1 cpu is 5.417 secs with a cl-2 cpu of .803 secs
>
> There were 1289 lock waits on Parent L-locks. At the end there were 9 locks and 5 claims. The extra locks were on work file object. 1 for the database and 1 for the pageset. Understandably the extra claims are on work file object too, a write claim and a CS claim, waiting for my commit.
>
> Perhaps the extra entries into IRLM conume more cycles.
>
> I am on a uniprocessor system with no other activity outside of the test case.
>
> I dont have a DB2 driver with me to test this on a simple java call. DB2 metrics will be the same but I anticipate that the extra cpu cycles may outwigh the redundant entry/exit caused by splitting the SQL into two. However if someone can test it, we may be astonished.
>
> Here is the DDL.
>
> CREATE TABLE SYSADM.VENTEST01
> (CINT INTEGER NOT NULL,
> CCHAR CHAR(1) FOR SBCS DATA NOT NULL,
> CTS TIMESTAMP (6) WITHOUT TIME ZONE NOT NULL)
> IN VENKATDB.VENKATTS
> AUDIT NONE
> DATA CAPTURE NONE
> CCSID EBCDIC
> NOT VOLATILE
> APPEND NO ;
> COMMIT;
>
> --INSERT INTO SYSADM.VENTEST01
> --(CINT, CCHAR , CTS )
> --VALUES
> --(1,'A',CURRENT TIMESTAMP);
>
> Venkat
>
> -----End Original Message-----


Links:
------
[1] http://www.idug.org/p/fo/st/post=171973&anc=p171973#p171973
[2] http://www.idug.org/p/fo/si/topic=19
[3] http://www.idug.org/p/us/to/
[4] http://www.idug.org/p/cm/ld/fid=639
[5] http://www.idug.org/p/cm/ld/fid=2

Venkat Srinivasan

RE: Select from update performance - DB2 10 for z/OS
(in response to Daniel Luksetich)

Your syntax is FINAL TABLE versus my syntax is OLD TABLE as the op mentioned OLD TABLE in his original post. Will you share your DDL and the two SQL stmts I can execute those and compare. I said I have a uni processor system. God knows how DB2 code enters IRLM. Perhaps I am taking a hit on the inherent hardware serialization with 1 logical cpu.

I will go back and change the sql to final.

Venkat

Venkat Srinivasan

RE: Select from update performance - DB2 10 for z/OS
(in response to Venkat Srinivasan)

As expected there is no change in the metrics for me with FINAL / OLD. From the accounting metrics that I have, it looks like the extra locks for workfile negatively influence the overall performance when SELECT ...(UPDATE) is driven against small tables. I expect it to behave differently when there is some additional processing around the UPDATE statement, something like number of rows / pages changed and the size of the returned resultset etc.    

Venkat

Shay Miller

RE: Select from update performance - DB2 10 for z/OS
(in response to Venkat Srinivasan)

Thank you Daniel and Venkat for you replies and checks and for the interesting discussion.

The program is a local CICS program.
The initial SELECT and UPDATE were very efficient to begin with :

"The access path for locating the row is a index only matching scan (3/3) on a unique index."

I'm surprised that the work file handling takes more CPU than the index search.

I'll let the program run for a bit more in the QA environment in order to get more accurate stats.

We'll discard this change and won't move it to production if the stats stays the same.

Thanks,

Shay.

Edited By:
Shay Miller[Organization Members] @ Oct 15, 2015 - 09:42 AM (Asia/Jerusalem)
Shay Miller[Organization Members] @ Oct 15, 2015 - 09:44 AM (Asia/Jerusalem)

Daniel Luksetich

Select from update performance - DB2 10 for z/OS
(in response to Shay Miller)
One final note. I was testing on DB2 11 for z/OS and you may get better results under DB2 11 due to improvements in workfile utilization, especially the potential for using an in-memory workfile.

I did just do my same exact test under DB2 10 for z/OS and in my case the results were about the same as my results under DB2 11. So, in my case a modest reduction in CPU for the single statement solution.

Thanks,
Dan

From: Shay Miller [mailto:[login to unmask email]
Sent: Thursday, October 15, 2015 2:40 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Select from update performance - DB2 10 for z/OS

Thank you Daniel and Venkat for you replies and for the interesting discussion.
The program is a local CICS program.
The initial SELECT and UPDATE were very efficient to begin with :
"The access path for locating the row is a index only matching scan (3/3) on a unique index."
I'm surprised that the work file handling takes more CPU than the index search.

We'll probably discard this change and won't move it to production.

-----End Original Message-----