Update Statement Tuning - Plan attached

db2udbgirl · #1 (**permalink**) 11-04-08, 17:55

A simple update involving two small tables takes nearly 8 hours to complete. I would appreciate if you can kindly help me to understand the root cause of this slowness.

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 :

Quote:

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 db2reorgchk

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.

I have attached the explain plan collected using db2exfmt for your reference

n_i · #2 (**permalink**) 11-04-08, 19:13

I would speculate that an index on STG.TB_FIN_BRANCH (BRANCH_SK, BRANCH_STATE) could be a better choice. However, unless I'm missing something, you could achieve the same goal by doing

Code:

update stg.tb_fin_branch aa set aa.branch_state = ' ' where not exists 
(select 1 from stg.tb_state st where aa.branch_state = st.state)

db2udbgirl · #3 (**permalink**) 11-05-08, 15:57

Thanks for your suggestion. I generated the explain plan with the new SQL and it was very good, when I executed the update statement it completed within a second.

But when I tried to validate using select statement it failed.

Old SQL : Returned 34,658 rows

Quote:

select count(*) from STG.TB_FIN_BRANCH AA where (AA.branch_state) = ( select COALESCE(state,' ') from STG.TB_FIN_BRANCH BR LEFT OUTER JOIN STG.TB_STATE ST on BR.branch_state = ST.state where AA.branch_sk = BR.branch_sk ) with ur

New SQL : Returned 0 rows

Quote:

select count(*) from stg.tb_fin_branch aa where not exists ( select 1 from stg.tb_state st where aa.branch_state = st.state )

Could you please provide your thoughts on this? Thanks

n_i · #4 (**permalink**) 11-05-08, 16:20

Well, if you attempt to explain what your SQL statement is doing in plain words, it's really quite simple. Your original statement can be described as this: fetch the STATE column value from TB_STATE if it equals the value in the BRANCH_STATE column in TB_FIN_BRANCH; update BRANCH_STATE with the STATE value. If the matching STATE value cannot be found, put a single space into BRANCH_STATE. Update every line in TB_FIN_BRANCH in this manner.

Now, if you think about it, why would you need to update BRANCH_STATE if it already has the same value as STATE? Clearly, you wouldn't, and only the second part of the original statement would possibly do anything useful. Therefore, we can reduce the description of work to this: find BRANCH_STATE values that do not have matching values in STATE and set them to the single blank value.

As you can probably see, the original statement updates all records in TB_FIN_BRANCH, while the new one only updates those that need to be updated. It appears though that none need to be: they all have matching values in TB_STATE.

From your question I have a feeling that it may not have been you who wrote the original statement...

db2udbgirl · #5 (**permalink**) 11-05-08, 17:07

Thanks for the quick and detailed reply. That helped me understand exactly what is happening on that statement.

And as you suspected I did not code the SQL and infact this Update SQL would be automatically triggered by a third party tool.

Internet Services

Web Development

Digital Marketing

Consumer Tech