By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,136 Members | 2,135 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,136 IT Pros & Developers. It's quick & easy.

MERGE performance help DB2 UDB v8.1.9

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.