469,923 Members | 1,337 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,923 developers. It's quick & easy.

MERGE performance help DB2 UDB v8.1.9

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)
Jan 12 '06 #1
3 3358
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
Jan 12 '06 #2
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.
Jan 12 '06 #3
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
Jan 13 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Kevin King | last post: by
2 posts views Thread by Private Pyle | last post: by
3 posts views Thread by Michel Esber | last post: by
24 posts views Thread by Henry J. | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.