Archives of the TeradataForum
Message Posted: Thu, 24 Aug 2006 @ 13:36:57 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|