Archives of the TeradataForum

Message Posted: Thu, 24 Aug 2006 @ 13:36:57 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

For some time now we've tended to use a Delete and subsequent Insert into target tables from work tables, based upon an historical understanding that Updates perform quite poorly.

Recently I've staked my reputation - always a bad thing to do - on changing from Delete/Insert to Updates but, from my own testing, have been disappointed with the results.

Based upon a table with 780K rows in it, I used a Work table with 5000 new inserts and 5000 updates to apply to the Target table. Both are co- located, by the way.

I performed the test with and without a Single Table Join Index in place.

The results are as follows :

        Traditional Delete/Insert process
     ?  Delete (W/O JI) : 9.5 secs
     ?  Delete (With JI) : 41 secs !
     ?  Insert (W/O JI) : 12.5 secs
     ?  Insert (With JI) : 16 secs

        New Process
     ?  Update (W/O JI) : 15 secs
     ?  Update (With JI) : 48 secs !
     ?  Insert (W/O JI) : 8.5 secs
     ?  Insert (With JI) : 12 secs

As you can see the totals are essentially

     Delete/Insert
     Total (W/O JI) : 22 secs
     Total (With JI) : 57 secs

     Update method
     Total (W/O JI) : 23.5
     Total (With JI) : 60 secs

What I surprised about is that the Update process was actually slower - slightly - than the Delete/Insert process.

The Inserts used a NOT EXISTS type construct, so that only those entries on the Work table that didn't already exist in the Target table were inserted. The Update SQL used an Update via a Join type construct, and all columns in the table were in the SET statement (even though only one column was actually changing).

I tried removing the number of columns in the SET statement, but it made no difference.

Does anyone have any insight as to why this should be the case ?

Oh, and we're currently on V2R5.1

Thanks for replies, as always.

Dave Clough
Database Designer
Express ICS

www.tnt.com

	<Prev	Next>		<<First	<Prev	Next>	Last>>