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.ta ttoo
and t1.sex=t2.sex
and t1.birth_date=t 2.birth_date
and t1.assoc=t2.ass oc
and t1.sire_regnum= t2.sire_regnum
and t1.dam_regnum=t 2.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_ts bd
on nullid.angus_in (tattoo, sex, birth_date,
assoc, sire_regnum, dam_regnum)
3  3526 
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.ta ttoo
and t1.sex=t2.sex
and t1.birth_date=t 2.birth_date
and t1.assoc=t2.ass oc
and t1.sire_regnum= t2.sire_regnum
and t1.dam_regnum=t 2.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_ts bd
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
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.ta ttoo
and t1.sex=t2.sex
and t1.birth_date=t 2.birth_date
and t1.assoc=t2.ass oc
and t1.sire_regnum= t2.sire_regnum
and t1.dam_regnum=t 2.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_ts bd
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.
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.ta ttoo
and t1.sex=t2.sex
and t1.birth_date=t 2.birth_date
and t1.assoc=t2.ass oc
and t1.sire_regnum= t2.sire_regnum
and t1.dam_regnum=t 2.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_ts bd
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Kevin King |
last post by:
I have a question about an assignment I have. I need to count the
number of comparisons in my merge sort. I know that the function is
roughly nlog(n), but I am definately coming up with too many
comparisons. It seems to me like I should just use a single counter in
the merge function's 'if' statement, but this can't be right because
an array of 50 takes about 100 comparisons this way. If anyone has any
suggestions I would greatly...
|
by: Private Pyle |
last post by:
AIX 5.1, DB2 8.1.3 64-bit ESE 5 partitions 1 catalog, 4 data.
I have a situation where I have to update 269,000,000 rows in a table
with the value in another table with just about the same number of
records. It's a reporting table and the update is based on the
primary key and both tables are indexed to support to the look up.
Both tables also share the same partitioning key and are in the same
node group.
My first try was to declare...
|
by: DraguVaso |
last post by:
Hi,
I have two DataTables (our DataViews or whatever that will suit the best for
the solution). I want to merge these two DataTables the fastest as possible,
but they have to be merged one table after the others: First all the recors
of DataTable1, and afterwarths the records of DataTable2.
Does anybody has any idea how to do this? The purpose it that it goes as
fast as possible! DataTable2 can have up to 15.000 records, DataTable1...
|
by: UDBDBA |
last post by:
Hi All:
I need some clarification on a MERGE statement. The database is on V8
FP12 (AIX) 64bit.
The source table is tableA.
The target is a View "FACT" with UNION ALL because of the 512 Gig
restriction.
Will a MERGE into this view "FACT" from tableA work?
|
by: sk.rasheedfarhan |
last post by:
Hi all,
I set the configuration for Merge replication for Subscription on
one database and
I have created the Merge replication for publication on another
machine.
And I updated columns of subscription database table and I find
publication machine database table also updated with subscription
database table, here my problem starts,
My problem is when I run replication for merge transaction, I have to
find instance of the Windows on my...
| | |
by: eavery |
last post by:
Does anyone know of any documentation on the performance of partition
merge/split? Does the merge or split of a partition cause any locking
on the partitioned table? If you were merging or splitting a large
volume of data rebalancing your partitioned table would you
potentially lock users out?
|
by: eavery |
last post by:
Does anyone know of any documentation on the performance of partition
merge/split? Does the merge or split of a partition cause any locking
on the partitioned table? If you were merging or splitting a large
volume of data rebalancing your partitioned table would you
potentially lock users out?
|
by: Michel Esber |
last post by:
Hi all,
DB2 V8 LUW FP 15
There is a table T (ID varchar (24), ABC timestamp). ID is PK.
Our application needs to frequently update T with a new value for ABC.
update T set ABC=? where ID = ?
|
by: Henry J. |
last post by:
My app needs to insert thousand value rows into a mostly empty table
(data are read from a file). I can either use inserts, or use merge.
The advantage of using merge is that in the few cases where the table
is not empty, it can take care of the updating part, which makes the
app cleaner.
However, my concern is the merge state would slow dowm the insertion
of new data, since in most cases the table is empty.
So my questions (before I...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
