WHich is faster?

georgesebastian · Post by **georgesebastian** » Sat Mar 31, 2007 3:48 am

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

vmcburney · Post by **vmcburney** » Sat Mar 31, 2007 4:07 am

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.

ray.wurlod · Post by **ray.wurlod** » Sat Mar 31, 2007 4:27 am

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.

georgesebastian · Post by **georgesebastian** » Sun Apr 01, 2007 12:55 am

Thanks all
But still i am not clear.

If the no of records to update is more then
we have to use ->Update existing rows or insert new rows

If the no of records to Insert is more then

we have to use -> Insert new rows or update existing rows

Am i Right?
George

chulett · Post by **chulett** » Sun Apr 01, 2007 7:20 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.

ray.wurlod · Post by **ray.wurlod** » Sun Apr 01, 2007 9:37 am

I add my two cents worth to Craig's making a grand total of four cents' worth advocation the "avoid unnecessary double action" design.

vmcburney · Post by **vmcburney** » Sun Apr 01, 2007 5:26 pm

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.

ray.wurlod · Post by **ray.wurlod** » Sun Apr 01, 2007 7:33 pm

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.

georgesebastian · Post by **georgesebastian** » Mon Apr 02, 2007 12:01 am

Hi Everyone,

That really was very infomative.Thanks all.This is really a fantasic forum.
I love this.

Thanks
George
:D

georgesebastian · Post by **georgesebastian** » Mon Apr 02, 2007 12:01 am

Hi Everyone,

That really was very informative.Thanks all.This is really a fantasic forum.
I love this.

Thanks
George
:D