WHich is faster?
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 83
- Joined: Tue Dec 19, 2006 8:38 am
WHich is faster?
Hi everyone.
can any one tell me which one is faster for table update action.
using update existing or insert new rows
OR
insert new or update existing rows
and why?
Is there any real difference between the two?
Thanks
George :D
can any one tell me which one is faster for table update action.
using update existing or insert new rows
OR
insert new or update existing rows
and why?
Is there any real difference between the two?
Thanks
George :D
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
It depends on your ratio of inserts to updates! Faster still is to split your input into two streams and send them to a bulk load for inserts and an update only for updates.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The key performance indicator in ETL is elapsed time. If you can get the data in - or out, as the case may be - within the allowed time window, with a safety margin, then your ETL is performing satisfactorily.
Fast is far less important than correct.
One stream doing insert only and another stream doing update only is not only faster than "try one then if it fails try the other", it also indicates that you are checking your data as they flow through.
Fast is far less important than correct.
One stream doing insert only and another stream doing update only is not only faster than "try one then if it fails try the other", it also indicates that you are checking your data as they flow through.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 83
- Joined: Tue Dec 19, 2006 8:38 am
Yes - of course - you are better off matching the first action to whichever you will have 'more' of. This lessens the number of times you burn two actions to perform one.
My two cents on the 'dual action' actions? Don't use them. To me they are a crutch supporting a poor job design, a dangerous shortcut. As noted by everyone else, you are much better off following the Best Practice of separating inserts and updates to their own links by checking the source for existing keys via a hashed file lookup.
My two cents on the 'dual action' actions? Don't use them. To me they are a crutch supporting a poor job design, a dangerous shortcut. As noted by everyone else, you are much better off following the Best Practice of separating inserts and updates to their own links by checking the source for existing keys via a hashed file lookup.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
We don't have two cent coins in Australia any more Ray. Since they wiped out the 1 and 2 cent pieces you have to add your 5 cents worth.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
We do, and they're still legal. But only banks are forced to accept them. Also, you can write a cheque for two cents. I once had to write a cheque for three cents because of a stupidly programmed computer.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 83
- Joined: Tue Dec 19, 2006 8:38 am
-
- Participant
- Posts: 83
- Joined: Tue Dec 19, 2006 8:38 am