Co-releated Update Statement Tuning

[Sam Durai]

A simple update involving two small tables takes 45 minutes to
complete. I would appreciate if you can kindly help me to understand
the root cause of this slowness. Users would be happy if I can bring
the timing down to 5 minutes or less.

Environment:
DB2 V8.1 FP 12 / AIX 5.3
Both are non-partitioned tables..residing on a logically partitioned
database

Table1: STG.TB_FIN_BRANCH has 34,658 records
Table2: STG.TB_STATE has 108 records

Update SQL :

update stg.tb_fin_branch aa set (aa.branch_state) = ( select coalesce
state,' ') from stg.tb_fin_branch br left outer join stg.tb_state st
on r.branch_state = st.state where aa.branch_sk = br.branch_sk)

Both tables has matching indexes and are in good shape as per runstat/
reorgchk statistics

Quote:
Table DDL:
$ db2 describe table STG.TB_FIN_BRANCH

Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ --------
----- ------
BRANCH_SK SYSIBM DECIMAL 10 0 No
BRANCH SYSIBM VARCHAR 6 0 No
BRANCH_ADDRESS SYSIBM VARCHAR 30 0 Yes
BRANCH_CITY SYSIBM VARCHAR 25 0 Yes
BRANCH_STATE SYSIBM VARCHAR 2 0 Yes
BRANCH_ZIPCODE SYSIBM VARCHAR 9 0 Yes
BRANCH_COUNTRY SYSIBM VARCHAR 2 0 Yes
CLOSED_DATE SYSIBM TIMESTAMP 10 0 Yes
CONTACT_SK SYSIBM DECIMAL 10 0 No
CONTACT_AGENCY SYSIBM VARCHAR 35 0 Yes

10 record(s) selected.

$ db2 describe indexes for table STG.TB_FIN_BRANCH show detail

Index Index Unique Number of
schema name rule columns Column names
------------------------------- ------------------ --------------
--------------
------------------------------------------------------------
STG WMX1 D 2 +BRANCH_STATE+BRANCH_SK
STG WMX2 D 10 +CONTACT_SK+CONTACT_AGENCY+CLOSED_DATE+BRANCH_ZIPC ODE
+BRANCH_CITY+BRANCH_ADDRESS+BRANCH+BRANCH_SK+BR ANCH_COUNTRY
+BRANCH_STATE

Table 2 DDL:
$ db2 describe table STG.TB_STATE

Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ --------
----- ------
COUNTRY SYSIBM VARCHAR 2 0 No
STATE SYSIBM VARCHAR 2 0 No
STATE_NAME SYSIBM VARCHAR 100 0 No
CTRY_STATE SYSIBM VARCHAR 100 0 No
STATE_ORDER SYSIBM DECIMAL 10 0 No

5 record(s) selected.

$ db2 describe indexes for table STG.TB_STATE show detail

Index Index Unique Number of
schema name rule columns Column names
------------------------------- ------------------ --------------
--------------
------------------------------------------------------------
STG IX1 D 1 +STATE

1 record(s) selected.

Explain Plan:
Access Plan:
-----------
Total Cost: 2.93797e+06
Query Degree: 1

Total Cost: 2.93797e+06
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
34658
UPDATE
( 2)
2.93797e+06
69450.9
/----+----\
34658 34658
FETCH TABLE: STG
( 3) TB_FIN_BRANCH
2.07137e+06
34792.9
/----+----\
34658 34658
TBSCAN TABLE: STG
( 4) TB_FIN_BRANCH
1.20477e+06
134.857
|
34658
TEMP
( 5)
1.20473e+06
134.857
|
34658
NLJOIN
( 6)
1.20472e+06
134.857
/---+---\
34658 1
IXSCAN FILTER
( 7) ( 8)
446.489 456.5
66.8571 66.8571
| |
34658 34981.9
INDEX: STG HSJOIN
WMX1 ( 9)
450.066
66.8571
/-----+-----\
34658 108
IXSCAN IXSCAN
( 10) ( 11)
446.489 0.0933342
66.8571 0
| |
34658 108
INDEX: STG INDEX: STG
WMX1 IX1

[Serge Rielau]

Sam Durai wrote:

A simple update involving two small tables takes 45 minutes to
complete. I would appreciate if you can kindly help me to understand
the root cause of this slowness. Users would be happy if I can bring
the timing down to 5 minutes or less.

Environment:
DB2 V8.1 FP 12 / AIX 5.3
Both are non-partitioned tables..residing on a logically partitioned
database

Table1: STG.TB_FIN_BRANCH has 34,658 records
Table2: STG.TB_STATE has 108 records

Update SQL :

update stg.tb_fin_branch aa set (aa.branch_state) = ( select coalesce
state,' ') from stg.tb_fin_branch br left outer join stg.tb_state st
on r.branch_state = st.state where aa.branch_sk = br.branch_sk)

Let be translate this into English:
You have a table TB_FIN_BRANCH with a column BRANCH_SK.
Now you want to fill in an extra (new?) column BRANCH_STATE for _all_
rows in the table based on TB_STATE.
I think you are doing an extra join here that isn't needed:

UPDATE stg.tb_fin_branch aa
SET aa.branch_state = COALESCE((SELECT state
FROM stg.tb_fin_branch AS br
WHERE aa.branch_sk = br.branch_sk),
' ');

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[jmuehe]

On Nov 6, 5:18*am, Serge Rielau <[e-mail]:

Sam Durai wrote:

A simple update involving two small tables takes 45 minutes to
complete. I would appreciate if you can kindly help me to understand
the root cause of this slowness. Users would be happy if I can bring
the timing down to 5 minutes or less.

Environment:
DB2 V8.1 FP 12 / AIX 5.3
Both are non-partitioned tables..residing on a logically partitioned
database

Table1: STG.TB_FIN_BRANCH has 34,658 records
Table2: STG.TB_STATE has 108 records

Update SQL :

update stg.tb_fin_branch aa set (aa.branch_state) = ( select coalesce
state,' ') from stg.tb_fin_branch br left outer join stg.tb_state st
on r.branch_state = st.state where aa.branch_sk = br.branch_sk)

Let be translate this into English:
You have a table TB_FIN_BRANCH with a column BRANCH_SK.
Now you want to fill in an extra (new?) column BRANCH_STATE for _all_
rows in the table based on TB_STATE.
I think you are doing an extra join here that isn't needed:

UPDATE stg.tb_fin_branch aa
* * SET aa.branch_state = COALESCE((SELECT state
* * * * * * * * * * * * * * * * * * FROM stg.tb_fin_branch AS br
* * * * * * * * * * * * * * * * * * WHERE aa.branch_sk = br.branch_sk),
* * * * * * * * * * * * * * * * * *' ');

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

try this -- it should run much faster.

Update Stg.Tb_Fin_Branch Aa
Set Aa.Branch_State =
(Select Coalesce(State,' ')
From Stg.Tb_State St Where Aa.Branch_Sk = Br.Branch_Sk)
Where Aa.Branch_State !=
(Select Coalesce(State,' ')
From Stg.Tb_State St Where Aa.Branch_Sk = Br.Branch_Sk)

[jmuehe]

On Nov 6, 1:18*pm, [e-mail] wrote:

On Nov 6, 5:18*am, Serge Rielau <[e-mail]:

Sam Durai wrote:

A simple update involving two small tables takes 45 minutes to
complete. I would appreciate if you can kindly help me to understand
the root cause of this slowness. Users would be happy if I can bring
the timing down to 5 minutes or less.

Environment:
DB2 V8.1 FP 12 / AIX 5.3
Both are non-partitioned tables..residing on a logically partitioned
database

Table1: STG.TB_FIN_BRANCH has 34,658 records
Table2: STG.TB_STATE has 108 records

Update SQL :

update stg.tb_fin_branch aa set (aa.branch_state) = ( select coalesce
state,' ') from stg.tb_fin_branch br left outer join stg.tb_state st
on r.branch_state = st.state where aa.branch_sk = br.branch_sk)

Let be translate this into English:
You have a table TB_FIN_BRANCH with a column BRANCH_SK.
Now you want to fill in an extra (new?) column BRANCH_STATE for _all_
rows in the table based on TB_STATE.
I think you are doing an extra join here that isn't needed:

UPDATE stg.tb_fin_branch aa
* * SET aa.branch_state = COALESCE((SELECT state
* * * * * * * * * * * * * * * * * *FROM stg.tb_fin_branch AS br
* * * * * * * * * * * * * * * * * *WHERE aa.branch_sk = br.branch_sk),
* * * * * * * * * * * * * * * * * *' ');

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

try this -- it should run much faster.

Update Stg.Tb_Fin_Branch Aa
Set Aa.Branch_State =
* * * * (Select Coalesce(State,' ')
* * * * From Stg.Tb_State St Where Aa.Branch_Sk = Br.Branch_Sk)
Where Aa.Branch_State !=
* * * * (Select Coalesce(State,' ')
* * * * From Stg.Tb_State St Where Aa.Branch_Sk = Br.Branch_Sk)- Hide quoted text -

- Show quoted text -

correction

Update Stg.Tb_Fin_Branch Aa
Set Aa.Branch_State =
(Select Coalesce(State,' ')
From Stg.Tb_State St Where Aa.Branch_Sk = St.Branch_Sk)
Where Aa.Branch_State !=
(Select Coalesce(State,' ')
From Stg.Tb_State St Where Aa.Branch_Sk = St.Branch_Sk)