MERGE performance help DB2 UDB v8.1.9

[Bob Stearns]

We have a very poorly performing MERGE statement (an hour or more on
tables of ~10000 and ~100000). This may require building temporary
tables with appropriate indexes, but I thought I would ask your
collective opinion. Would building a comprehensive index on
nullid.angus_is improve this statement's performance?

merge into (select * from nullid.ANGUS_IN where bhid is null) t1
using (select ta.*, ts.regnum as sire_regnum, td.regnum as dam_regnum
from is3.animals ta
join is3.animals ts on ta.sire_bhid=ts.bhid
join is3.animals td on ta.dam_bhid=ts.bhid ) t2
on t1.tattoo=t2.tattoo
and t1.sex=t2.sex
and t1.birth_date=t2.birth_date
and t1.assoc=t2.assoc
and t1.sire_regnum=t2.sire_regnum
and t1.dam_regnum=t2.dam_regnum
when matched and t2.regnum is null then update
set t1.bhid = t2.bhid

The proposed index would be:

create index nullid.an_in_tsbd
on nullid.angus_in(tattoo, sex, birth_date,
assoc, sire_regnum, dam_regnum)

[Serge Rielau]

Bob Stearns wrote:

We have a very poorly performing MERGE statement (an hour or more on
tables of ~10000 and ~100000). This may require building temporary
tables with appropriate indexes, but I thought I would ask your
collective opinion. Would building a comprehensive index on
nullid.angus_is improve this statement's performance?

merge into (select * from nullid.ANGUS_IN where bhid is null) t1
using (select ta.*, ts.regnum as sire_regnum, td.regnum as dam_regnum
from is3.animals ta
join is3.animals ts on ta.sire_bhid=ts.bhid
join is3.animals td on ta.dam_bhid=ts.bhid ) t2
on t1.tattoo=t2.tattoo
and t1.sex=t2.sex
and t1.birth_date=t2.birth_date
and t1.assoc=t2.assoc
and t1.sire_regnum=t2.sire_regnum
and t1.dam_regnum=t2.dam_regnum
when matched and t2.regnum is null then update
set t1.bhid = t2.bhid

The proposed index would be:

create index nullid.an_in_tsbd
on nullid.angus_in(tattoo, sex, birth_date,
assoc, sire_regnum, dam_regnum)

One of the most important criteria for MEREG to perform is that the
source produces provably (by DB2 that is) DISTINCT rows w.r.t. the ON
clause. If that is not the case MERGE requires that DB2 detects
duplicate updates and raises a runtime error. This is costly.
MERGE shines when the on clause is on a key.
In your case it's inetersting that you apply t2.regnum IS NULL in teh
MATCHED clause. Typically predicates there are used to distinguish
between UPDATE and DELETE branches.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Bob Stearns]

Serge Rielau wrote:

Bob Stearns wrote:

We have a very poorly performing MERGE statement (an hour or more on
tables of ~10000 and ~100000). This may require building temporary
tables with appropriate indexes, but I thought I would ask your
collective opinion. Would building a comprehensive index on
nullid.angus_is improve this statement's performance?

merge into (select * from nullid.ANGUS_IN where bhid is null) t1
using (select ta.*, ts.regnum as sire_regnum, td.regnum as dam_regnum
from is3.animals ta
join is3.animals ts on ta.sire_bhid=ts.bhid
join is3.animals td on ta.dam_bhid=ts.bhid ) t2
on t1.tattoo=t2.tattoo
and t1.sex=t2.sex
and t1.birth_date=t2.birth_date
and t1.assoc=t2.assoc
and t1.sire_regnum=t2.sire_regnum
and t1.dam_regnum=t2.dam_regnum
when matched and t2.regnum is null then update
set t1.bhid = t2.bhid

The proposed index would be:

create index nullid.an_in_tsbd
on nullid.angus_in(tattoo, sex, birth_date,
assoc, sire_regnum, dam_regnum)

One of the most important criteria for MEREG to perform is that the
source produces provably (by DB2 that is) DISTINCT rows w.r.t. the ON
clause. If that is not the case MERGE requires that DB2 detects
duplicate updates and raises a runtime error. This is costly.
MERGE shines when the on clause is on a key.
In your case it's inetersting that you apply t2.regnum IS NULL in teh
MATCHED clause. Typically predicates there are used to distinguish
between UPDATE and DELETE branches.

Cheers
Serge

Thanks for the quick reply. You missed (or maybe thought I knew what was
doing:-) the not so obvious error in the last JOIN: ts.bhid should have
been td.bhid; the syntax scanner can not find logical errors. My only
excuse is that it was not written by me but a colleague.

[Serge Rielau]

Bob Stearns wrote:

Serge Rielau wrote:

Bob Stearns wrote:

We have a very poorly performing MERGE statement (an hour or more on
tables of ~10000 and ~100000). This may require building temporary
tables with appropriate indexes, but I thought I would ask your
collective opinion. Would building a comprehensive index on
nullid.angus_is improve this statement's performance?

merge into (select * from nullid.ANGUS_IN where bhid is null) t1
using (select ta.*, ts.regnum as sire_regnum, td.regnum as dam_regnum
from is3.animals ta
join is3.animals ts on ta.sire_bhid=ts.bhid
join is3.animals td on ta.dam_bhid=ts.bhid ) t2
on t1.tattoo=t2.tattoo
and t1.sex=t2.sex
and t1.birth_date=t2.birth_date
and t1.assoc=t2.assoc
and t1.sire_regnum=t2.sire_regnum
and t1.dam_regnum=t2.dam_regnum
when matched and t2.regnum is null then update
set t1.bhid = t2.bhid

The proposed index would be:

create index nullid.an_in_tsbd
on nullid.angus_in(tattoo, sex, birth_date,
assoc, sire_regnum, dam_regnum)

One of the most important criteria for MEREG to perform is that the
source produces provably (by DB2 that is) DISTINCT rows w.r.t. the ON
clause. If that is not the case MERGE requires that DB2 detects
duplicate updates and raises a runtime error. This is costly.
MERGE shines when the on clause is on a key.
In your case it's inetersting that you apply t2.regnum IS NULL in teh
MATCHED clause. Typically predicates there are used to distinguish
between UPDATE and DELETE branches.

Cheers
Serge

Thanks for the quick reply. You missed (or maybe thought I knew what was
doing:-) the not so obvious error in the last JOIN: ts.bhid should have
been td.bhid; the syntax scanner can not find logical errors. My only
excuse is that it was not written by me but a colleague.

Ah yes.. a bad on clause may do you in in no time but for long time

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab